Click here to Skip to main content
14,573,794 members
Rate this:
Please Sign up or sign in to vote.
I want to move TableA data to TableB after one week finish.
=============================================================


Hi frnds,

am working on asp.net c#, SqlServer 2005.

my requirement is :

I have two tables in sqlserver database.
1)TableA
2)TableB

after every one week finish TableA data must be inserted into TableB...only one week data must be inserted in TableB.

This process should be continued.

Please can you help me, how to do this.

Thanks in advance.
Posted
Comments
Prasad Khandekar 4-Jun-13 5:57am
   
Hello,

Assuming that both the table structures are identical and there is a column containing details of when a record was last modified, then you can use SQL similar to one shown below to INSERT Selective Data into Table B from Table A.

INSERT INTO table_b (tb_col1, tb_col2, tb_col3, ...)
SELECT ta.col1, ta.col2, ta.col3, ...FROM table_a ta
WHERE ta.last_modified BETWEEN date_start AND date_end

Later on you can fire the DELETE SQL to delete this data from table_a.
DELETE
FROM table_a taWHERE ta.last_modified BETWEEN date_start AND date_end

Don't forget to wrap both these queries in a transaction block and check the count of inserted vs deleted rows to make sure that everything is done correctly. This entire thing can be wrapped in a procedure which then can be scheduled to run on every weekend. (http://msdn.microsoft.com/en-us/library/ms190268.aspx).
Rate this:
Please Sign up or sign in to vote.

Solution 1

You can make use of SQL Server Jobs to accomplish this task.There are wonderful articles available for the same on below links.

A scheduler GUI for SQL Server Agent[^]

SQL Server Agent Job Management[^]

Important:

SQL Express does not support SQL Server Agent.
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 2

Create a SQL Job to do that for you periodically. To learn how to create one check this[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

You need to create job.
CREATE a T-SQL Job Step[^]
How to: Create a T-SQL Job Step (SSMS)[^]

Query:
INSERT INTO table2 (field1, field2, field3,...fieldN)
SELECT field1, field2, field3,...fieldN
FROM table1
WHERE datefield BETWEEN @stratDate AND @endDate


More: INSERT Examples[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100