select differences between tables

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 2008 Forums

Transact-SQL (2008)

select differences between tables

Author

Topic

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2011-08-30 : 12:06:00

I have a table A with 60 columns. Whenever there is a change to any column in Table A, I want to write the change to table B.

For example:
Table a (Before)
ID, column 1, column 2, column 3, column 4
123, value, value, value, value
124, value, value, value, value

Table a (After)
ID, column 1, column 2, column 3, column 4
123, Modified value, value, value, value
124, value, value, value, value

Table B (Before)
Modified Date_Time,ID, column 1, column 2, column 3, column 4
123, value, value, value, value
124, value, value, value, value

Table B (After)
Modified Date_Time,ID, column 1, column 2, column 3, column 4
1/2/2011 11:00AM,123, value, value, value, value
1/2/2011 11:00AM, 124, value, value, value, value
1/2/2011 11:05AM, 123, Modified value, value, value

Is there such a thing that can be done without having to specify all 60 columns in the query?

Nick W Saban

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 12:20:58

feel like what you're asking is a trigger

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2011-08-30 : 14:21:40

Thanks Visakh16. I already have an event that I can tie the query to. What I would like to know is whether there is a query that can do what I describe without having to specify all 60 columns in the query.

Nick W Saban

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 00:10:45

you can in trigger you've two internal temporary tables called INSERTED and DELETED which assumes the structure of triggering object at run time. so you dont have to specify all the 60 columns.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources