Solved

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

Posted on 2012-03-21
8
224 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Stored procedure 4 32
How to use TOP 1 in a T-SQL sub-query? 14 44
Query 14 55
Help with simplifying SQL 6 53
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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 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