How to copy new records & overwrite existing ones

[bogjacket]

Hello,

I need to copy one table into another. If a primary key from the source matches one in the destination, I need to overwrite the existing record in the destination with the new one from the source.

FWIW, I've searched online, and I've bought a book on sql syntax, but I can't seem to find exactly what I need.

Is this possible? Any pointers?

 

[jbenson001]

I would use 2 sql statments.

Use one for insert:
Insert Into <table>(col1... etc)
where table.id not in(select id
from <table2>)
and one for Update:
Update <table2>
set col1 = something,
col2 = something... etc
inner join <tabel1> ON <tabel1>.ID = <table2>.ID

They syntax may not be 100%, but you can get the idea and fix as necessary.

Jim

 

[SajidAttar]

Or

Delete all records from Destination table which are in Source table.

  Delete a from DestinationTable a
  inner join SourceTable on a.Column1 = b.Column1 and . . .

and Insert all records from Source table.

  Insert into DestinationTable
  Select * from SourceTable

Have backup of your data before deleting and delete backup once you confirm. If your destination table has identity column then you neet to set identity_insert property.


Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[vongrunt]

Suppose there are 50 records to insert and 100 to update.

INSERT + UPDATE: inserts 50 rows, then updates 150 rows
DELETE + INSERT: deletes 100 rows, then insert 150 rows
UPDATE + INSERT: updates 100 rows, then inserts 50 rows

All ways get the job done, but some cause less transactional logging.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

What you need is UPSERT, this has been requested for the next version of SQL server
basically you do UPSERT and then specify the key fields
if the key exists an update happens if it doesn't an insert happens
Who knows if it will make it inot the next version?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bogjacket]

That certainly sounds helpful, Denis.

Thanks to everyone else for their responses. I'll be working with these today.