Share via

How to move data one table to another table older than 30 days record? MS SQL SERVER 2016

Devendra Kumar Sahu 246 Reputation points
2022-02-09T09:45:40.19+00:00

I have two table OUTBOX and OUTBOX_1 both table column is same.

My table containing 14409004 record. I want to move record to OUTBOX_1 from OUTBOX those are Current date to older than 30 days.

Below the query through i fount out older data form 30 Days

SELECT * FROM OUTBOX WHERE DT_INSERT >= DATEADD(DAY, -30, GETDATE())

but i unable to move data

Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

SQL Server | Other

Answer accepted by question author

  1. Sreeju Nair 12,761 Reputation points
    2022-02-09T10:33:20.037+00:00

    Basically you need to do this ina transaction. Then use an insert into statement to insert data from table 1 to table 2, then delete the records from table 1, and then commit the transaction. An example 

    Begin Transaction
Insert into Outbox_1 (field1, field 2......) SELECT (field1, field2......) FROM OUTBOX WHERE DT_INSERT >= DATEADD(DAY, -30, GETDATE());
Delete FROM OUTBOX WHERE DT_INSERT >= DATEADD(DAY, -30, GETDATE());

Commit;

    Hope this helps

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.