Solved

Transferring records from one table to another based on days in SQL 2005

Posted on 2012-03-21
8
228 Views
Last Modified: 2012-03-29
Hi Experts,

I have got two table Table A that contains huge records of logs and Table B as an archive table. I want to keep records for last 60 days starting from the current date in table A and all the other records will get automatically transferred to Table B and will get deleted from table a.

I there is a failure in the above while transferring then a roll back should happen.

I need an SQL query that will accomplish this when i execute it from a client application.I have attached a sample file for your reference.

With Kind Regards.
Book1.xlsx
0
Comment
Question by:vikas_nm
  • 4
  • 3
8 Comments
 
LVL 6

Expert Comment

by:yawkey13
ID: 37748722
You safest bet is to insert into the archive table and then delete from the production table...

INSERT INTO TABLEB( Id, Log )
SELECT ( Id, Log ) FROM TABLEA
WHERE DATEDIFF(d,Log,GETDATE()) > 60;
DELETE FROM TABLEA
WHERE Id IN (
  SELECT Id FROM TABLEB
)
0
 

Author Comment

by:vikas_nm
ID: 37749081
What if i need to check i data already exists in the table b before inserting. how can this be done.
0
 
LVL 6

Expert Comment

by:yawkey13
ID: 37749121
INSERT INTO TABLEB( Id, Log )
SELECT ( Id, Log ) FROM TABLEA
WHERE DATEDIFF(d,Log,GETDATE()) > 60
AND Id NOT IN ( SELECT Id FROM TABLEB );
DELETE FROM TABLEA
WHERE Id IN (
  SELECT Id FROM TABLEB
)
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:vikas_nm
ID: 37749165
One last question is this the fastest way to transfer data from one table to another via the SQL Query or is there any optimized query to accomplish this task. Because in a day i would be having like 4000 to 6000 records been getting transferred.
0
 
LVL 6

Expert Comment

by:yawkey13
ID: 37749240
For large transfer there are BULK commands and SSIS functions for stuff like this, but for 4,000-6,000 records, I don't think it would be worth it.  With a properly setup table and database, SQL Server will move this many rows without you noticing.
0
 

Author Comment

by:vikas_nm
ID: 37749318
Thanks a lot. If any queries i will post it tomorrow.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 37754740
SET XACT_ABORT ON
GO

BEGIN TRY

BEGIN TRANSACTION

DECLARE @ids TABLE ( Id int PRIMARY KEY)

INSERT INTO TABLEB ( Id, ... )
OUTPUT Id INTO @ids
SELECT Id, ...
FROM TABLEA
WHERE
    dateColumn < DATEADD(DAY, -60, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AND
    NOT EXISTS ( SELECT 1 FROM TABLEB WHERE TABLEB.ID = TABLEA.ID )
ORDER BY Id

DELETE FROM TABLEA
FROM TABLEA
INNER JOIN @ids ids ON ids.Id = TABLEA.ID

COMMIT TRANSACTION

END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION
END CATCH
0
 

Author Closing Comment

by:vikas_nm
ID: 37783657
Was really helpful and the most suitable
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question