Unable to Insert into table using EXEC

Question

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 1

You should escape quotes as double quotes, take a look at this simplified query:

DECLARE @sql varchar(20) = 'SELECT 1';
EXEC ('EXEC('''+@sql+''')')

Answer 2

I think problem is in quotes near nice. It should be:

'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'

Answer 3

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'

Answer 4

Put enough quotes into the @UpdateString :

DECLARE @UpdateString VARCHAR(max) = '''SELECT ''''text'''',1''';
EXEC ('EXEC('+@UpdateString+')')