CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
user1289451
user1289451

Reputation: 921

Create list of column names for insert into table

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

Answers (2)

Dance-Henry
Dance-Henry

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

vercelli
vercelli

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

Related Questions