CODEWITHSUNDEEP
sql-server
Jeff
Jeff

Reputation: 2313

Updating entire row efficiently using Merge

I am designing a stored procedure which will run on a schedule. It's purpose is to update a table based on results from an OpenQuery. The OpenQuery will return some completely new records, and some updated records. I currently have this written in such a manner.

DELETE A
FROM TableA A
JOIN @OpenQueryResult B
  ON A.Key1 = B.Key1 AND A.Key2 = B.Key2

INSERT INTO TableA
(.
.
.)
SELECT 
.
.
.
FROM @OpenQueryResult

Note: TableA has all the columns from @OpenQueryResult in addition to several calculated fields.

I would like to find a better way to do this. Through some research, it seems that Merge would be a good choice as it cuts down on overhead, and thus, execution time. I imagine things looking a little like this:

MERGE TableA AS target
USING @OpenQueryResult AS source
ON (target.Key1 = source.Key1 AND target.Key2 = source.Key2)
WHEN MATCHED THEN
  UPDATE ...
WHEN NOT MATCHED THEN
  INSERT ...
  VALUES ...

All the examples I've seen online explicitly list the columns that should be updated. Is there a shortcut to say "copy all matching column names"? TableA is rather massive in terms of number of columns.

Upvotes: 2

Views: 1291

Answers (1)

Andomar
Andomar

Reputation: 238226

Is there a shortcut to say "copy all matching column names"?

Nope. Gotta list them all. You can usually copy/paste the column list from somewhere. For example, right click -> script -> script as select gives you a comma-separated column list.

Upvotes: 4

Related Questions