Pranay Deep Pranay Deep - 4 months ago 9
SQL Question

Unable to Insert into table using EXEC

EXEC('INSERT INTO T_MyTable('+ @Columns +')
EXEC ('+ @UpdateString + ')'
)


Where @Columns contains comma seperated column names and @UpdateString contains

'Update T_OtherTable
Set col1 = 123,
col2 = 456,
col3 = 'nice'
OUTPUT
DELETED.col1 as Old_FirstCol
INSERTED.col1 as New_FirstCol
DELETED.col2 as Old_SecondCol
INSERTED.col2 as New_SecondCol
DELETED.col3 as Old_ThridCol
INSERTED.col3 as New_ThirdCol
Where ID = 1'


I am getting incorrect syntax error. Can't we use EXEC inside EXEC?

Answer

you are missing comma after column names in OUTPUT,

'Update T_OtherTable
Set col1 = 123,
col2 =  456,
col3 = ''nice''
OUTPUT
DELETED.col1 as Old_FirstCol,
INSERTED.col1 as New_FirstCol,
DELETED.col2 as Old_SecondCol,
INSERTED.col2 as New_SecondCol,
DELETED.col3 as Old_ThridCol,
INSERTED.col3 as New_ThirdCol
Where ID = 1'

Also its better to directly insert data to the table from OUTPUT clause then using nested EXEC. It should be like,

'Update T_OtherTable
Set col1 = 123,
col2 =  456,
col3 = ''nice''
OUTPUT
DELETED.col1 as Old_FirstCol,
INSERTED.col1 as New_FirstCol,
DELETED.col2 as Old_SecondCol,
INSERTED.col2 as New_SecondCol,
DELETED.col3 as Old_ThridCol,
INSERTED.col3 as New_ThirdCol
INTO T_MyTable('+ @Columns +')
Where ID = 1'
Comments