[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Archive SQL Server table data

Posted on 2013-01-19
14
Medium Priority
?
313 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 23

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 23

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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 23

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 23

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 23

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 23

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 70

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

650 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