Monitor Record Changes After External Process Is Run

Greetings all,

I have a process that I run that modifies or adds records. I need to monitor changes to the original records without using triggers.

I have working code but was wondering if someone had an easier way.

My process is as follows.

1. Clone the original table and records:



Code Snippet

CREATE PROCEDURE usp_CreateOriginalTable
@TableName as varchar(50)
AS
--Clone the table
DECLARE @NewTableClone as varchar(60)
SET @NewTableClone = 'SELECT * INTO ' + @TableName + '_ORIGINAL ' + 'FROM ' + @TableName
EXEC(@NewTableClone)

GO






2. Now run my process.
3. Now compare the two tables (Table1 and Table1_ORIGINAL):




Code Snippet
CREATE PROCEDURE usp_GetModifiedorAddedRecords
@TableName varchar(50)
AS
/*The initial code create the base for the d-sql query*/
DECLARE @myQuery as varchar(8000)
SET @myQuery = 'SELECT t1.* FROM '+ @TableName + ' t1 ' +
'LEFT JOIN ' + @TableName + '_ORIGINAL t2 ON '

/*The cursor loops through all fields in the table*/
DECLARE myCursor CURSOR
FOR

SELECT sc.Name
FROM syscolumns sc
JOIN sysobjects so
ON so.ID = sc.ID
WHERE so.Name = @TableName

OPEN myCursor
DECLARE @fieldName varchar(50)
FETCH NEXT FROM myCursor INTO @fieldName
WHILE @@FETCH_STATUS = 0
BEGIN

/*In order to check modified records, all fields must be joined with clone*/
SET @myQuery = @myQuery + 't1.' + @fieldName + ' = t2.' + @fieldName + ' AND '

FETCH NEXT FROM myCursor INTO @fieldName
END
CLOSE myCursor
DEALLOCATE myCursor

SET @myQuery = @myQuery + 'WHERE t2.' + @fieldName + ' IS NULL'
SET @myQuery = REPLACE(@myQuery, 'AND WHERE', 'WHERE')

--SELECT @myQuery
EXEC(@myQuery)
GO




Yes I know it uses a cursor but I can't find an easier way. Does anyone know of a better way of doing this or is this fine?