Question Regarding Script for Update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question Regarding Script for Update

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-11-13 : 05:14:54

Hi

I am trying to create a simple script to do the following:

Alter Tables to put in new columns

Create a New Table

Insert 5 rows into this new table

Update a row in a column in a particular table

So first off I am unsure about scripting in sql server.

I have built a basic script and keep getting these errors:

Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '('.

Here is the script with substituted names for the columns and data:

ALTER table dbo.table1 ADD newColumn VARCHAR(10) NULL

ALTER table dbo.table2 ADD (newColumn INT NULL, 
				    newColumn2 VARCHAR(4) NULL, 
				    newColumn3 VARCHAR(10) NULL)

ALTER table dbo.table3 ADD newColumn VARCHAR(10) NULL

ALTER table dbo.table4 ADD newColumn VARCHAR(10) NULL

ALTER table dbo.table5 ADD (newColumn INT NULL, 
				   newColumn2 VARCHAR(4) NULL,
				   newColumn3 VARCHAR(4) NULL)

-- Creating new table to hold new data

CREATE TABLE NewTable

(

	column		int	     NOT NULL,	
	column2		varchar(50)  NOT NULL,		
	column3		varchar(10)  NOT NULL,		
	column4		varchar(10)  NOT NULL,		
	column5	        bit	     NOT NULL,			
	column6  	bit	     NOT NULL,			
	column7 	bit	     NOT NULL		

)


-- Insert new values into new table

INSERT INTO NewTable VALUES (0,	'value1', 'value1', 'value1', 0, 0, 1)
INSERT INTO NewTable VALUES (1,	'value2', 'value2', 'value2', 1, 1, 0)
INSERT INTO NewTable VALUES (2,	'value3', 'value3', 'value3', 0, 1, 0)
INSERT INTO NewTable VALUES (3,	'value4', 'value4', 'value4', 0, 0, 1)
INSERT INTO NewTable VALUES (4,	'value5', 'value5', 'value5', 0, 0, 1)


-- Inserting new version number of System


UPDATE TABLE dbo.SystemParameters 
SET ParamValue = '2.3.2.0'
WHERE ParamName = 'SystemVersion'

Questions:

I also want to know about using begin transaction and end transaction because I would like to rollback the transaction if it causes any problems. Do I use 'begin transaction' and 'rollback transaction' round the whole script.

Do I need to use semi-colon ';' after each statement or '/' as in Oracle script? I have tried with and without and doesn't make any difference from what I can see.

And I tried running one line of script (the top one) and it was successful but when I went to check new table column in sql enterprise manager it is as if the system had frozen. I couldn't right click on table and show table design. When I done rollback it let me back in.

Why am I getting errors shown above?

Thanks for any help

G

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-13 : 05:42:08

Your questions in order:

quote:
I also want to know about using begin transaction and end transaction because I would like to rollback the transaction if it causes any problems. Do I use 'begin transaction' and 'rollback transaction' round the whole script.

Yes you can do this or you can enclose any segment of your script in a transaction. You can nest transactions but be aware that if you issue a ROLLBACK TRANSACTION statement you will rollback ALL the transactions in your nested transaction statement, not just the highest level transaction you are in.

Books online link: http://msdn.microsoft.com/en-us/library/ms188929(SQL.90).aspx

quote:

Do I need to use semi-colon ';' after each statement or '/' as in Oracle script? I have tried with and without and doesn't make any difference from what I can see.

No. There are very few cases where you have to use ; Starting a CTE when it is not the first line of a batch is the only one commonly encountered.

quote:

And I tried running one line of script (the top one) and it was successful but when I went to check new table column in sql enterprise manager it is as if the system had frozen. I couldn't right click on table and show table design. When I done rollback it let me back in.

Sounds like you did this inside a transaction and because you never issued a ROLLBACK or COMMIT statement then management studio was locked out of seeing that table until you had.

Check out BOOKS ONLINE -- best documentation.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources