Monday, March 19, 2012

Composite key "NOT IN" query?

I have a table with a composite key formed by the unique combination of columns w, x, y, z

I'm trying to write an INSERT statement along the following lines

INSERT INTO myTable

(SELECT w, x, y, z FROM someTable) t1

WHERE (this is the part I'm stumped on - where the unique combination of w, x, y, z is NOT in myTable already)

Help would be appreciated. Can you use the NOT IN keyword on composite values?

Can you use the NOT IN keyword on composite values?

Yes,but that is expensive.

FromQuery SQL Server Performance Tuning Tips

But If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, instead try to use one of the following options instead, all of which offer better performance:

Use EXISTS or NOT EXISTS|||

INSERT INTO MyTable

SELECT t1.w,t1.x,t1.y,t1.z

FROM someTable t1

LEFT JOIN MyTable t2 ON t1.w=t2.w and t1.x=t2.x and t1.y=t2.y and t1.z=t2.z

WHERE t2.w IS NULL

This should also work:

INSERT INTO MyTable

SELECT w,x,y,z

FROM someTable

EXCEPT

SELECT w,x,y,z

FROM MyTable

No comments:

Post a Comment