August 9, 2002 at 6:56 am
Hi,
I have approx 300 tables that I need to insert a new column as the 5th column.
Does anyone know of a away to automate the process, please help, I realy dont want to do this manually.
August 9, 2002 at 8:10 am
Need some type of loop. You can use VB and do something like this:
'open connection goes here
set rs=cn.execute("Select * from sysobjects where .....")
do until rs.eof
cn.execute "alter table " & rs.fields("Name") & " add columnabc"
rs.movenext
loop
Do pretty much the same thing with a TSQL cursor, build up the string and use Exec(). Regardless of method, I'd recommend dumping all the table names into a table, then removing manually the ones you don't want to process (couple queries to do it maybe).
Andy
August 9, 2002 at 8:43 am
Thanks for the reply, but, maybe I wasnt as clear as I could have been.
The 300 + tables have different number of columns, columns 1 to 4 are standard which all tables have, the remainder are user defined.
What I need to do is INSERT a 5th std column.
Col1
Col2
Col3
Col4
New Column Inserted Here
Col5
etc.......
Phil
August 9, 2002 at 9:19 am
Typically, we don't consider the order of columns important in RDBMSes, as we can always specify order in our queries. If it is important in your case, however, there's no easy way to do this. If I remember right, Enterprise Manager, which allows you to re-order the columns does so by creating an entirely new table, moving data, removing the old table, and renaming the new one.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
August 9, 2002 at 10:05 am
Agree with Brian. You can do that change in a loop too, but its a lot more work. Way more than its worth in my opinion.
Andy
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply