Archive SQL Server table data

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)

What format would the date have to be?

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.

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.

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

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.

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)

Select allOpen in new window

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

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...

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())
  )

Select allOpen in new window

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.

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.

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

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'.