Solved

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

Posted on 2012-03-21
8
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

726 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