T-SQL - Merge all columns from source to target table w/o listing all the columns

Question

I'm trying to merge a very wide table from a source (linked Oracle server) to a target table (SQL Server 2012) w/o listing all the columns. Both tables are identical except for the records in them. This is what I have been using:

TRUNCATE TABLE TargetTable
INSERT INTO TargetTable
SELECT *
FROM SourceTable

When/if I get this working I would like to make it a procedure so that I can pass into it the source, target and match key(s) needed to make the update. For now I would just love to get it to work at all.

USE ThisDatabase  
GO

DECLARE
  @Columns VARCHAR(4000) = (
    SELECT COLUMN_NAME + ',' 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'TargetTable' 
    FOR XML PATH('')
    )

MERGE TargetTable AS T

USING (SELECT * FROM SourceTable) AS S
ON (T.ID = S.ID AND T.ROWVERSION = S.ROWVERSION)

WHEN MATCHED THEN 
  UPDATE SET @Columns = S.@Columns

WHEN NOT MATCHED THEN   
  INSERT (@Columns)
  VALUES (S.@Columns)

Please excuse my noob-ness. I feel like I'm only half way there, but I don't understand some parts of SQL well enough to put it all together. Many thanks.

Answer 1

As previously mentioned in the answers, if you don't want to specify the columns , then you have to write a dynamic query. Something like this in your case should help:

DECLARE
@Columns VARCHAR(4000) = (
SELECT COLUMN_NAME + ',' 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'TargetTable' 
FOR XML PATH('')
)

DECLARE  @MergeQuery NVARCHAR(MAX)
DECLARE  @UpdateQuery VARCHAR(MAX)
DECLARE  @InsertQuery VARCHAR(MAX)
DECLARE  @InsertQueryValues VARCHAR(MAX)
DECLARE  @Col VARCHAR(200)

SET @UpdateQuery='Update Set '
SET @InsertQuery='Insert ('
SET @InsertQueryValues=' Values('


WHILE LEN(@Columns) > 0
BEGIN
   SET @Col=left(@Columns, charindex(',', @Columns+',')-1);

   IF @Col<> 'ID' AND @Col <> 'ROWVERSION'
   BEGIN
      SET @UpdateQuery= @UpdateQuery+ 'TargetTable.'+ @Col + ' = SourceTable.'+ @Col+ ','
      SET @InsertQuery= @InsertQuery+@Col + ','
     SET @InsertQueryValues=@InsertQueryValues+'SourceTable.'+ @Col+ ','
   END

  SET @Columns = stuff(@Columns, 1, charindex(',', @Columns+','), '')
END

SET @UpdateQuery=LEFT(@UpdateQuery, LEN(@UpdateQuery) - 1)
SET @InsertQuery=LEFT(@InsertQuery, LEN(@InsertQuery) - 1)
SET @InsertQueryValues=LEFT(@InsertQueryValues, LEN(@InsertQueryValues) - 1)

SET @InsertQuery=@InsertQuery+ ')'+  @InsertQueryValues +')'

SET @MergeQuery=
N'MERGE TargetTable
USING SourceTable
ON TargetTable.ID = SourceTable.ID AND TargetTable.ROWVERSION =    SourceTable.ROWVERSION ' +

'WHEN MATCHED THEN ' + @UpdateQuery +
' WHEN NOT MATCHED THEN '+@InsertQuery +';'

Execute sp_executesql @MergeQuery

If you want more information about Merge, you could read the this excellent article