Archiving SQL Server Data

Question

PROBLEM UPDATED, PLEASE SEE END OF THIS POST:

I have two tables that I need to archive data from. The first is an INVOICE table that stores the customer, supplier, store, and invoice numbers. The second is an INVOICE LINE ITEM table that stores the supplier/invoice number, as well as a line item number and specific item information. In other words the INVOICE table ties the invoice to the customer/supplier, while the INVOICE LINE ITEM table ties the items to that particular invoice.

The goal is to archive any data from these tables greater than 2 years old. The problem is that there is no date on the INVOICE LINE ITEM table, only the INVOICE table. Here is what I have so far:

IF @CutOffDate IS NULL 
BEGIN
    SET @CutOffDate = DATEADD(mm, -24, CURRENT_TIMESTAMP)

END
ELSE
BEGIN
    IF @CutOffDate > DATEADD(mm, -24, CURRENT_TIMESTAMP)
    BEGIN
        RAISERROR ('Cannot delete data from last 24 months', 16, 1)
        RETURN -1
    END
END

BEGIN TRAN


    INSERT INTO archive.INVOICE
            SELECT *
            FROM INVOICE
    WHERE invoice_date < @CutOffDate

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRAN
        RAISERROR ('Error occured while moving data from INVOICE', 16, 1)
        RETURN -1
    END

    INSERT INTO archive.INVOICE_LINE_ITEM
            SELECT *
            FROM INVOICE_LINE_ITEM
    WHERE invoice=
        (Select invoice
        From INVOICE
        WHERE invoice_date < @CutOffDate
        )

  IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRAN
        RETURN 0
    END

END

The problem is, I get this error whenever I try to execute it:

Msg 208, Level 16, State 1, Procedure ArchiveData, Line 26
Invalid object name 'archive.INVOICE'.
Msg 266, Level 16, State 2, Procedure ArchiveData, Line 26
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

But I know for a fact that archive.INVOICE does exist (I'm looking at it right now). I've been fighting with this for days, please help! If you need any more information let me know!

EDIT:

Thank you Demo, it turns out that the first error was happening because the other database was in a separate schema. Pretty silly that I didn't notice.

However, now the first portion works, but I run into a problem when I get to this code:

INSERT INTO argus_archive.argus.INVOICE_LINE_ITEM
        SELECT *
        FROM argus.INVOICE_LINE_ITEM
        WHERE invoice=
            (Select invoice
            From argus.INVOICE
            WHERE invoice_date < @CutOffDate
            )

The goal is to archive all the rows that correspond to the Invoices that were just archived, but since each Invoice has multiple rows in the Line Items table, I get this error:

Msg 512, Level 16, State 1, Procedure ArchiveData, Line 38
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Answer 1

Like the error message suggests, your sub-query is returning more than one value, and can't be compared using =.

Instead, I would suggest a JOIN, and add a DISTINCT to match the intended original functionality:

INSERT INTO argus_archive.argus.INVOICE_LINE_ITEM
SELECT DISTINCT ilt.*
FROM 
    argus.INVOICE_LINE_ITEM ilt
    JOIN argus.INVOICE i
        ON i.invoice = ilt.invoice 
        AND i.invoice_date < @CutOffDate

EDIT:

To Delete:

DELETE ilt
FROM 
    argus.INVOICE_LINE_ITEM ilt
    JOIN argus.INVOICE i
        ON i.invoice = ilt.invoice 
        AND i.invoice_date < @CutOffDate