Muti table insert

Question

Post reply

Muti table insert

sati.nagra

SSC Rookie

Points: 40
More actions
October 12, 2009 at 7:38 am

#385558

I need to insert a column at the top of a table into 400 tables. Its called company and will have a default value of FPO into it. Can someone advise if this can be done using a script please as I'm fairly new to SQlServer ?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323744 More actions · Answer 1

it's fairly easy to generate a script to add a column to every table in the database.

--company and will have a default value of FPO

SELECT

'ALTER TABLE ' + name + ' ADD COMPANY VARCHAR(30) DEFAULT ''FPO'' WITH VALUES'

FROM sysobjects WHERE xtype='U'

Now for some advice:

400 tables, and they ALL need a Company column? are you sure? isn't there a high level table, that the orther tables reference, and only THAT table needs the reference? check your design...i doubt very much 400 tables need a new column. They only time i've done an operation like this is to add a datetime column with a default so i can find when a row gets added.

anyway this will generate the script for you to review so you can decide which tables it should run on.

it will add the column to teh end of the table, NOT at the beginning.

as far as SQL goes, it does not care about the order of the columns, but end users might...you mentioned you wanted to add it at the "top" of the table....

the WITH VALUES will insert the default value for any rows that already exist.

in order to change the order of the columns, you need to rebuild the table..use the GUI for that, it does everything for you, becuase foreign keys and other constriants have to be rebuilt around the table.

It's not necessary to change the column order, but i understand reasons why it might be desired.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sati.nagra SSC Rookie Points: 40 More actions · Answer 2

Many thanks for the information. Unfortunately I do have to insert the column into all our tables as we need to seperate the data for different companies.

Lowell SSC Guru Points: 323744 More actions · Answer 3

ahh ok...you know your data better than us, of course...so if you have 400 identical tables, one for each client, that makes sense.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!