CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Eric
Eric

Reputation: 5743

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

Upvotes: 0

Views: 87

Answers (1)

tezzo
tezzo

Reputation: 11115

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

Upvotes: 1

Related Questions