locked
Insert, Update and delete RRS feed

  • Question

  • How do we delete the existing records in destination table, when we find they are no more exist in the source (Sharepoint list). 

    I was using the lookup transformation for comparison of records in Source and destination, as people who enter data in sharepoint list  I will be inserting the those records(new) and as well update the existing records if there is any change. But some people will be deleting there entries after some days, and they will not exists in the sahrepoint list.

    But by the time I would have loaded my table with there entries, so now how can I delete those records simultaneously while inserting new records and updating the existing records?

    Please help.

    Thursday, September 6, 2012 11:23 PM

Answers

  • Did you consider using a merge statement, that has the ability to insert , update and delete records when not matched by source? you might require a staging table for this purpose.

    Here's a link explaining how you can use the merge statement inside SSIS Merge in SSIS

    Sample SQl From MSDN Merge and delete Merge TSQl Sample


    Regards, Dinesh

    • Proposed as answer by Koen VerbeeckMVP Friday, September 7, 2012 8:51 AM
    • Marked as answer by Eileen Zhao Thursday, September 13, 2012 11:46 AM
    Friday, September 7, 2012 1:08 AM

All replies

  • Did you consider using a merge statement, that has the ability to insert , update and delete records when not matched by source? you might require a staging table for this purpose.

    Here's a link explaining how you can use the merge statement inside SSIS Merge in SSIS

    Sample SQl From MSDN Merge and delete Merge TSQl Sample


    Regards, Dinesh

    • Proposed as answer by Koen VerbeeckMVP Friday, September 7, 2012 8:51 AM
    • Marked as answer by Eileen Zhao Thursday, September 13, 2012 11:46 AM
    Friday, September 7, 2012 1:08 AM
  • by Looking your requirements, it seems you need Merge concept.

    by using Merge statement, you will be able to Update existing records as well as Insert New records as per your requirements.

    there are many options you can use like as you said about the lookup in SSIS, or you can write a procedure which does inserts and updates.

    Please go through given links. you will find easy to implement Merge with examples.

    http://www.codeproject.com/Articles/37172/Merge-Statement-in-SQL-Server-2008

    http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/

    Good luck...


    Regards, Chirag Patel (ETL Engineer)

    Friday, September 7, 2012 1:34 AM
  • If you can pay for a license check out the table difference component from CozyRoc here. It allows you to identify new, updated, unchnaged and deleted records by comparing the source and desctination data source.

    If you can't, you can try using the built in Slowly Changing Dimension transformation or a custom more powerful SCD from here or you can stage your data from the sharepoint list into a sql table and then use T-SQL queries to get the difference between the staging table and the destination table.


    http://btsbee.wordpress.com/

    • Proposed as answer by COZYROC Monday, September 10, 2012 2:17 AM
    Friday, September 7, 2012 8:48 AM