locked
How to purge data from a table RRS feed

  • Question

  • We have staging table in which data is dumped from files . The staging table is truncated for every load . In order to retain data from staging table we are creating staging_purge table which hold the staging data. what is the fastest way  to copy data from staging to purge table without impacting the load process.
    Wednesday, November 4, 2015 9:58 AM

Answers

  • The suggestion I made to rename the table and recreate the table takes less than a second.

    True

    Only thing is that it needs to have a supplimentary process which will do the tidy up later for these objects once they're no longer required


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Eric__Zhang Tuesday, November 17, 2015 8:54 AM
    Thursday, November 5, 2015 1:18 PM

All replies

  • You are going to have higher IO by copying the data between tables, but as a quick thought you could change the truncate to DELETE with an OUTPUT INTO clause?


    Gavin Clayton Claytabase Ltd www.claytabase.co.uk

    Wednesday, November 4, 2015 10:09 AM
  • The classic insert statement will do:

    INSERT INTO newTable (col1, col2, col3)
    SELECT column1, column2, column3
    FROM oldTable

    Fastest way without hindering the process time:

    --1
    Select * into Staging_temp from Staging
    --2
    Truncate Table Staging
    --3
    INSERT INTO Staging_Purge (col1, col2, col3)
    SELECT column1, column2, column3
    FROM Staging_temp
    --4
    Drop table Staging_temp

    Shreepal

    Future-MVPian with a Motive to Help

    Please mark as answer/helpful, if it is useful


    Wednesday, November 4, 2015 10:15 AM
  • We need to keep the data at least for 6 months to ensure the reconciliation process , so that we can back track the data which came from the file for data load process.

    We tried for switch partition , but assume if one partition is full and data came for the same partition it won't work.

    what should be the fastest way to move data from 1 table to another  in sql server 2008

    Wednesday, November 4, 2015 10:26 AM
  • --SQL2008,2012,....
    insert into TBL 
    select getdate(),d.*
    from (delete top (100) 
            from TB
            output deleted.*) d
            go

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, November 4, 2015 11:21 AM
    Answerer
  • DELETE will increase the size of log file and in production environment database is in full recovery mode.

    We planned to TRUNCATE the staging and before TRUNCATING we want to preserve the STAGING data into some other table (PURGE Table ) , what should be best and fastest way to move the data into the PURGE table . 

    Please suggest 

    Wednesday, November 4, 2015 11:58 AM
  • How much data do you want to move? Also SSIS + OLEDB Fast Load option could be considered

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, November 4, 2015 12:05 PM
    Answerer
  • It contains around 7000000 data in staging table  for each load , and if we use SSIS that means another component

    need to be plugged into the existing package ,

    Wednesday, November 4, 2015 12:44 PM
  • Just add a step in your SSIS package to copy your data to another table through INSERT and truncate your table at the end of your T-SQL in this step.  By the way, log file size is a different topic. Your database needs a proper log file work and you should have a maintenance log backup  in place to keep the log file stay "health".
    Wednesday, November 4, 2015 3:22 PM
  • If you only need the staging table for audit purposes and will not often query the table, you can rename the staging table to <table name>_yyyymmdd

    and recreate the staging table again with every load

    Wednesday, November 4, 2015 3:43 PM
  • We need to hold the data in a purge table for minimum 6 months  . The STAGING table is truncated for every load .

    We don't want the load process to take much time in order to insert data from STAGING to PURGE table .

    The STAGING table will be TRUNCATED after every load .

    What should be best and fastest possible solution to move data from STAGING to PURGE table .

    Thursday, November 5, 2015 9:52 AM
  • The suggestion I made to rename the table and recreate the table takes less than a second.
    Thursday, November 5, 2015 12:52 PM
  • The suggestion I made to rename the table and recreate the table takes less than a second.

    True

    Only thing is that it needs to have a supplimentary process which will do the tidy up later for these objects once they're no longer required


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Eric__Zhang Tuesday, November 17, 2015 8:54 AM
    Thursday, November 5, 2015 1:18 PM
  • That means every time data load happens a new purge table will be created . Is there any where we can append the data in the same purge table .

    Please suggest

    Thursday, November 5, 2015 2:00 PM