Inserting only new and modified records?
-
Hi everyone, first time poster.
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 really hope this makes sense and moreso hope someone can help me! Thanks!
-
Hi Andrew -
Welcome to the SQLServerCentral world!
Questions back to you: Is there a way to uniquely identify every record? Would there be a field or combination of fields that you can use to specify one and only one record? When you say existing records that have been changed, are the changes limited to one or two fields or can any part of a record be updated?
A solution to inserting into tbl1 those recs in tbl2 that do not already exist in tbl1 would be using an insert..select statement where there is a join statement in the select.
Example:
insert into tbl1(field1,field2,field3)
select b.field1, b.field2, b.field3
from tbl1 a right outer join tbl2 on
a.field1 = b.field1
where a.field3 is null
Hope this helps,
Michelle -
Hi Michelle,
Yes, I have a DocID field to uniquely identify records. Yes, any part of a record (aside from a few computed fields) can be updated.
-
Hi Andrew,
Instead you executing the SQL statements to insert the records from Tbl2 to Tbl1, you can create a trigger on Tbl2 to serve the purpose.
Inside the trigger you can check for the newly inserted record or for modified one using INSERTED and DELETED tables appropriately.
You can get details of using INSERTED and DELETED tables in BOOKS ONLINE. If you still have doubts do contact me.
Regards,
Ankush
-
Well if you are running the insert and update statements inside a Stored Procedure then related inserts and updates on tbl1 could be done in the same procedure under the same transaction boundaries. If you are tracking the date_modified field along with time as a column item in the tables then accomplishing the task should be seemless.
Prasad Bhogadi
www.inforaise.com -
May I ask why you have two different tables with the same structure and the more or less same data in two different databases?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sure, Frank. We used to update the existing table from a flat file, which was written in a script, but we decided to bypass that step and go straight from the script to the table. So I created a new table for that purpose in our test environment since the existing receives live updates throughout the day. We need to test the new insert without screwing around with the existing table directly. These are in the same database, btw.
-
I see. I thought you wanted to have those two identical tables permanently.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply