Dynamic SQL Add New Column and Work with it

Is there any way to add column and select / update that new column in dynamic SQL?

CREATE TABLE #test (Id int primary key)

exec('
BEGIN TRAN

    ALTER TABLE #test ADD [Id_copy] int null
    --GO -- Cannot do GO in exec, otherwise below statement should work normally

    SELECT * FROM #test -- [Id_copy] shows up
    SELECT [Id_copy] FROM #test -- Error: Invalid column name ''Id_copy''.
    UPDATE #test SET Id_copy = Id -- Error: Invalid column name ''Id_copy''.

COMMIT TRAN
')

DROP TABLE #test

Answers


You can use 2 different EXEC:

CREATE TABLE #test (Id int primary key)

EXEC('
BEGIN TRAN
    ALTER TABLE #test ADD [Id_copy] int null
COMMIT TRAN
')

EXEC('
BEGIN TRAN
    SELECT * FROM #test
    SELECT [Id_copy] FROM #test
    UPDATE #test SET Id_copy = Id
COMMIT TRAN
')

DROP TABLE #test

Need Your Help

Python maintaining list of values

python list python-2.7 linked-list

I am trying to make colour maps using 3 specific lists(peak, X3 and Y3). The code of the lists that I have written where built using the insert function. The code looks like follow: