Solved

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

Posted on 2012-03-21
8
218 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
 

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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:
ScottPletcher 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now