Selecting column name dynamically in an insert query

Question

Getting error Invalid column name '@ColumnNames'. in the last line (insert clause), any idea why ?

Declare @ColumnNames varchar(2000)
Declare @OrderId int
set @OrderId = 110077

select @ColumnNames = COALESCE(@ColumnNames + ', ', '') + COLUMN_NAME
    from 
        INFORMATION_SCHEMA.COLUMNS
    where 
        TABLE_NAME='OrderItems'

Insert into dbo.OrderHistory(@ColumnNames) select * from dbo.[Order] where ID= @OrderId

Solution 1

@ColumnNames is a string of text, not a list of columns. As a result, when you try to use it as a list of column names in the insert query, it fails.

You can use dynamic SQL to do what you desire, like so:

declare @insertquery nvarchar(1000)
set @insertquery = N'insert into dbo.orderhistory(' + @ColumnNames + ') select * from dbo.[Order] where ID=' + cast(@OrderId as nvarchar(10))

sp_executesql @insertquery

Solution 2

You should use dynamic sql. And dont forget to perform data casting constructing query string!

Declare @ColumnNames varchar(2000)
Declare @OrderId int
set @OrderId = 110077

select @ColumnNames = COALESCE(@ColumnNames + ', ', '') + COLUMN_NAME
    from 
        INFORMATION_SCHEMA.COLUMNS
    where 
        TABLE_NAME='OrderItems'
Declare @DynSqlStatement varchar(max);
set @DynSqlStatement = 'Insert into dbo.OrderHistory('+ @ColumnNames + ') 
      select * from dbo.[Order] where ID= ' + cast(@OrderId as varchar(10));
exec( @DynSqlStatement );

Comments

[Adrian Dymorz]

I had to add EXEC before sp_executesql