Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to merge new table into existing table

Coding and Syntax

How to merge new table into existing table

by  myearwood  Posted    (Edited  )
Over the years, I've seen people ask how to merge a new table into an existing table. This can be for purposes of a data conversion or a business process to synchronize two systems.

This usually involves a long process to scan the new table, seek in the existing table and either add or replace records.

I once had to merge 100,000 records into 1 million. The original code took several hours. I managed to get it down to about 15 minutes. I've refined that technique to just two commands.

Code: 
UPDATE t ;
	SET ;
		t.field1 = s.field1, ;
		t.field2 = s.field2, ;
		t.field3 = s.field3 ;
	FROM ;
		target t ;
		inner join source s ;
                on t.pk = s.pk ;
	WHERE ;
		t.field1 # s.field1 ;
		OR t.field2 # s.field2 ;
		OR t.field3 # s.field3

INSERT INTO ;
	target ;
	(;
		field1,;
		field2,;
		field3) ;
	SELECT ;
		field1,;
		field2,;
		field3 ;
	FROM ;
		source s ;
	WHERE ;
		s.pk NOT IN ;
		(SELECT pk FROM target)
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top