Updating a column at a time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Updating a column at a time

Author

Topic

rushdib
Yak Posting Veteran

93 Posts

Posted - 2003-12-23 : 16:02:25

Hi,
I have a source table and a target table. Both tables are identical. Each table has 100 columns. I want to update target table from the source table. I used dynamic sql inside a cursor so I don't have to code the 100 columns inside the update statement. The problem I am facing is that I want audit which column has been changed, basically a log table containing the following:
Key
Date modified
ColumnModified
BeforeChange
AfterChange

The following is my code for the update:

declare @sqlUpdate nvarchar(200)
declare @columnName nvarchar(50)
declare column_cursor cursor for select ColumnName from dbo.tblColumns
open column_cursor
fetch next from column_cursor into @columnName
begin
while (@@fetch_status = 0)
begin
set @sqlUpdate = 'update tbl1 set tbl1.' + @columnName + ' = tbl2.' + @columnName + '' +
' , DateModified = getDate(), UpdateFlag = 1 from' + ' dbo.table1 tbl1, dbo.table2 tbl2 where' +
' tbl1.CustNo = tbl2.CustNo and tbl1.' + @columnName + ' <> tbl2.' + @columnName + ''
fetch next from column_cursor into @columnName
end
close column_cursor
deallocate column_cursor
end

Thank you,
Rushdi

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-23 : 16:42:30

Have a look at Nigels Audit Trigger:

http://www.nigelrivett.net/AuditTrailTrigger.html

rushdib
Yak Posting Veteran

93 Posts

Posted - 2004-01-05 : 10:05:53

Thank you so much, it's the exact what I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources