none
Staging tables to Production RRS feed

  • Question

  • How do you update data from Staging tables to production typically. Do we have any shortcut or something like that. In oracle we have “exchange partition” command to load the table into the production environment with minimal impact.Help me understand. Thanks.

    svk

    Thursday, May 17, 2012 4:56 PM

Answers

  • Generally I have t-sql execution task to delete data from staging table before data flow control where I do insert/update/delete on staging table and finally another execution task to insert data from staging into a prod. table. Sure they are shlould be at the same structure.

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by czarvk Friday, May 18, 2012 3:00 PM
    Thursday, May 17, 2012 5:53 PM
  • Another option is using the MERGE T-SQL (available in SQL Server 2008 and up). the concept is easy upserts.

    Partitioning also exists in SQL Server, if you have your table set to be partitioned (I guess based on date) then any new data would automatically sit in the new partition which yes, in turn yields better performance.


    Arthur My Blog

    • Marked as answer by czarvk Friday, May 18, 2012 3:00 PM
    Thursday, May 17, 2012 6:51 PM
    Moderator

All replies

  • Generally I have t-sql execution task to delete data from staging table before data flow control where I do insert/update/delete on staging table and finally another execution task to insert data from staging into a prod. table. Sure they are shlould be at the same structure.

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by czarvk Friday, May 18, 2012 3:00 PM
    Thursday, May 17, 2012 5:53 PM
  • Another option is using the MERGE T-SQL (available in SQL Server 2008 and up). the concept is easy upserts.

    Partitioning also exists in SQL Server, if you have your table set to be partitioned (I guess based on date) then any new data would automatically sit in the new partition which yes, in turn yields better performance.


    Arthur My Blog

    • Marked as answer by czarvk Friday, May 18, 2012 3:00 PM
    Thursday, May 17, 2012 6:51 PM
    Moderator