Post reply

How do I Update Existing Rows and Insert New Rows?

  • April 15, 2016 at 10:35 am

    #306383

    I have an SSIS package that reads a CSV file. I have everything setup for the data conversion so that it can be inserted into the MS SQL destination correctly. The flat file source contains rows of students. The destination table already has existing records that will match the existing students. How would I first query the database and see if the student number already exists and if it does update the row with the current information. If it doesn't, insert the new record?

  • April 15, 2016 at 10:53 am

    #1872415

    The overall answer is to look into the Merge command.

    https://msdn.microsoft.com/en-us/library/bb510625.aspx?f=255&MSPPError=-2147217396

    However this doesn't take into account the idea of slowly changing dimensions. Do you need to keep a history of the student information as it changes or is this a straight update-insert situation?

  • April 15, 2016 at 10:55 am

    #1872417

    In this case it is a straight update-insert situation. I was hoping there might have been an item in the SSIS toolbox in Visual Studio that would help with that.

  • April 15, 2016 at 11:33 am

    #1872446

    MERGE would do if you wanted to use TSQL, but if you want to do this in SSIS, you'll need to do one of the following:

    1. Use a Lookup Transformation in your data flow to see if the row already exists in the target table. Only route no match condition to the INSERT. You can route matching rows another direction towards your UPDATE logic. I'd recommend this route if your lookup is not hitting a huge table.

    https://www.simple-talk.com/sql/ssis/implementing-lookup-logic-in-sql-server-integration-services/

    2. Use a MERGE JOIN. An alternative to the lookup is the MERGE JOIN. For this method, you would merge your incoming data from the file into your lookup table. Use an OUTER join in the MERGE JOIN, then you'll need to test for the existence of the lookup row in a conditional split after the merge. Route matches to UPDATE logic and no matches to INSERT logic.

    https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-merge-join-transformation/

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

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

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