September 21, 2005 at 8:20 am
I need to grab data from a table in an older database and insert it into a table with the same name and fields in a new database. The only catch is that I only want to bring in data from the old database if it is not in the table in the new database.
I already have the Linked Server set up and that is working properly. I tried to use a query along the lines of 'insert into new_table select * from old_table where not exists(select * from new_table), but it updated 0 rows, and I know for a fact that there is data in the old table that is not in the new table.
Here are the fields in the tables (same fields in each):
ClinicKey
ProviderKey
PhoneCallTypeKey
CallDT
ReturnCallDT
DateAdded
DateChanged
Any ideas?
Thanks,
Rob
September 21, 2005 at 9:18 am
Rob,
Try this:
insert into new_table
select * from old_table
where not exists(select * from new_table
where new_table.clinickey = old_table.clinickey
and new_table.providerkey = old_table.providerkey
and new_table.phonecalltypekey =
old_table.phonecalltypekey)
providing the 3 columns make up a primary key.
Greg
Greg
September 21, 2005 at 9:27 am
Your correlated subquery is not structured correctly.
As Greg Noted.
For a correlated subquery your inner query must have reference to your outer query.
Your statement should look like this I am assuming ClinicKey is your primary key. If it is an identity, then you will need the set Identity_insert NewTable on (remember to put it back to off when done)
Insert into new_Table (ClinicKey, ProviderKey, PhoneCallTypeKey, CallDT, ReturnCallDT, DateAdded, DateChanged)
Select ClinicKey, ProviderKey, PhoneCallTypeKey, CallDT, ReturnCallDT, DateAdded, DateChanged
From Otherserver.DatabaseName.dbo.TableName A
where not exists (select *
from New_Table B
where A.ClinicKey = B.ClinicKey)
September 21, 2005 at 9:43 am
OK I forgot to mention... each table has a Primary Key column named PhoneCallKey that is an AutoNumber. That is what is goofing me up. So the primary keys in the old table may not match up with what is in the new table.
So could I use the other fields to make up a composite PK? Because basically the existing PK is useless in this instance.
Rob
September 21, 2005 at 2:57 pm
Sure
Insert into new_Table (ClinicKey, ProviderKey, PhoneCallTypeKey, CallDT, ReturnCallDT, DateAdded, DateChanged)
Select ClinicKey, ProviderKey, PhoneCallTypeKey, CallDT, ReturnCallDT, DateAdded, DateChanged
From Otherserver.DatabaseName.dbo.TableName A
where not exists (select *
from New_Table B
where a.col1 = b.col1
and a.col2 = b.col2
and a.col3=b.col3)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply