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

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
vikas_nmAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yawkey13Commented:
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
)
vikas_nmAuthor Commented:
What if i need to check i data already exists in the table b before inserting. how can this be done.
yawkey13Commented:
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
)
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

vikas_nmAuthor Commented:
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.
yawkey13Commented:
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.
vikas_nmAuthor Commented:
Thanks a lot. If any queries i will post it tomorrow.
Scott PletcherSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vikas_nmAuthor Commented:
Was really helpful and the most suitable
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.