Old Way Change Capture

OldWayChangeCapture.sql

USE Pantheon;
GO

DECLARE @Id int = 1

-- ChangeLog data store
-- Yes, I know that they're all varchars...
DECLARE @ChangeLogTemp table
      (
          ColumnName varchar(100),
          OldValue   varchar(20),
          NewValue   varchar(20)
      );

-- Old & New value data store
DECLARE @OutputTemp table
      (
          OldColumn1 int         NOT NULL,
          NewColumn1 int         NOT NULL,
          OldColumn2 char(1)     NOT NULL,
          NewColumn2 char(1)     NOT NULL,
          OldColumn3 date        NOT NULL,
          NewColumn3 date        NOT NULL,
          OldColumn4 bigint      NULL,
          NewColumn4 bigint      NULL,
          OldColumn5 varchar(50) NOT NULL,
          NewColumn5 varchar(50) NOT NULL,
          OldColumn6 datetime    NULL,
          NewColumn6 datetime    NULL
      );

-- Actual update, outputting the old and new values
UPDATE  gac
SET     gac.Column1 = 1,
        gac.Column2 = 'B',
        gac.Column6 = GETDATE()
OUTPUT	Deleted.Column1, Inserted.Column1, 
		Deleted.Column2, Inserted.Column2, 
		Deleted.Column3, Inserted.Column3, 
		Deleted.Column4, Inserted.Column4, 
		Deleted.Column5, Inserted.Column5, 
		Deleted.Column6, Inserted.Column6
		INTO @OutputTemp
FROM	dbo.GotAnyChange AS gac
WHERE	gac.GotAnyChangeID = @Id;

-- Capture changes. 1 for each column we want to capture
INSERT INTO @ChangeLogTemp
SELECT  'Column1',
        OldColumn1,
        NewColumn1
FROM    @OutputTemp
WHERE   ISNULL(OldColumn1, NewColumn1) <> NewColumn1;

INSERT INTO @ChangeLogTemp
SELECT  'Column2',
        OldColumn2,
        NewColumn2
FROM    @OutputTemp
WHERE   ISNULL(OldColumn2, NewColumn2) <> NewColumn2;

INSERT INTO @ChangeLogTemp
SELECT  'Column3',
        OldColumn3,
        NewColumn3
FROM    @OutputTemp
WHERE   ISNULL(OldColumn3, NewColumn3) <> NewColumn3;

INSERT INTO @ChangeLogTemp
SELECT  'Column4',
        OldColumn4,
        NewColumn4
FROM    @OutputTemp
WHERE   ISNULL(OldColumn4, NewColumn4) <> NewColumn4;

INSERT INTO @ChangeLogTemp
SELECT  'Column5',
        OldColumn5,
        NewColumn5
FROM    @OutputTemp
WHERE   ISNULL(OldColumn5, NewColumn5) <> NewColumn5;

INSERT INTO @ChangeLogTemp
SELECT  'Column6',
        OldColumn6,
        NewColumn6
FROM    @OutputTemp
WHERE   ISNULL(OldColumn6, NewColumn6) <> NewColumn6;

-- Capture all changes as an XML
SELECT  ColumnName AS CN,
        OldValue   AS OV,
        NewValue   AS NV
FROM    @ChangeLogTemp
FOR XML PATH('Change'), ROOT('Changes');