| David Portas 2005-07-01, 8:23 pm |
| Every table should have a key. If you don't have one then I suggest you
fix that first - clean up your data if necessary and add a UNIQUE /
PRIMARY KEY constraint.
(You mentioned "unique identifier" by which I infer that you really
meant "unique key". SQL Server has a datatype called UNIQUEIDENTIFIER
but it's just a datatype - it isn't required for a key.)
Once you have a key (key_col in this example) you can do an INSERT like
this:
INSERT INTO Table2 (key_col, col1, col2, ...)
SELECT T1.key_col, T1.col1, T1.col2, ...
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.key_col = T2.key_col
WHERE T2.key_col IS NULL
Hopefully it's obvious that this will extend to any number of non-NULL
columns to make a unique key, so if you haven't figured out a key yet
it may help just to join on everything:
INSERT INTO Table2 (col1, col2, col3, ...)
SELECT DISTINCT T1.key_col, T1.col1, T1.col2, T1.col3, ...
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.col1 = T2.col1
AND T1.col2 = T2.col2
AND T1.col3 = T2.col3
AND ... etc
WHERE T2.col1 IS NULL
Be careful about columns with NULLs however, you probably won't get the
result you expect in that case.
--
David Portas
SQL Server MVP
--
|