October 23, 2002 at 1:18 pm
I'm trying to write a Stored Proc that periodically copies data from a source table to another destination table. Both the source and destination tables have the same Primary Keys. The key is a compound key on 3 fields (CustID, Product, Date)
What SQL Statement(s) will ensure that I copy information from the source that is NOT already in the destination table? I can not use MAX(date) condition because orders may come in with past dates. I also cannot drop and recreate the destination table because other fields have data that would be lost.
Any help is appreciated, Thanks.
Sincerely,
Matthew Mamet
Web Developer
embarc LLC
Matthew Mamet
October 23, 2002 at 1:43 pm
Here is a simple example on how to identify the records in one table, that don't reside in another based on a key. This example only uses one column for the key "ID", so to use a compound key just change the "ON" condition on the join. Using a select statement like this with a insert into command should help accomplish what you need.
Hope this helps.
create table test_data (
id int,
product char(10)
)
create table test_data2 (
id int,
product char(10)
)
insert into test_data values(1,'item 1')
insert into test_data values(2,'item 2')
insert into test_data values(3,'item 3')
insert into test_data values(3,'item 3')
insert into test_data values(3,'item 3')
insert into test_data values(5,'item 5')
insert into test_data values(5,'item 5')
insert into test_data values(5,'item 5')
insert into test_data2 values(3,'item 3')
insert into test_data2 values(3,'item 3')
insert into test_data2 values(5,'item 5')
insert into test_data2 values(5,'item 5')
insert into test_data2 values(5,'item 5')
-- find duplicate id
select a.id, a.product
from test_data a left join test_data2 b on a.id = b.id
where b.product is null
-- drop table test_data
drop table test_data, test_data2
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 23, 2002 at 1:52 pm
Greg, thanks for your reply.
I should have been more specific in my original question, because the help I need is how to change the ON clause of the join to accomodate 3 fields.
I know how to do it with 1 field, like your example.
thanks!
Sincerely,
Matthew Mamet
Web Developer
embarc LLC
Matthew Mamet
October 23, 2002 at 2:06 pm
Here is a the same join with three columns included. Hope this helps......
create table test_data (
id int, id2 int, id3 int,
product char(10)
)
create table test_data2 (
id int, id2 int, id3 int,
product char(10)
)
insert into test_data values(1,1,1,'item 1')
insert into test_data values(2,1,1,'item 2')
insert into test_data values(3,1,1,'item 3')
insert into test_data values(3,1,1,'item 3')
insert into test_data values(3,1,1,'item 3')
insert into test_data values(5,1,1,'item 5')
insert into test_data values(5,1,1,'item 5')
insert into test_data values(5,1,1,'item 5')
insert into test_data2 values(3,1,1,'item 3')
insert into test_data2 values(3,1,1,'item 3')
insert into test_data2 values(5,1,1,'item 5')
insert into test_data2 values(5,1,1,'item 5')
insert into test_data2 values(5,1,1,'item 5')
-- find duplicate id
select a.id, a.product
from test_data a left join test_data2 b on a.id = b.id and a.id2=b.id2 and a.id3=b.id3
where b.product is null
-- drop table test_data
drop table test_data, test_data2
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 23, 2002 at 2:21 pm
ah great, so its just like a regular WHERE statement.
thanks for your help!
Sincerely,
Matthew Mamet
Web Developer
embarc LLC
Matthew Mamet
October 23, 2002 at 2:34 pm
Do you want to update data already there or just insert data where the PK values do not exist?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply