update all columns in one update
--Procedure to Update all columns in a given table (@Source) w/ Join from another Table (@Dest)
CREATE PROCEDURE UPDATE_ALL
@SOURCE VARCHAR(100),
@DEST VARCHAR(100),
@ID VARCHAR(100)
AS
DECLARE @SQL VARCHAR(MAX) =
'UPDATE D SET ' +
-- Google 'for xml path stuff' This gets the rows from query results and
-- turns into comma separated list.
STUFF((SELECT ', D.'+ COLUMN_NAME + ' = S.' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @DEST
AND COLUMN_NAME <> @ID
FOR XML PATH('')),1,1,'')
+ ' FROM ' + @SOURCE + ' S JOIN ' + @DEST + ' D ON S.' + @ID + ' = D.' + @ID
--SELECT @SQL
EXEC (@SQL)
--usage:
EXEC UPDATE_ALL 'source_table','destination_table','id_column'
-- Here's a hardcore way to do it with SQL SERVER. Carefully consider security and integrity before you try it, though.
--This uses schema to get the names of all the columns and then puts together a big update statement to update all columns except ID column, which it uses to join the tables.
--This only works for a single column key, not composites.