Post reply

Process by which to append new records or update existing ones and append to associated history table

  • August 24, 2012 at 3:25 pm

    #265924

    Hello,

    I ran a search on this site, but couldn not find an example similar to my objective in a SQL Server 2008 R2 database I've created. Using SSIS I am importing tasks from multiple MPP files that are first going to a Tasks_import staging table, that is not normalized. All data cleansing has been performed in the Tasks_import staging table.

    My objective is to now import the contents of the Tasks_import table to a normalized table called Tasks. Each task record is unique defined by a combination of the ProjectID, IterationID, and WBS field values. When a new unique set appears in the staging table following an import, it must add this new record to the Tasks table. If this unique set already exists, then it must place a copy of the current record in the Tasks table over to Tasks_History table and then update the current record in Tasks table with the new information from the Tasks_import staging table.

    Can somebofdy provide an example solution for this? Basically, what I asking for is the SQL logic that determines when to import a record and when to add a record to the history table and update the one in the Tasks table.

    Currently, I am just using SSIS OLEDB Source and Destination tasks to perform this function, but I'm guessing that these will need to be replaced with a stored procedure requiring a parameter for each field being passed to it from the staging table.

    Thanks.

  • August 24, 2012 at 4:29 pm

    #1529998

    grantbanjo (8/24/2012)

    Process by which to append new records or update existing ones and append to associated history table

    Described insert/update process can be solved by using MERGE statement. Appending to history table by a trigger.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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

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