Best way to archive

Transact-SQL
1

Hi There,
Could I ask for your advice please.
I maintain an MS Access application that has a MS Sql Server back end.
I have been asked by the client to speed up the application by archiving records that go back to 2004
The issue is that the client still wants to be able to run reports and queries which includes the archived data and any new records since the archive.
Can anyone suggest a way to achieve this?

Thanks for any help you can give.

Best Regards,

Steve.

2

Something like this to copy Old/Stale data to the Archive Table (and delete it from the Live Table)

DECLARE @CutoffDate datetime = '20040101'     -- Set this to the appropriate cutoff date (NOTE: date of earliest entry to KEEP!!)
--
BEGIN TRANSACTION MySavePoint_01
SAVE TRANSACTION MySavePoint_02
--
INSERT INTO MyArchiveTable
SELECT *
FROM MyActualTable
WHERE SomeDate < @CutoffDate
IF @@ERROR <> 0  GOTO MyAbortLabel
--
DELETE D
FROM MyActualTable AS D
     JOIN MyArchiveTable AS A
         ON A.SomePKeyID = D.SomePKeyID
        AND A.SomeOtherPKey2 = D.SomeOtherPKey2
        AND ...
WHERE D.SomeDate < '20040101'
IF @@ERROR <> 0  GOTO MyAbortLabel
--
GOTO MySucessLabel
--
MyAbortLabel:
    ROLLBACK TRANSACTION MySavePoint_02
--
MySucessLabel:
    COMMIT TRANSACTION MySavePoint_01

Then create a VIEW which combines the Live and Archive tables:

CREATE VIEW MyViewName
AS
SELECT Col1, Col2, ... --- List ALL columns by name here, do NOT use SELECT *
FROM MyActualTable
UNION ALL
SELECT Col1, Col2, ...
FROM MyArchiveTable

and have the user query "MyViewName" instead of "MyActualTable" when they want to query ALL data, including Archive

You can create a Linked Table, in Access, which will make MyViewName available to the users in the same way as any other Access table. They can then, hopefully???!!! just pick the one they need depending on their report