Solved

Archive SQL Server table data

Posted on 2013-01-19
14
290 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
  • 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
 

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

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…
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…
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…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 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

11 Experts available now in Live!

Get 1:1 Help Now