Sql Update With Dynamic Column Names

EDIT: Database names have been modified for simplicity I'm trying to get some dynamic sql in place to update static copies of some key production tables into another database (sql2

Solution 1:

You have a mix of SQL and dynamic SQL in your query against information_schema. Also QUOTENAME isn't necessary in the where clause and will actually prevent a match at all, since SQL Server stores column_name, not [column_name], in the metadata. Finally, I'm going to change it to sys.columns since this is the way we should be deriving metadata in SQL Server. Try:

SELECT  @colnames += ',' + name 
  FROM Prod.sys.columns
  WHERE OBJECT_NAME([object_id]) = @objname
  AND name <> CASE WHEN @objname = 'TableXx' THEN 'ExcludeCol1' ELSE '' END
  AND name <> CASE WHEN @objname = 'TableYy' THEN 'ExcludeCol2' ELSE '' END;

SET @colnames = STUFF(@colnames, 1, 1, '');