I know there are a lot of threads regarding similar issues but nothing have really gotten me forward.
I want to update a table in database 2 with what was updated in the corresponding table in database 1 through a trigger. They have the same table structure. Using MS SQL 2008.
There are really no problems doing this in an AFTER INSERT trigger just like
INSERT INTO DB2..Table1
SELECT *
FROM inserted
WHERE column1 = @column1
But when doing this AFTER UPDATE-style
UPDATE DB2..Table1
SET column2 = @column2 --etcetera in some way
FROM DB1..Table1
WHERE column1 = @column1 --or something not important
There are 100+ columns as well as changes/additions to the table columns (although DB1..Table1 and DB2..Table1 will always have the same structure and columns) so setting a value for each column that is to be updated feels like a really bad choice.
What I really want to write is:
UPDATE DB2..Table1
SET DB2..Table1 = (SELECT * FROM inserted WHERE column1 = @column1) alternatively DB2..Table1 = DB1..Table1
WHERE column1 = @column1
Additionally I have to set fixed values for 3 out of the 100+ columns. Those three columns will be the only difference between the DB1 table and the DB2 table. That is also fine when a new row is inserted but makes me wonder how I'm supposed to do that in the case of an update.
Any ideas?
Similar question as this one: Update multiple columns in SQL but I really hope that there is a better way to do it that I haven't thought of.
