SQL Server : Insert with Dynamic Queries just working when 1 Inserting Value

Question

When trying to insert a value into a table coming from a dynamic SQL this is working perfectly.

INSERT TableName(ColumnName1) EXEC('SELECT '+@ColumnName+' from kat.[dbo].[History] ')

However, when I want to insert more than 1 value with this Dynamic SQL this is not working anymore.

INSERT TableName(ColumnName1, ColumnName2) EXEC('SELECT '+@ColumnName+' from kat.[dbo].[History] '), datum from kat.[dbo].[History] .

Is there a reason for this?

May thanks in advance,

Kat

Xedni · Accepted Answer · 2017-10-17 22:08:21Z

1

Your first statement, the dynamic SQL is returning a single, distinct result set, which is being inserted into your table. Your second statement, your treating it like it's a column, which it's not. Since you're pulling from the same table, you could either include datum inside your dynamic SQL, or join back out to dbo.history after the fact

answered Oct 17, 2017 at 22:08

Xedni

4,7913 gold badges22 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Katherine99 Over a year ago

Hi Xedni, Thanks for the answer. You mean to create the following : INSERT TableName(ColumnName1, ColumnName2) EXEC('SELECT '+@ColumnName+', datum from kat.[dbo].[History] ')

Xedni Over a year ago

That was my thought, yes.

Katherine99 Over a year ago

Hi, thanks for this. You are right, this is working, as both columns are in the same table. The problem comes when you want to combine different dynamic queries from different tables or constants, concats, etc...

Collectives™ on Stack Overflow

SQL Server : Insert with Dynamic Queries just working when 1 Inserting Value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related