CODEWITHSUNDEEP
sql-server-2005
Mehboob Afridi
Mehboob Afridi

Reputation: 309

Copy data from one table to another without duplicates based on more then one column comparision

I am stuck in the following query. This was working properly on mySQL but it gives error on MSSQL-2005. The main purpose of the query is to copy data from one table to another without duplicates based on multiple columns comparison from both tables.

I can do this to compare one column for duplication, but I can't do when I compare more then one column for duplication.

Here is my query.

INSERT INTO eBayStockTaking (OrderLineItemID,Qty,SKU,SubscriberID,eBayUserID) 
SELECT OrderLineItemID,Qty,SKU,SubscriberID,eBayUserID 
FROM tempEBayStockTaking WHERE (OrderLineItemID,SubscriberID,eBayUserID) 
Not In (SELECT OrderLineItemID,SubscriberID,eBayUserID FROM eBayStockTaking)

Note: I have been through many similar questions but all in vain.

Thanks

Upvotes: 0

Views: 1280

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166326

Rather try NOT EXISTS

Something like

INSERT INTO eBayStockTaking (OrderLineItemID,Qty,SKU,SubscriberID,eBayUserID) 
SELECT  OrderLineItemID,
        Qty,
        SKU,
        SubscriberID,
        eBayUserID 
FROM    tempEBayStockTaking t
WHERE   Not EXISTS  (
                        SELECT  *
                        FROM    eBayStockTaking e
                        WHERE   e.OrderLineItemID = t.OrderLineItemID
                        AND     e.SubscriberID = t.SubscriberID
                        AND     e.eBayUserID = t.eBayUserID) 
                    )

I know MySQL allows Row Subqueries, nut SQL Server does not allow this.

Upvotes: 2

Related Questions