How to kep one week data in table using ASP.Net C# SQLSERVER 2005

Question

0.00/5 (No votes)

See more:

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 3-Jun-13 23:48pm

Software Engineer 892

Add a Solution

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).

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Thanks7872 · Answer 1 · 2013-06-03T23:56:00

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.

Posted 3-Jun-13 23:56pm

Thanks7872

Updated 3-Jun-13 23:59pm

v2

Zafar Sultan · Answer 2 · 2013-06-03T23:57:00

Solution 2

Create a SQL Job to do that for you periodically. To learn how to create one check this[^]

Posted 3-Jun-13 23:57pm

Zafar Sultan

Maciej Los · Answer 3 · 2013-06-04T00:08:00

Solution 3

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

Query:

SQL

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

More: INSERT Examples[^]

Posted 4-Jun-13 0:08am

Maciej Los