0

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.

Community
  • 1
  • 1
heen
  • 1

1 Answers1

0

I'm afraid you're out of luck. The onlt way to do an update is

set a.col1 = b.val1, a.col2 = b.val2....

There might be a DBMS or two which have different syntactical options, but this is the standard.

simon at rcl
  • 7,326
  • 1
  • 17
  • 24