Selecting column name dynamically in an insert query
dynamic-sql, sql, sql-server, t-sql
Solution
`@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
Problem
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 ```