none
Check and re-insert data RRS feed

  • Question

  • Hi, 

    I am trying to move data from the staging database to the prod database, but my table doesn't have primary key so how can I create a SSIS process? which compares a data from staging db to prod db then delete the existing data from prod and insert new data from staging. Can you please help me with this issue?  

    I have same rows in both databases, and I want to compare a data based on the date. 

    Thank you,

    Tuesday, October 8, 2019 3:19 PM

All replies

  • Hi

    Look for a artificial key. i.e., - Look for creation of a artificial key which could be combination of multiple columns and this decision should be taken based on business columns & domain.

    As we don't have any information on your scenario. I should just say you to analyze data and go for a composite key based on multiple columns.

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, October 8, 2019 4:09 PM
  • Hi yo_786,

    You can leverage T-SQL EXCEPT statement.

    Check it out:

    DECLARE @source TABLE (ID INT PRIMARY KEY, City VARCHAR(50));
    DECLARE @target TABLE (ID INT PRIMARY KEY, City VARCHAR(50));
    
    INSERT INTO @source (ID, City)
    VALUES (1, 'Miami')
       , (2, 'Orlando')
       , (3, 'Tampa')
       , (4, 'Coral Gables');
    
    INSERT INTO @target (ID, City)
    VALUES (1, 'Miami')
       , (3, 'Tampa')
       , (4, 'Fort Lauderdale');
    
    -- identical rows
    SELECT * FROM @source
    INTERSECT
    SELECT * FROM @target;
    
    -- different rows #1
    -- 2   Orlando         -- to INSERT
    -- 4   Coral Gables   -- to UPDATE
    SELECT * FROM @source
    EXCEPT
    SELECT * FROM @target;
    
    -- different rows #2
    -- 4   Fort Lauderdale   -- to DELETE
    SELECT * FROM @target
    EXCEPT
    SELECT * FROM @source;
    Tuesday, October 8, 2019 4:14 PM
  • Hi yo_786,

    Thanks for your post.

    Or you could consider using tablediff command to achieve your needs.

    SQL Server tablediff command line utility

    Ways to compare and find differences for SQL Server tables and data

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 9, 2019 6:15 AM
  • Hi yo_786,

    Have you got your answer? If yes, please kindly close the thread by marking useful replies as answers.

    Thanks for your cooperation.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 15, 2019 7:05 AM