Post reply

Updating one table with records from another

  • March 27, 2009 at 3:54 am

    #73593

    Hi

    I have a problem that I've been struggling with for a few hours but have been unable to so far find a solution. Basically I need to construct a query that will move new records from one table to another - I'll explain in more detail.

    I have two tables that have identical records: we will call them 'Table A' and 'Table B' (Although 'Table B' does have some additional fields)

    Each morning all the records are replaced in 'Table A' (the records are uploaded from a .csv file)

    I need a query that will check if there are any new records in 'Table A' that are not present in 'Table B' and then copy those records to 'Table B'

    In the process of copying the records to 'Table B' once there, they need to be marked as new/updated (This last point is the reason why I cannot just do a bulk replace of the contents in 'Table B')

    The tables do not have a IDENTITY column but there is a four digit number which will be used for comparison purposes.

    As I'm using SQL Server 2000 and do not have access to the EXCEPT keywork I'm having some difficulty constructing a query.

    Any help you could give on this would be greatly appreciated.

  • March 27, 2009 at 4:23 am

    #967275

    Something like this should do the trick, Joseph:

    INSERT INTO TableB ([column list])

    SELECT ([matching column list from TableA] including GETDATE() AS DateUpdated)

    FROM TableA a

    LEFT JOIN TableB b

    ON b.[four digit number] = a.[four digit number]

    WHERE b.[four digit number] IS NULL

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • March 27, 2009 at 4:48 am

    #967286

    Thanks Chris.

    That's just what I needed - I actually just managed to figure it out - I was obviously staring at it for far too long last night.

    Anyway thanks for the quick reply.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply