1

I am working on a data migration from a current prod table to a new table. I have prepared and tested the batch insert script based on the [Keycol] and [col8] (datetime) . This import is expected to go on for about 10 days.

--old table
SELECT TOP (1000) [Keycol],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10]
FROM [dbo].[oldtable]

--newtable with identity column
SELECT TOP (1000) [NewIdentitycol],[Keycol],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10]
FROM [dbo].[newtable]

I am preparing the insert/update/delete trigger on oldtable to capture any insert/update/delete after I started the import and do the same in my newtable.

I am new to triggers and most of the references I see in Google just tracks any one column for an update. I basically want to write trigger which captures update to any column in the tracked table and if that record is already imported into the new table, apply that update.

I took reference from https://www.sqlshack.com/solve-identity-crisis-sql-server/

Insert looks straight forward, but not sure what I tried in update/delete works for my case. I am going to do the POC, is there a better way of handing this. Please let me know.

CREATE TRIGGER Migration_trigger
ON dbo.oldtable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- INSERTS ONLY
    
    INSERT INTO dbo.newtable
        ([Keycol],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10])
    SELECT Inserted.[Keycol],Inserted.[col2],Inserted.[col3],Inserted.[col4],Inserted.[col5],Inserted.[col6],Inserted.[col7],Inserted.[col8],Inserted.[col9],Inserted.[col10]
    FROM Inserted
    LEFT JOIN Deleted
    ON Deleted.[Keycol] = Inserted.[Keycol]
    WHERE Deleted.[Keycol] IS NULL;
    
 
    -- DELETE ONLY already imported record
    IF(SELECT [Keycol] from dbo.newtable n
    join inserted on n.[keycol] = inserted.[keycol]
    join deleted on inserted.[keycol] = Deleted.[keycol] where n.[Keycol]= Deleted.[Keycol]) IS NOT NULL
    BEGIN
        DELETE 
        FROM dbo.newtable
        WHERE newtable.[Key] IN (
        SELECT Deleted.[Keycol]
        FROM Deleted
        LEFT JOIN Inserted
        ON Deleted.[Keycol] = Inserted.[Keycol]
        WHERE Inserted.[Keycol] IS NULL);
    END

    -- UPDATE ONLY already imported record

        IF(SELECT [Keycol] from dbo.newtable n
    join inserted  on n.[keycol] = inserted.[keycol]
    join deleted on inserted.[keycol] = Deleted.[keycol] where n.[Keycol]= Deleted.[Keycol] and [Keycol]=inserted.[Keycol] ) IS NOT NULL
    BEGIN
        UPDATE dbo.newtable 
        SET [Keycol] = Inserted.[Keycol],[col2]=Inserted.[col2],[col3]=Inserted.[col3],[col4]=Inserted.[col4],[col5]=Inserted.[col5],[col6]=Inserted.[col6],
        [col7]=Inserted.[col7],[col8]=Inserted.[col8],[col9]=Inserted.[col9],[col10]=Inserted.[col10]
        FROM Inserted
        INNER JOIN Deleted
        ON Deleted.[Keycol] = Inserted.[Keycol]
        INNER JOIN dbo.newtable
        ON newtable.[Keycol] = Inserted.[Keycol]
    END
END
     

Thanks

0

2 Answers 2

1

It's almost right, however the IF (SELECT is going to fail if there are multiple rows, and in any case is unnecessary (if there are no rows then nothing will happen).

Furthermore

  • Use a WHERE NOT EXISTS instead of LEFT JOIN...IS NULL, it's usually faster.
  • Use table aliases, and don't quote names unnecessarily, to make it more readable.
  • Bail early if there are no rows.
  • In the DELETE you can use EXCEPT to make it more readable.
  • In the UPDATE you do not need to update the primary key, as you are joining on that anyway.
    Also do not use the full table name on the first line, only the alias
CREATE OR ALTER TRIGGER Migration_trigger
ON dbo.oldtable
AFTER INSERT, UPDATE, DELETE
AS

IF @@ROWCOUNT = 0
    RETURN;  -- early bail-out

SET NOCOUNT ON;

INSERT INTO dbo.newtable
    (Keycol, col2, col3, col4, col5, col6, col7, col8, col9, col10)
    SELECT i.Keycol, i.col2, i.col3, i.col4, i.col5, i.col6, i.col7, i.col8, i.col9, i.col10
    FROM inserted i
    WHERE NOT EXISTS (SELECT 1
        FROM deleted d
        WHERE d.Keycol = i.Keycol
    );    
 
DELETE 
    FROM dbo.newtable
    WHERE newtable.Key IN (
        SELECT d.Keycol
        FROM deleted d
        EXCEPT
        SELECT i.Keycol
        FROM inserted i
    );

UPDATE n
SET col2 = i.col2,
    col3 = i.col3,
    col4 = i.col4,
    col5 = i.col5,
    col6 = i.col6,
    col7 = i.col7,
    col8 = i.col8,
    col9 = i.col9,
    col10 = i.col10
FROM inserted i
JOIN deleted ON d.Keycol = i.Keycol
JOIN dbo.newtable n ON n.Keycol = i.Keycol;     

You can also use a MERGE

CREATE OR ALTER TRIGGER Migration_trigger
ON dbo.oldtable
AFTER INSERT, UPDATE, DELETE
AS

IF @@ROWCOUNT = 0
    RETURN;  -- early bail-out

SET NOCOUNT ON;

WITH source AS (
    SELECT
      i.*,
      ActualKey = ISNULL(i.Keycol, d.Keycol)
    FROM inserted i
    FULL JOIN deleted d ON d.Keycol = i.Keycol
)
MERGE dbo.newtable AS n
USING source AS i
ON i.ActualKey = n.Keycol
WHEN NOT MATCHED THEN
  INSERT
    (Keycol, col2, col3, col4, col5, col6, col7, col8, col9, col10)
  VALUES (i.Keycol, i.col2, i.col3, i.col4, i.col5, i.col6, i.col7, i.col8, i.col9, i.col10)
WHEN MATCHED AND i.Keycol IS NULL THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    col2 = i.col2,
    col3 = i.col3,
    col4 = i.col4,
    col5 = i.col5,
    col6 = i.col6,
    col7 = i.col7,
    col8 = i.col8,
    col9 = i.col9,
    col10 = i.col10
;
5
  • Thank you so much @Charlieface - Keycol is unique clustered index and I was under the assumption that the update/delete trigger will fail if it cannot find that record in target table (similar to how it works in transactional replication). I will test the first script. I think that suits my requirement. Jul 14, 2023 at 11:35
  • About the merge approach, I dont want the merge to insert if the record is not yet in the newtable because if it inserts it before my import job attempts to insert it, my import job will fail with key violation error. new records that gets inserted after my import start date will be handled by the insert trigger. Jul 14, 2023 at 11:35
  • Good point. What do you want to happen, do you want to just insert that new row? The Merge version will just insert it, but the Insert/Update/Delete version will get an error if the row exists already Jul 14, 2023 at 11:55
  • Good point, forgot that, was too caught up in OP's original code. Maybe you should write that as an answer rather than a comment........... Jul 14, 2023 at 13:49
  • My requirement is if the record is not present in the new table, update/delete trigger should silently fend without doing anything. This means the actual record which got updated in the current table will eventually imported into new table by the batch job Jul 14, 2023 at 15:30
0

You don't need to write the batch transfer and trigger code from scratch. It can be tricky to get everything right, handle error conditions properly, and make the whole process resumable.

Michael J Swart has an easy-to-read series starting with Modifying Tables Online – Part 1: Migration Strategy

The general idea is the same as yours:

  1. Create a staging table
  2. Mirror ongoing changes to the original table using triggers
  3. Migrate data in batches
  4. Swap the tables at the end

He uses separate triggers for insert, update, and delete in his AdventureWorks example since that makes the code simpler:

/*
create triggers to maintain new table
*/
 
use AdventureWorks2012;
go
 
create trigger t_i_SalesOrderHeader
on Sales.SalesOrderHeader
after insert
as
    set identity_insert Sales.SalesOrderHeader_new on;
 
    insert SalesOrderHeader_new(SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
    select SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate
    from inserted
 
    set identity_insert Sales.SalesOrderHeader_new off;
go
 
create trigger t_u_SalesOrderHeader
on Sales.SalesOrderHeader
after update
as
    -- assuming pk values are never altered.
    update Sales.SalesOrderHeader_new
    set
        RevisionNumber = i.RevisionNumber,
        OrderDate  = i.OrderDate,
        DueDate  = i.DueDate,
        ShipDate  = i.ShipDate,
        [Status]  = i.[Status],
        OnlineOrderFlag  = i.OnlineOrderFlag,
        PurchaseOrderNumber  = i.PurchaseOrderNumber,
        AccountNumber  = i.AccountNumber,
        CustomerID  = i.CustomerID,
        SalesPersonID  = i.SalesPersonID,
        TerritoryID  = i.TerritoryID,
        BillToAddressID  = i.BillToAddressID,
        ShipToAddressID  = i.ShipToAddressID,
        ShipMethodID  = i.ShipMethodID,
        CreditCardID  = i.CreditCardID,
        CreditCardApprovalCode = i.CreditCardApprovalCode,
        CurrencyRateID  = i.CurrencyRateID,
        SubTotal  = i.SubTotal,
        TaxAmt  = i.TaxAmt,
        Freight  = i.Freight,
        Comment  = i.Comment,
        rowguid  = i.rowguid,
        ModifiedDate = i.ModifiedDate
    from Sales.SalesOrderHeader_new s
    join inserted i
        on s.SalesOrderID = i.SalesOrderID;
go
 
create trigger t_d_SalesOrderHeader
on Sales.SalesOrderHeader
after delete
as
    delete Sales.SalesOrderHeader_new
    from Sales.SalesOrderHeader_new s
    join deleted d
        on d.SalesOrderID = s.SalesOrderID ;
go

Small improvements are possible, but the basic implementation is sound as a template to work from. I would add the following to the start of each trigger, for example:

IF ROW_COUNT_BIG() = 0 BEGIN RETURN END;
SET NOCOUNT ON;

Make sure you read and understand all five parts of the series. They're quite short.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.