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