Post reply

Inserting only new and modified records?

  • March 12, 2004 at 11:23 am

    #359297

    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!

  • March 12, 2004 at 11:59 am

    #498576

    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

  • March 12, 2004 at 12:12 pm

    #498578

    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.

  • March 15, 2004 at 12:22 am

    #498705

    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

    Ankush.choudera@slk-soft.com

  • March 15, 2004 at 4:56 am

    #498724

    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

  • March 15, 2004 at 5:29 am

    #498727

    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]

  • March 15, 2004 at 6:17 am

    #498732

    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.

  • March 15, 2004 at 6:47 am

    #498740

    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