• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

Archive SQL Server table data

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
dave_sky
Asked:
dave_sky
  • 6
  • 6
  • 2
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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
 
dave_skyAuthor Commented:
What format would the date have to be?
0
 
Steve WalesSenior Database AdministratorCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dave_skyAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
dave_skyAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
dave_skyAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
dave_skyAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
dave_skyAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now