Reputation: 921
I'm working on a query where I need to insert rows into a table and a temp table that have the same column names.
I would like to avoid repeating the column names in each insert statement and would prefer to create a list and just pass the list to the insert statements.
Here's what I have so far:
DECLARE @columnNameList varchar(MAX) = COALESCE(RecType, ColumnName,
SourcePosition, MaxWidth, isNullable, dotNetDataType, [Format])
EXEC('INSERT INTO #RowList (' + @columnNameList + ')
VALUES......
This almost works. I get an error for each column name that looks like this:
Invalid column name 'RecType'
I think the error is because the column name has single quotes around it as it is being converted to a string.
Is there a way to build a list of column objects rather than strings to pass in?
Upvotes: 1
Views: 4079
Reputation: 953
Try this query below.
create table #rowlist (RecType int, ColumnName int,
SourcePosition int, MaxWidth int, isNullable int, dotNetDataType int, [Format] int)
DECLARE @columnNameList nvarchar(MAX) ='(RecType, ColumnName,
SourcePosition, MaxWidth, isNullable, dotNetDataType, [Format])'
DECLARE @SQLCMD nvarchar(MAX) =N'INSERT INTO #RowList ' + @columnNameList + N' VALUES (1,1,1,1,1,1,1)'
exec(@sqlcmd)
Upvotes: 1
Reputation: 4767
You can create a single line with all the columns of your table (myTableName
here) separated with commas with STUFF
and XML PATH
select STUFF((select ','+a.name
from sys.all_columns a join sys.tables t
on a.object_id = t.object_id
and t.name = 'myTableName'
order by a.column_id
for xml path ('')
),1,1,'')
OUTPUT
column1,column2,...,columnN
Upvotes: 3