Solved

Archive SQL Server table data

Posted on 2013-01-19
14
299 Views
Last Modified: 2013-02-08
SQL Server 2005:

I need to archive part of the records in 2 linked tables.

I was planning on copying the tables so they can be used for looking up old records if necessary.

In the tables I am copying, I need to delete all records older than 2 years.

How would I go about that.

Thanks!
0
Comment
Question by:dave_sky
[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
  • 6
  • 6
  • 2
14 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38796944
Since you're doing it for archiving and they won't be updated any more, I would assume that you can do away with what I'm assuming is the foreign key linking the two fields.

Hopefully, one or both tables have a date column that you can use as your reference.

delete from table1
where datecolumn <= dateadd(yy, -2, getdate())

Then you can do the same for table2.

If table2 doesn't have a date column you could do something like

delete from table2
where linkedcolumn not in (select linkedcolumn from table1)
0
 

Author Comment

by:dave_sky
ID: 38796964
What format would the date have to be?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38797046
If you column is defined as datetime, the above will work automatically.

If you have an artificial date column (a char or varchar where a date is stored in a format like YYYYMMDD for example), let me know and I will modify accordingly.

As always, make sure you run in test before running in production.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:dave_sky
ID: 38797403
The data type is: nvarchar(10)

The dates are stored in a mixed format: some records M/DD/YYYY and some  MM/DD/YYYY

I appreciate the help.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38797429
Applying a function to the left part of the where will invalidate any indexes in the update but since you have date stored as plain text, there's really no choice.

delete from table1
where convert(datetime, datecolumn, 101) <= dateadd(yy, -2, getdate())

That will make datecolumn a datetime field for comparison purposes.

As mentioned above, test first!

Here's the BOL entry for the convert function
http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 

Author Comment

by:dave_sky
ID: 38797454
I used this query on table1:

delete from tblPlumbingBid
where convert(datetime, BidDate, 101) <= dateadd(yy, -2, getdate())

Got this error:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_tblPlumbingBidDetail_tblPlumbingBid". The conflict occurred in database "MySQL", table "dbo.tblPlumbingBidDetail", column 'PlumbingBidKey'.
The statement has been terminated.

I have no date column in table2.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38797505
Try this:

I would think there's some column common to the two tables called BidID or something ?

delete from tblPlumbingBidDetail
where BidID in (select BidID from tblPlumbingBid where where convert(datetime, BidDate, 101) <= dateadd(yy, -2, getdate())

You may want to actually do something like this instead so that getdate() isn't a moving target since you're deleting from 2 tables:

declare @today datetime
set @today = getdate()

delete from tblPlumbingBidDetail
where BidID in (select BidID from tblPlumbingBid where where convert(datetime, BidDate, 101) <= dateadd(yy, -2, @today)

Open in new window


Then run your original delete, changing getdate() to @today
0
 

Author Comment

by:dave_sky
ID: 38798741
Tried this query:

delete from tblPlumbingBidDetail
where PlumbingBidKey in (select PlumbingBidKey from tblPlumbingBid where convert (datetime, BidDate, 101) <= dateadd(yy, -2, getdate())

Got the error message:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.

I tried everything I knew with no luck...
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38798938
Looks like there's a missing ) at the end of the line

Try:

delete from tblPlumbingBidDetail
where PlumbingBidKey in 
  (select PlumbingBidKey 
   from tblPlumbingBid 
   where convert (datetime, BidDate, 101) <= dateadd(yy, -2, getdate())
  )

Open in new window

0
 

Author Comment

by:dave_sky
ID: 38799242
That took care of the previous error - now I am getting this error:

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38799336
You must have some bad data in your table.

You may have to examine it to make sure it's all valid

Dates in format MM/DD/YYYY with or without leading zeroes on single digit days and months should still work  - I'm thinking you may have some rows with junk in this column.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38803278
You need to physically capture the keys to be deleted -- you can't afford to have a new (unarchived) key value slip into the delete when the row wasn't archived first.  And use a transaction so that none or all of the tables are modified, not just one table, or a few tables out of the total.

Something like this:


DECLARE @archive_date datetime
SET @archive_date = GETDATE()

DECLARE #PlumbingBidKeys_Archived (
    PlumbingBidKey int PRIMARY KEY
)

INSERT INTO #PlumbingBidKeys_Archived
SELECT PB.PlumbingBidKey
FROM dbo.tblPlumbingBid PB
WHERE
    1 = CASE
        WHEN ISDATE(BidDate) = 0 THEN 0
        WHEN BidDate < DATEADD(YEAR, -2, GETDATE()) THEN 1
        ELSE 0 END
ORDER BY
    PB.PlumbingBidKey

BEGIN TRY
BEGIN TRANSACTION

INSERT INTO dbo.PlumbingBidArchive ( ... )
SELECT @archive_date, ...
FROM dbo.tblPlumbingBid PB
INNER JOIN #PlumbingBidKeys_Archived PBKA ON
    PBKA.PlumbingBidKey = PB.PlubmingBidKey

INSERT INTO dbo.PlumbingBidDetailArchive ( ... )
SELECT @archive_date, ...
FROM dbo.tblPlumbingBidDetail PBD
INNER JOIN #PlumbingBidKeys_Archived PBKA ON
    PBKA.PlumbingBidKey = PBD.PlubmingBidKey

DELETE FROM PBD
FROM dbo.tblPlumbingBidDetail PBD
INNER JOIN #PlumbingBidKeys_Archived PBKA ON
    PBKT.PlumbingBidKey = PBD.PlumbingBidKey

DELETE FROM PB
FROM dbo.tblPlumbingBid PB
INNER JOIN #PlumbingBidKeys_To_Archive PBKTA ON
    PBKTA.PlumbingBidKey = PB.PlumbingBidKey

COMMIT TRANSACTION
END TRY

BEGIN CATCH
IF XACT_STATE() = 1
    ROLLBACK TRANSACTION
END CATCH
0
 

Author Comment

by:dave_sky
ID: 38806620
I tried this query:

DECLARE @archive_date datetime
SET @archive_date = GETDATE()

DECLARE #PlumbingBidKeys_Archived (
    PlumbingBidKey int PRIMARY KEY
)

INSERT INTO #PlumbingBidKeys_Archived
SELECT PB.PlumbingBidKey
FROM dbo.tblPlumbingBid PB
WHERE
    1 = CASE
        WHEN ISDATE(BidDate) = 0 THEN 0
        WHEN BidDate < DATEADD(YEAR, -2, GETDATE()) THEN 1
        ELSE 0 END
ORDER BY
    PB.PlumbingBidKey

BEGIN TRY
BEGIN TRANSACTION

INSERT INTO dbo.PlumbingBidArchive ( ... )
SELECT @archive_date, ...
FROM dbo.tblPlumbingBid PB
INNER JOIN #PlumbingBidKeys_Archived PBKA ON
    PBKA.PlumbingBidKey = PB.PlumbingBidKey (

INSERT INTO dbo.PlumbingBidDetailArchive ( ... )
SELECT @archive_date, ...
FROM dbo.tblPlumbingBidDetail PBD
INNER JOIN #PlumbingBidKeys_Archived PBKA ON
    PBKA.PlumbingBidKey = PBD.PlumbingBidKey

DELETE FROM PBD
FROM dbo.tblPlumbingBidDetail PBD
INNER JOIN #PlumbingBidKeys_Archived PBKA ON
    PBKT.PlumbingBidKey = PBD.PlumbingBidKey

DELETE FROM PB
FROM dbo.tblPlumbingBid PB
INNER JOIN #PlumbingBidKeys_To_Archive PBKTA ON
    PBKTA.PlumbingBidKey = PB.PlumbingBidKey

COMMIT TRANSACTION
END TRY

BEGIN CATCH
IF XACT_STATE() = 1
    ROLLBACK TRANSACTION
END CATCH

And got these errors.

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'FROM'.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 38806682
This:

DECLARE #PlumbingBidKeys_Archived (
    PlumbingBidKey int PRIMARY KEY
)

was a typo and needs to be replaced by:

IF OBJECT_ID('tempdb..#PlumbingBidKeys_Archived') IS NOT NULL
    DROP TABLE #PlumbingBidKeys_Archived
CREATE TABLE #PlumbingBidKeys_Archived (
    PlumbingBidKey int PRIMARY KEY
)


As for the rest, you need to replace the '...' with the appropriate column names from the actual tables.

I suggest you include either an archive date or some other archive code, such as an archive_id, that will let you know when a particular row was archived.
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

737 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