locked
What is the best approach to purge (Move) data from original table to History table? RRS feed

  • Question

  • Hi Folks,

    We are facing big challenges to manipulate data over larger database tables. We are planning to move older data (say six months old) from original table to history table (history table might either persist on the same server/database or other server). This means the original table shoud be having only the recent six months data plus current month data. Once the current month data loads are finished, the oldest data should be moved from original table to history table.

    Can someone let me know the best scenario to implement this process (The process should be able to automate)?

    Thanks in advance..!


    Regards; Sureddy; Database Administrator

    Wednesday, August 8, 2012 10:21 AM

Answers

  • The SELECT INTO statement is perfect to copy one table's data into another..

    a&h

    • Marked as answer by Chreddy S Monday, October 29, 2012 2:02 PM
    Tuesday, September 25, 2012 9:16 AM

All replies

  • 1) Partition 

    2) 

    insert into fooArchive..dbArchive 
    select getdate(),d.*
    from (delete top (1000) 
            from foo..dbname
            output deleted.*) d
            go

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

    • Marked as answer by Chreddy S Wednesday, August 8, 2012 11:14 AM
    • Unmarked as answer by Chreddy S Wednesday, August 8, 2012 11:17 AM
    Wednesday, August 8, 2012 10:41 AM
  • Hi Uri Dimant,

    Thanks for your reply.

    Just for curiosity want to know, Can't we switch the older parition data to history table, instead of executiy the above query?


    Regards; Sureddy; Database Administrator

    Wednesday, August 8, 2012 11:20 AM
  • Yes we can :-)

    --create 7 partitions
    CREATE PARTITION FUNCTION PF_TargetTable1(varchar(15))
    AS RANGE LEFT FOR VALUES
    (
        '20080630000000',
        '20080701000000',
        '20080702000000',
        '20080703000000',
        '20080704000000',
        '20080705000000'
     )
    GO

    CREATE PARTITION SCHEME PS_TargetTable1
    AS PARTITION PF_TargetTable1
    ALL TO ([PRIMARY])
    GO

    ALTER TABLE dbo.TargetTable1
    DROP CONSTRAINT PK_TargetTable1
    GO

    ALTER TABLE dbo.TargetTable1
    ADD CONSTRAINT [PK_TargetTable1] PRIMARY KEY CLUSTERED
    (
        StartDate,
        id
    )
    ON PS_TargetTable1(StartDate)
    GO

    Using the above table, the script below shows one method to efficiently 
    remove all data from a partition using SWITCH.  Although not required, this 
    example uses a partitioned staging table, which ensures the source and 
    target partition(s) are on the same filegroup(s).  I've found that technique 
    to be handy for more complex partition schemes.

    --insert 8 rows of test data
    INSERT INTO dbo.TargetTable1(Company, StartDate)
        SELECT 'Company 1', '20080630010203'
        UNION ALL SELECT 'Company 2', '20080630020304'
        UNION ALL SELECT 'Company 3', '20080701030405'
        UNION ALL SELECT 'Company 4', '20080701235959'
        UNION ALL SELECT 'Company 5', '20080701235959'
        UNION ALL SELECT 'Company 6', '20080701235959'
        UNION ALL SELECT 'Company 7', '20080702040506'
        UNION ALL SELECT 'Company 8', '20080702050607'
    GO

    --create staging table with same schema and on same file group as source
    CREATE TABLE [dbo].[TargetTable1_Staging]
    (
        [Company] [varchar](50) NOT NULL ,
        [StartDate] [varchar](15) NOT NULL,
        [id] [bigint] IDENTITY(1,1) NOT NULL,
        CONSTRAINT [PK_TargetTable1_Staging] PRIMARY KEY CLUSTERED
        (
            StartDate,
            id
        ) ON PS_TargetTable1(StartDate)
    )

    --move partition 3 to staging table
    --any value within the desired partition may be specified)
    ALTER TABLE dbo.TargetTable1
    SWITCH PARTITION $PARTITION.PF_TargetTable1('20080701235959')
    TO dbo.TargetTable1_Staging PARTITION 
    $PARTITION.PF_TargetTable1('20080701235959')
    GO

    --show moved data
    SELECT * FROM dbo.TargetTable1
    SELECT * FROM dbo.TargetTable1_Staging
    GO


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

    • Marked as answer by Chreddy S Wednesday, August 8, 2012 12:15 PM
    • Unmarked as answer by Chreddy S Tuesday, August 28, 2012 12:52 PM
    Wednesday, August 8, 2012 11:31 AM
  • Thank you Uri Dimant. I will get back you, if I see any issues.

    Regards; Sureddy; Database Administrator

    Wednesday, August 8, 2012 12:16 PM
  • Hi Uri Dimant,

    I have followed your scenario, but I can't be able to swith same partition more than one time. Is there any sollution?

    Thanks for your assistance


    Regards; Sureddy; Database Administrator

    Tuesday, August 28, 2012 12:41 PM
  • The SELECT INTO statement is perfect to copy one table's data into another..

    a&h

    • Marked as answer by Chreddy S Monday, October 29, 2012 2:02 PM
    Tuesday, September 25, 2012 9:16 AM