Merge to update all target table columns

Question

I have the same table in 2 Databases

-----------------------------------
|           table                 |
-----------------------------------
| ID | col1 | col2 | ... | col(n) |

I need merge statement to update rows with matched IDs like this statement:

MERGE [DB1]..[table]
USING [DB2]..[table] ON [DB1]..[table].[ID] = [DB2]..[table].[ID]
WHEN MATCHED THEN
    UPDATE SET [DB1]..[table].[*] = [DB1]..[table].[*]

the problem is I have more than 30 columns so its difficult to set values column by column .

Answer 1

There is no way to use a wildcard character or keyword to specify the target update columns and mappings (even if all columns are identical). You can get a head start on all the typing by clicking on the Columns node in Object Explorer, and dragging it (and hence the column names) onto your query window:

So now you don't have all that much typing to do, just a bunch of manipulation and copying and pasting.

And I wouldn't use MERGE for this anyway, for various reasons (including the fact that the syntax is rather cumbersome when only an UPDATE is involved), and as mentioned in my comment above. See this post for details and also this landing page.

UPDATE trg
  SET col1 = src.col1,
      col2 = src.col2, 
      ...
  FROM DB1.dbo.table AS trg
  INNER JOIN DB2.dbo.table AS src
    ON trg.ID = src.ID;

Generating that UPDATE statement (or any statement, really) dynamically, assuming all the column names and types are identical and compatible, is not all that difficult in T-SQL, unlike the wall of code apparently required in PL/SQL. Let's imagine you have these two matching tables:

CREATE TABLE dbo.table1(id int, a varchar(32), b date);
CREATE TABLE dbo.table2(id int, a varchar(32), b date);

Here is the dynamic SQL we could use (and half the complexity is just making sure it's pretty, protecting you from poorly chosen names, etc.):

DECLARE @TargetTable nvarchar(520) = N'dbo.table1',
        @SourceTable nvarchar(520) = N'dbo.table2',
        @JoinColumn  sysname       = N'id',
        @columns     nvarchar(max) = N'',
        @sql         nvarchar(max) = N'UPDATE trg SET ';

SELECT @columns += N',
         trg.'  + QUOTENAME(t.name) 
  + N' = src.'  + QUOTENAME(s.name)
FROM sys.columns AS t
INNER JOIN sys.columns AS s
ON t.name = s.name
WHERE t.name <> @JoinColumn
AND t.[object_id] = OBJECT_ID(@TargetTable)
AND s.[object_id] = OBJECT_ID(@SourceTable);

SELECT @sql += STUFF(@columns, 1, 1, N'') + N'
  FROM ' + @TargetTable + N' AS trg
    INNER JOIN ' + @SourceTable + N' AS src 
    ON trg.'    + QUOTENAME(@JoinColumn)
  + N' =  src.' + QUOTENAME(@JoinColumn) + N';';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Output:

UPDATE trg SET 
         trg.[a] = src.[a],
         trg.[b] = src.[b]
  FROM dbo.table1 AS trg
    INNER JOIN dbo.table2 AS src 
    ON trg.[id] =  src.[id];

The join on t.name = s.name could be looser, e.g. if you know the mapping between the tables isn't precise, you could hard-code the mapping in that join, like ON t.name = CASE WHEN t.name = N'foo' THEN s.name = N'bar' [...] or you could store that mapping in a table and join to it, too.

If the objects are in different databases or even on different servers, it gets a little more complex, but not really. Imagine you have t1 and t2 in two different databases:

USE db1;
GO
-- target table:
CREATE TABLE dbo.table1(id int, a varchar(32), b date);
GO
USE db2;
GO
-- source table:
CREATE TABLE dbo.table2(id int, a varchar(32), b date);

You have to pick one of the databases to execute from, and the other database to run the dynamic SQL in. I'm choosing to execute dynamic SQL in the source database from the context of the target database. From different servers would be the same except there'd be a 4-part name instead of a 3-part name (and an assumption about a valid linked server, auth, etc).

USE db1; -- target!
GO

DECLARE @TargetDB       sysname       = DB_NAME(), -- assume local DB
        @TargetTable    nvarchar(520) = N'dbo.table1',
        @SourceDB       sysname       = N'db2',
        @SourceTable    nvarchar(520) = N'dbo.table2',
        @JoinColumn     sysname       = N'id',
        @columns        nvarchar(max) = N'',
        @sql            nvarchar(max) = N'UPDATE trg SET ',
        @exec           nvarchar(500),
        @execSQL        nvarchar(max),
        @TargetObjectID int;

SELECT @exec = QUOTENAME(@SourceDB) + N'.sys.sp_executesql',
       @TargetObjectID = OBJECT_ID(@TargetTable);

SET @execSQL = N'DECLARE @columns nvarchar(max) = SPACE(0);

  SELECT @columns += N'', trg.'' + QUOTENAME(t.name)
  + N'' = src.''  + QUOTENAME(s.name)
FROM ' + QUOTENAME(@TargetDB) + N'.sys.columns AS t
INNER JOIN /* SourceDB is where it runs! */ sys.columns AS s
ON t.name = s.name
WHERE t.name <> @JoinColumn
AND t.[object_id] = @TargetObjectID
AND s.[object_id] = OBJECT_ID(@SourceTable);

SELECT @sql += STUFF(@columns, 1, 1, SPACE(0)) + N''
  FROM ' + QUOTENAME(@TargetDB) + N'.' + @TargetTable + N' AS trg
    INNER JOIN ' + QUOTENAME(@SourceDB) + N'.' + @SourceTable + N' AS src 
    ON trg.'' + QUOTENAME(@JoinColumn)
  + N'' =  src.'' + QUOTENAME(@JoinColumn) + N'';'';
  
PRINT @sql;
--EXEC sys.sp_executesql @sql';
  
EXEC @exec @execSQL, N'@sql nvarchar(max),
  @JoinColumn sysname, 
  @TargetObjectID int, @SourceTable nvarchar(520)', 
  @sql, @JoinColumn, @TargetObjectID, @SourceTable;

Output is a little less pretty but similar to the original:

UPDATE trg SET  trg.[a] = src.[a], trg.[b] = src.[b]
  FROM [db1].dbo.table1 AS trg
    INNER JOIN [db2].dbo.table2 AS src 
    ON trg.[id] =  src.[id];

But, I mean, dragging the columns onto your query window (even twice) is a whole lot simpler. Sometimes you don't need to build a Rube Goldberg machine to put a query together.