471,092 Members | 1,481 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

Insert Records From Table1 that do no exist in Table2

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

Jul 23 '05 #1
1 1469
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
--

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by soni29 | last post: by
5 posts views Thread by ndn_24_7 | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.