UNIQUE ROWS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

UNIQUE ROWS

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-10-18 : 05:38:51

Hi,

I am not sure how to accomplish the following:
I have to import a large excel file into tables in the db.
I have 2 tables Order with order_id as PK and OrderItem with unique keys.
- I need to check if the order_id exists in table ORDER. If not than i have to create the order in table ORDER.
-The excel file contains duplicate order_id so i need to only get the distinct id and insert into table Order.
- I than have to check if PL_id in excel file is null. If it's null than i have to insert the unique order_id,style and colour codes into the table orderitems.

To insert the distinct order_ids into the ORDER table i use the SORT component. However, i than need to insert the unique rows into the ORDERITEM TABLE. I noticed that the SORT component will eliminate all rows with duplicate order_ids and for the orderitem i need to return all the order_ids.

eg of file
order_id style code colour code
23 ABC900 RED
23 CF897 RED
23 CF897 RED
34 PP100 BLACK

ORDER TABLE will have
order_id
23
34

ORDERITEM TABLE
order_id style code colour code
23 ABC900 RED
23 CF897 RED
34 PP100 BLACK

How can i get the above results without using staging tables?

Thanks

CREATE

TABLE [dbo].[Order](

[order_id] [nvarchar]

(50) NOT NULL,

[order_date] [smalldatetime] NOT NULL,

[ordertype_id] [int] NOT NULL

CREATE

TABLE [dbo].[OrderItem](

[orderitem_style_code] [nvarchar]

(50) NOT NULL,

[orderitem_color_code] [nvarchar]

(50) NOT NULL,

[order_id] [nvarchar]

(50) NOT NULL,

[orderitem_quantity] [int]

NOT NULL,

[orderitem_totalnet] [decimal]

(10, 2) NULL,

[orderitem_unitprice] [decimal]

(10, 2) NULL,

[orderitem_entrydate] [smalldatetime]

NULL,

CONSTRAINT [PK_OrderItem_1] PRIMARY KEY CLUSTERED

(

[orderitem_style_code]

ASC,

[orderitem_color_code]

ASC,

[order_id]

ASC

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-19 : 10:41:44

In general, I'd recommend staging tables but as you can't use them...

To ignore duplicates, try:
CREATE UNIQUE CLUSTERED INDEX IDX_UNQ_ORDER_ID ON Order(Order_ID) WITH IGNORE_DUP_KEY

gaauspawcscwcj
Starting Member

29 Posts

Posted - 2009-10-21 : 03:13:50

you can try this solution
1. create an temp table like ORDER TABLE but without any key and more ID key identity(1,1)
2. put all data in this temp table
3. filter temp table like code below

delete dbo.Order_temp
where exists (
select test1.order_id from dbo.Order_temp as test1
where test1.order_id = dbo.Order_temp.order_id
group by test1.order_id
having Order_temp.ID <> min(test1.ID) )
4. put all your data in temp table to main table
5. have fun
gaauspawcscwcj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources