Inserting only new and modified records?

[andrewradvansky]

I have two tables in SQL Server. One contains existing data (Tbl1) and one is used for inserts (Tbl2) from another database. I should note that both tables contain data from the same source, so some records will be duplicates. What I would like to do is insert from Tbl2 any records that are new or modified, into Tbl1. So instead of dropping the entire Tbl1 and inserting all records from Tbl2, I would just be adding new records or updating existing records that have been changed. I have a DocID field that can be used to identify unique records. This would be used during our testing process. Can anyone help?

 

[SQLSister]

To get the new records is easy, just do a left join with the table you want to move the records to and select those records where the key field is null for the second table.

Updated records are harder unless you have a field that indicates the date of the last insert. In the easy case, you just join the tables together and select the records in which the update date is greater on the records in the insert table.

If you don't have a way to figure it out, you need to compare every single field. If you try to do all of them in one query, likely it will time out or take forever to run. So it's more work, but usually faster if you write individual queries to update the records checking each field. In this case you checek to see if the values are not equal to each other.

If you don't know how to do an update using the records from another table, you should check out the Update section of books online.