create database [Online]
go
use [Online]
go
create table OrderHeader
(
  OrderID INT PRIMARY KEY
, OrderDate DATETIME
, OrderAmount MONEY
, BillingAddressID INT
, OrderStatus TINYINT
)
GO

CREATE TABLE OrderDelta
(
  OrderID			INT
, TableName			VARCHAR(100)
, ColumnName		VARCHAR(100)
, BeforeValue		VARCHAR(100)
, AfterValue		VARCHAR(100)
, DateTimeStamp		DATETIME		
)
GO

INSERT INTO OrderHeader
VALUES (1,'20101019 00:00:00.000', 23.98,123,1)

CREATE TABLE #MyTempTable
    (  OrderID INT PRIMARY KEY
    , [Action]	VARCHAR(20)
	, BeforeOrderDate DATETIME
	, BeforeOrderAmount MONEY
	, BeforeBillingAddressID INT
	, BeforeOrderStatus TINYINT
	, AfterOrderDate DATETIME
	, AfterOrderAmount MONEY
	, AfterBillingAddressID INT
	, AfterOrderStatus TINYINT
    );

GO

CREATE PROCEDURE InsertOrderHeader
(  
  @OrderID INT 
, @OrderDate DATETIME
, @OrderAmount MONEY
, @BillingAddressID INT
, @OrderStatus TINYINT	
)
AS
SET NOCOUNT ON 

BEGIN 
	MERGE OrderHeader AS target
    USING (SELECT @OrderID, @OrderDate, @OrderAmount, @BillingAddressID, @OrderStatus) AS source (OrderID, OrderDate, OrderAmount, BillingAddressID, OrderStatus)
    ON (target.OrderID = source.OrderID)
    WHEN MATCHED THEN 
        UPDATE SET OrderDate = source.OrderDate, OrderAmount = source.OrderAmount, BillingAddressID = source.BillingAddressID, OrderStatus = source.OrderStatus
	WHEN NOT MATCHED THEN	
	    INSERT (OrderID, OrderDate, OrderAmount, BillingAddressID, OrderStatus)
	    VALUES (source.OrderID, source.OrderDate, source.OrderAmount, source.BillingAddressID, source.OrderStatus)
	    OUTPUT	deleted.OrderID, $action, deleted.OrderDate, deleted.OrderAmount, deleted.BillingAddressID, deleted.OrderStatus, 
				inserted.OrderDate, inserted.OrderAmount, inserted.BillingAddressID, inserted.OrderStatus; --INTO #MyTempTable;
END
GO

INSERT INTO #MyTempTable
EXEC InsertOrderHeader @OrderID = 1, @OrderDate = '20101019 00:00:00.000', @OrderAmount = 20.00, @BillingAddressID = 123, @OrderStatus = 1

SELECT * FROM #MyTempTable

SELECT OrderID,'Column1Name',Column1OldValue,column1NewValue
FROM TempTable
WHERE Column1OldValue <> column1NewValue
UNION ALL
SELECT OrderID,'Column2Name',Column2OldValue,column2NewValue
FROM TempTable
WHERE Column2OldValue <> column2NewValue
....