SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Add columns to table with script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ds9
Starting Member

38 Posts

Posted - 04/28/2006 :  16:38:38  Show Profile  Click to see ds9's MSN Messenger address  Reply with Quote
I have a table with 70 columns and have an extra set of columns to add; is there a way to do it via script so I don't have to add one column ata a time?


Thanks
JRosa

Kristen
Test

United Kingdom
22431 Posts

Posted - 04/28/2006 :  16:43:33  Show Profile  Reply with Quote
I would suggest:

Using Enterprise Manager: RightCLik the Table and choose Design Table

Make the changes using the VisualTools.
Then use the "Generate Change Script", and save the script
Then Abandon the change (in Table Designer)
Then Edit/modify the script, if necessary, and then run it.

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 04/28/2006 :  16:45:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
ALTER TABLE Table1 ADD COLUMN Column1 int NOT NULL, COLUMN Column2 int NOT NULL, COLUMN Column3 varchar(50) NULL, ...

Tara Kizer
aka tduggan
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 04/29/2006 :  02:02:16  Show Profile  Reply with Quote
Tara: aren't they going to need to be NULL, or have DEFAULTs?

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 04/30/2006 :  18:36:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
Not necessarily. You have to start out with a default or NULL column (using ALTER TABLE command), but you can end up with it being NOT NULL and no default (second ALTER TABLE command if needed).

Tara Kizer
aka tduggan
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 05/02/2006 :  00:54:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by tkizer

ALTER TABLE Table1 ADD COLUMN Column1 int NOT NULL, COLUMN Column2 int NOT NULL, COLUMN Column3 varchar(50) NULL, ...

Tara Kizer
aka tduggan


The keyword COLUMN is not needed

ALTER TABLE Table1 ADD Column1 int NOT NULL, Column2 int NOT NULL, Column3 varchar(50) NULL, ...

ds9, Also run this in QA

Select 'Alter table '+table_name+' Add yourcol datatype' from information_schema.tables
where table_name<>'dtProperties'

Copy the result;Paste in QA and run them one by one

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/02/2006 :  04:12:42  Show Profile  Reply with Quote
"ALTER TABLE Table1 ADD Column1 int NOT NULL, Column2 int NOT NULL, Column3 varchar(50) NULL, ..."

... you can't add a NULL column without a Default definition ...

Easier to do in EM Table Designer and then generate the Change Script (if you need a script!)

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 05/02/2006 :  04:27:10  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>you can't add a NULL column without a Default definition ...

Yes. It is

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 05/02/2006 :  11:45:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by Kristen

"ALTER TABLE Table1 ADD Column1 int NOT NULL, Column2 int NOT NULL, Column3 varchar(50) NULL, ..."

... you can't add a NULL column without a Default definition ...

Easier to do in EM Table Designer and then generate the Change Script (if you need a script!)

Kristen



I disagree about doing it in Enterprise Manager and using its change script as it has to move data around to do it that way. You can still use ALTER TABLE, but you have to issue the command twice if you need a NOT NULL column without a default value. The first time you make it NULL. The second time you alter the column to make it NOT NULL. This will perform faster than the temp table/drop table/rename table approach that EM would use for this.

Tara Kizer
aka tduggan

Edited by - tkizer on 05/02/2006 11:46:49
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/02/2006 :  12:06:11  Show Profile  Reply with Quote
But you're going to have to do an UPDATE to set all the columns to a default value, aren't you?

I suppose you could leave them NULL, and all new records would be NOT NULL, but then

a) folk will probably be assuming there are no NULL values in that column, and get their WHERE clauses wrong!

and b) the next time you do need to do an alteration that moves all the data around its going to fail on the insert-to-temporary-table for those old-version rows that still have a NULL in that column.

The script generated by EM for an additional column that is NOT NULL, and has a DEFAULT assigned, is something like:

ALTER TABLE dbo.MyTable ADD
	MyColumn tinyint NOT NULL CONSTRAINT DF_MyColumn DEFAULT (1)
GO

which seems pretty "skinny" to me - although it will cause an inherent UPDATE to set existing rows to the default, which will take a while, and plenty of Logging, for a big table.

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 05/02/2006 :  12:42:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
The problem that I have is when EM generates the new table, moving of data, dropping the table, renaming the table, etc... That shouldn't be done in this case unless there isn't much data. All I'm saying is that adding columns can be handled with ALTER TABLE as long as you add the column to the end of the table.

Tara Kizer
aka tduggan
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/02/2006 :  13:00:42  Show Profile  Reply with Quote
Yup I'm with you! So long as you set a DEFAULT for a new, NOT NULL, column then EM will make a nice skinny "append column" script. Anything more tricky and it will do the full move-data-around 9-yards script though!

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000