Update all columns in a table with the columns in another table with same name and data type

Question

This is for SQL Server 2019. I have TableA with around 100 columns and want to update TableB which has the exact same column names and exact same data types.

Insert was easy using the built in generate insert script. However could not find an easy way to do this using the generated update script. TableA has a column called modifiedOn which I'm using to filter data.

My script would be similar to

UPDATE TableB
SET Column1 = Column1,
    Column2 = Column2,
    ....   Upto
    Column100 = Column100
WHERE TableA.id = TableB.id
  AND TableA.ModifiedOn > DateAdd(day,-1,GETDATE())

Answer 1

As multiple people suggested, you can build this dynamically from sys.columns, but this is rather simplistic because it assumes you don't need to care about identity columns, computed columns, rowversion, and other target columns that can't be updated directly, or cases where the data types are incompatible, or the data types don't quite match (and the target column can't accept values from the source), or constraints or triggers might prevent some values from being set.

DECLARE @srcTable sysname = N'TableA',
        @trgTable sysname = N'TableB',
        @joinCol  sysname = N'id',
        @cols     nvarchar(max),
        @update   nvarchar(max);

SELECT @cols = STRING_AGG(CONVERT(nvarchar(max), 
    CONCAT(N'   trg.', QUOTENAME(src.name), 
           N' = src.', QUOTENAME(src.name))), 
           ',' + char(13) + char(10) + space(4))
    WITHIN GROUP (ORDER BY src.column_id)
  FROM sys.columns AS src
  INNER JOIN sys.columns AS trg
  ON src.name = trg.name
  INNER JOIN sys.tables AS srct
    ON srct.[object_id] = src.[object_id]
  INNER JOIN sys.tables AS trgt
    ON trgt.[object_id] = trg.[object_id]
  WHERE src.name <> @joinCol
    AND srct.name = @srcTable
    AND trgt.name = @trgTable;

SELECT @update = CONCAT(N'UPDATE trg SET 
', @cols, N'
FROM dbo.', @srcTable, N' AS src
INNER JOIN dbo.', @trgTable, N' AS trg
ON src.', QUOTENAME(@joinCol), N' = trg.',
          QUOTENAME(@joinCol), N'
WHERE src.ModifiedOn >= DATEADD(DAY,-1,getdate());');

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

I'd show a fiddle, but, db<>fiddle seems to be non-operational at the moment. I created these tables locally:

CREATE TABLE dbo.TableA(id int primary key, 
  a sysname, b nvarchar(32), c bigint, d bigint,
  ModifiedOn datetime);

CREATE TABLE dbo.TableB(id int primary key, 
  a sysname, b nvarchar(64), c int, d int, e bigint,
  ModifiedOn datetime);

And the code above generated this script (note that e was left out because it isn't present in both tables):

UPDATE trg SET 
       trg.[a] = src.[a],
       trg.[b] = src.[b],
       trg.[c] = src.[c],
       trg.[d] = src.[d],
       trg.[ModifiedOn] = src.[ModifiedOn]
    FROM dbo.TableA AS src
    INNER JOIN dbo.TableB AS trg
    ON src.[id] = trg.[id]
    WHERE src.ModifiedOn >= DATEADD(DAY,-1,getdate());