June 10, 2002 at 9:19 am
Hi.
How can I insert a new column (USING the ALTER command and NOT Enterprice Manager) into a table between the last 2 columns, such that I end up with ;
OLD_COLUMN1
NEW_COLUMN
OLD_COLUMN2
The table I am altering is a live database table (obviously with data-lots of data)
Tnx
June 10, 2002 at 9:25 am
Dont think you can. Even using EM it will bcp the data out, change the structure, bcp it back in - if you're changing column order. Why do you care where the column goes?
Andy
June 10, 2002 at 9:32 am
The reason I want the new column before the last one is the the last column is a Reporting_Date field and it MUST be the last field of the table
June 10, 2002 at 10:20 am
I am confussed by that statement, SQL does not care where an item appears in a table. Nor should a program if done properly. The only time order will be an issue is whe a person queries like so
SELECT * FROM tblX
However if the columns where A, B and C and I want C, B, and A I can query like so.
SELECT C, B, A FROM tblX
and I get the results in the order I expect. For what you want to do thou you will need to use EM or write a really good script to read permissions, triggers, keys, constraints, indexes, and structure. Create a new table (different name), copy the data from the original table, drop the original table, rename the new table and add back the items I listed to make sure table is properly set back. You will also need to make sure you have enough room for two copies of the table in your database and on the file system. Otherwise EM does all except the last bit for you.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 10, 2002 at 10:55 am
Even if you do have a program that expects it to be in a certain ordinal position, you can fix that usually by changing the name of the table and replacing it with a correctly ordered view.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply