November 16, 2004 at 1:54 pm
I need to compare two tables (Old & New) in 2 databases on SQL 2000. Both tables have three columns. I need to determine which rows exist in Table Old but not in Table New. Ultimately, I need the results to be added to Table New. Any suggestions?
November 16, 2004 at 2:21 pm
SQL COMPARE BUNDLE from REDGATE Software for comparing.
For inserting you would probably have to write your own scripts.
Tajammal Butt
November 16, 2004 at 2:53 pm
This is an example for 2 tables T2 and TXTFilelist each had 1 field [Column 0] with 3 same records. In addition TXTFilelist contained 2 extra records (5 records total). The following query inserted 2 records into T2 making it a copy of TXTFilelist. You have to modify this script in the Select part which should select only 3 fields belonging to the OLD table (with more data) like select OLD.Field1, OLD.Field2, OLD.Field3 and you have to modify your join accordingly
Insert
T2
select
txtfilelist.[column 0] from txtfilelist full outer join T2
on
T2.[column 0] = txtfilelist.[column 0]
where
T2.[column 0] is null
Yelena
Regards,Yelena Varsha
November 16, 2004 at 8:07 pm
I would prefer Yelena's query. If you have two different databases then you might need to change query from tablename.column to "databasename..tablename.column" and if you have if on two different servers then you might want to add one server as a linked server and then run query and remove it after you are done.
November 17, 2004 at 12:18 am
Why not using a database link and use the "excist" in the where clause??
SQL basics !!
GKramer
November 17, 2004 at 10:52 am
I agree with Guus, using the NOT EXIST is more accurate (what happens when [column 0] is nullable?).
Try a query such as:
INSERT INTO NEWTABLE (COL1, COL2, COL3)
SELECT A.COL1, A.COL2, A.COL3
FROM database1.dbo.OLDTABLE A
where not exists (SELECT * from database2.dbo.NEWTABLE B where B.COL1 = A.COL1 and B.COL2 = A.COL2 and B.COL3 = A.COL3)
-ldb
November 17, 2004 at 1:48 pm
Thank you all for your input. The REDGATE software bundle is pretty awesome. It will allow me to compare the date in the two tables and synchronize them, provided the proper Primary Keys are in place. Most of the problem is created by the nature of the data in the columns. Essentially, for each row, the data in both Col1 AND Col2 has to be different to get the proper result. I am working on it and will keep this forum posted.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply