Add columns to table with script

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

Script Library

Add columns to table with script

Author

Topic

ds9
Starting Member

38 Posts

Posted - 2006-04-28 : 16:38:38

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

22859 Posts

Posted - 2006-04-28 : 16:43:33

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-28 : 16:45:08

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

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-04-29 : 02:02:16

Tara: aren't they going to need to be NULL, or have DEFAULTs?

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-30 : 18:36:10

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-02 : 00:54:41

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

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 04:12:42

"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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-02 : 04:27:10

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

Yes. It is

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-02 : 11:45:53

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

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 12:06:11

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-02 : 12:42:16

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

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 13:00:42

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources