| Author |
 Topic  |
|
|
ds9
Starting Member
38 Posts |
 Posted - 04/28/2006 : 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
United Kingdom
22191 Posts |
Posted - 04/28/2006 : 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
USA
35007 Posts |
Posted - 04/28/2006 : 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
United Kingdom
22191 Posts |
Posted - 04/29/2006 : 02:02:16
|
Tara: aren't they going to need to be NULL, or have DEFAULTs?
Kristen |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 04/30/2006 : 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
India
22461 Posts |
Posted - 05/02/2006 : 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
United Kingdom
22191 Posts |
Posted - 05/02/2006 : 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
India
22461 Posts |
Posted - 05/02/2006 : 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
USA
35007 Posts |
Posted - 05/02/2006 : 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 |
Edited by - tkizer on 05/02/2006 11:46:49 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 05/02/2006 : 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
USA
35007 Posts |
Posted - 05/02/2006 : 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
United Kingdom
22191 Posts |
Posted - 05/02/2006 : 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 |
|
|
| |
Topic |
|
Add columns to table with script
