I am trying to pass the local variable @DB1ColNames into the WHERE clause for SELECT @DB2ColNames (see below). Passing the local variable is not working, however if I cut and paste the results for @DB1ColNames (ex. 'Column1', 'Column2', 'Column3') into the WHERE clause for @DB2ColNames it works.
DECLARE @DB1ColNames VARCHAR(MAX) DECLARE @DB2ColNames VARCHAR(MAX) SELECT @DB1ColNames= STUFF ( ( SELECT ', '''+t1.COLUMN_NAME +'''' FROM INFORMATION_SCHEMA.COLUMNS t1 WHERE t1.TABLE_NAME = t2.TABLE_NAME ORDER BY t1.ORDINAL_POSITION FOR XML PATH(''), TYPE ).value('.','VARCHAR(MAX)' ) , 1,2,SPACE(0)) FROM INFORMATION_SCHEMA.COLUMNS t2 WHERE TABLE_NAME='DB1Table' GROUP BY t2.TABLE_NAME SELECT @DB2ColNames= STUFF ( ( SELECT ',' + quotename([SQLDB2_NAME].sys.columns.name) +'' AS ColumnName FROM [SQLDB2_NAME].sys.columns INNER JOIN [SQLDB2_NAME].sys.tables ON [SQLDB2_NAME].sys.columns.object_id = [SQLDB2_NAME].sys.tables.object_id WHERE ([SQLDB2_NAME].sys.columns.name IN (@DB1ColNames)) AND ([SQLDB2_NAME].sys.tables.name = N'DB2Table') FOR XML PATH(''), TYPE ).value('.','VARCHAR(MAX)' ) , 1,1,SPACE(0)) FROM [SQLDB2_NAME].INFORMATION_SCHEMA.COLUMNS t2 WHERE TABLE_NAME ='DB2Table' GROUP BY t2.TABLE_NAME PRINT @DB1ColNames PRINT @DB2ColNames
I am trying to find all of the columns from a table in another database (SQLDB2_NAME) that match the columns in a table from the current database. And eventually populate the table in the current database with the records from another database that match the column names.
