question

cchapel avatar image
cchapel asked

Using a comma separated string local variable in dynamic sql

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.

dynamic-sqlstringcomma-delimited
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
cchapel avatar image
cchapel answered

I solved this by joining the sys.tables and sys.columns from both databases then using FOR XML PATH.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.