t-SQL UPDATE table after adding columns to it with ADD TABLE

Question

I have to add several columns to the table and then update them in SQL Server 2008. The table definition boils down to this:

CREATE TABLE tbl (id INT PRIMARY KEY, 
                  dvt NVARCHAR(32), 
                  dd NVARCHAR(32));
INSERT INTO tbl (id, dvt, dd) 
 VALUES(1, '1', NULL);
INSERT INTO tbl (id, dvt, dd) 
 VALUES(2, '', NULL);
INSERT INTO tbl (id, dvt, dd) 
 VALUES(3, '2,5', NULL);
INSERT INTO tbl (id, dvt, dd) 
 VALUES(4, '13, 34, 45, 5', NULL);
INSERT INTO tbl (id, dvt, dd) 
 VALUES(5, '-1, 8, 10', NULL);
INSERT INTO tbl (id, dvt, dd) 
 VALUES(6, '-2, -10', NULL);

How do I add data to the table in the same transaction that created the table?

Now that you've changed your code, the sentence after the code block (I get an error: "Invalid column name 'd0") makes no sense. It would be better if you rolled back your question to the previous version and posted your solution as an answer. — Andriy M
– Andriy M, Commented Aug 10, 2012 at 5:22
Sorry, man. Someone just below suggested that I update it in my original post. Now you tell me to roll it back. — ahmd0
– ahmd0, Commented Aug 10, 2012 at 6:31
Perhaps I misinterpreted your update, then. I thought you found a solution and changed the code in your question to indicate that. If the problem isn't solved yet, fine, but is the error message Invalid column name 'd0 still relevant? You posted it before modifying the code, and you didn't remove it after the last update. — Andriy M
– Andriy M, Commented Aug 10, 2012 at 7:13
No, I did find the solution and it's posted above. I updated the original question to make it less confusing. So this post is now technically becomes a "statement." — ahmd0
– ahmd0, Commented Aug 10, 2012 at 8:40
Sorry @AndriyM, he said the new code (which was the result of the answer below and subsequent comments) was not working, so I asked him to post the exact new code in the question. I intended it to be an additional sample, not an edit to the original. I guess I need to be much, much, much more explicit. — Aaron Bertrand
– Aaron Bertrand, Commented Aug 10, 2012 at 10:48

Adriaan Stander · Accepted Answer · 2012-08-10 03:50:29Z

5

Try something like

SET XACT_ABORT ON     
**--BEGIN TRANSACTION** 

ALTER TABLE tbl ADD d0 SMALLINT NULL 
ALTER TABLE tbl ADD d1 SMALLINT NULL 
ALTER TABLE tbl ADD d2 SMALLINT NULL 

GO

UPDATE tbl 
 SET 
 d0 = 1, 
 d1 = 2, 
 d2 = 3 

**--COMMIT TRANSACTION** 
SET XACT_ABORT OFF 

SELECT * FROm tbl

Without the transaction

answered Aug 10, 2012 at 3:50

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

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

11 Comments

ahmd0 Over a year ago

Thanks. But I need the whole bunch to execute atomically (or in one transaction)?

Adriaan Stander Over a year ago

OK, try it with the transaction included, but with the GO where I placed it, that should work.

ahmd0 Over a year ago

Forgot to say, there's also an IF statement enclosing the whole sequence. When I add GO to it it says invalid syntax near UPDATE. (Sorry, for adjusting it post-fact)

Adriaan Stander Over a year ago

Then you might have to have the same if condition twice, with the go statement inbetween, and the alter in the first and the update in the second.

ahmd0 Over a year ago

Sorry, still no go. In SQL Fiddle I get "Explicit commits are not allowed." and in my actual SQL Server Express 2008 I get "Incorrect syntax near 'GO'."

|

Kirk Woll · Accepted Answer · 2014-01-08 23:23:10Z

2

The update statement must be enclosed in EXEC() block.

SET XACT_ABORT ON     
BEGIN TRANSACTION

IF 1=1 BEGIN  --used for simplicity to illustrate condition

ALTER TABLE tbl ADD d0 SMALLINT NULL 
ALTER TABLE tbl ADD d1 SMALLINT NULL 
ALTER TABLE tbl ADD d2 SMALLINT NULL 

EXEC('UPDATE tbl SET d0 = 1, d1 = 2, d2 = 3')

END

COMMIT TRANSACTION
SET XACT_ABORT OFF 

SELECT * FROm tbl

(This answer is actually ahmd0's answer, hence posting it as community wiki. However, his putting his answer in his question made it difficult for googlers like myself to idenitfy (and upvote) the answer we like.)

answered Jan 8, 2014 at 23:23

community wiki

Kirk Woll

Collectives™ on Stack Overflow

t-SQL UPDATE table after adding columns to it with ADD TABLE

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related