Create list of column names for insert into table

Question

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?

prepare `@sql` variable which will have final query, print this on the debug screen and figure out what is going on — techspider, Sep 09 '16 at 14:59

score 3 · Answer 1 · answered Sep 09 '16 at 15:02

3

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

answered Sep 09 '16 at 15:02

vercelli

4,717
2
13
15

1

Thanks, I combined both your answers to get [this](https://stackoverflow.com/a/57516715/558211) – Rubenisme Aug 15 '19 at 22:01

score 1 · Answer 2 · answered Sep 09 '16 at 17:14

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)

Create list of column names for insert into table

2 Answers2

Linked