Alter Table Add New Column

[westex96]

Hi Expert,

I am new at sql so go easy. : )

I have 2118 tables I need to update with a new column name of "SECTOR". In each new Sector column I need to fill down all the blanks with the same value.

I can accomplish this separately by running the following commands one at a time.

Is there any way to run all these commands at one time?


ALTER TABLE [Sector_Power_CV300A] ADD SECTOR VARCHAR(50);
ALTER TABLE [Sector_Power_CV300B] ADD SECTOR VARCHAR(50);
ALTER TABLE [Sector_Power_CV300C] ADD SECTOR VARCHAR(50);

UPDATE [Sector_Power_CV300A] SET Sector_Power_CV300A].SECTOR= ''CV300A''
UPDATE [Sector_Power_CV300B] SET Sector_Power_CV300B].SECTOR= ''CV300B''
UPDATE [Sector_Power_CV300C] SET Sector_Power_CV300C].SECTOR= ''CV300C''

Once again thank you for your help!

RH

 

[r937]

no, there isn't



r937.com | rudy.ca

 

[westex96]

LOL...TOO BAD...I GUESS SOME TASKS WILL REQUIRE MANUAL WORK. I JUST HAVE TO ROLL UP MY SLEVE.

THANK YOU FOR ALL YOUR HELP!

RAY

 

[dbelsey]

Have you started this project yet? There is a way to ease some of the pain.

 

[westex96]

I did start one of the three projects that are similar. If you have an easier way I am all ears. I tried to create a macro but had no luck.

Thanks!

Cheers,

Ray

 

[PHV]

You don't need any update instructions:
ALTER TABLE [Sector_Power_CV300A] ADD SECTOR VARCHAR(50) DEFAULT 'CV300A';
ALTER TABLE [Sector_Power_CV300B] ADD SECTOR VARCHAR(50) DEFAULT 'CV300B';
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[carp]

And, if you have a way of capturing output, you may be able to use SQL to generate your script. For instance, in Oracle, you could do the following:

SELECT 
'ALTER TABLE '||table_name||' ADD SECTOR VARCHAR2(50) DEFAULT '||substr(table_name,INSTR('_',1,-1)+1)||';'
FROM all_tables WHERE table_name LIKE 'SECTOR_POWER%');

This will dynamically create all of your SQL statements for you, and can now be run as a script.
(NOTE: There may be a syntax lurking in the above; I haven't actually tested it. But you get the idea!).