Insert Records From Table1 that do no exist in Table2

[csgraham74]

Hi guys,

i have a little problem here.

im attempting to write a stored procedure that compares two tables of
the same data structure and adds (inserts) extra records that exist in
table1 to table2.

My problem is that i dont have a unique identifier between the tables.

i think someone said that i needed to build up a key

any ideas greatly appreciated ??

C

[David Portas]

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
--