| Author |
Topic |
|
dba and prog
Starting Member
5 Posts |
 Posted - 2002-03-15 : 08:36:11
|
| I am trying to alter a table by adding a column some where in the middle of the table. The column cannot be at the end of the table, and has to be in the middle of the table due to the standards we have. How can I do this without using to Enterprise Manager, but acually scripting it?? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-15 : 08:37:44
|
| It makes absolutely no difference where the column is positioned in the table, it will have zero effect on how you access it or use it. |
 |
|
|
dba and prog
Starting Member
5 Posts |
Posted - 2002-03-15 : 08:42:22
|
quote:
It makes absolutely no difference where the column is positioned in the table, it will have zero effect on how you access it or use it.
No, it makes a difference for us, I need the newly added column in the middle of the table due to the standards we have. We are developing a huge project, and all of the last 5 columns are the same in each table, so if I add a new column it has to be added before the 5th column in the table. How can I alter the table?? |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-03-15 : 08:44:37
|
| OK From my understanding, what Enterprise Manager does when you do it there is this.Copies your data to a temp table.Drops your tableRecreates it with the new insert columnCopies your data back inSo, you could do something like that, or use EM.Damian |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-15 : 08:47:01
|
| There is never anything 'magical' about enterprise manager. To accomplish this, just do what it does, copy the table out to a temp table, drop and re-create it, pump the data back in. (don't forget to rebuild your index, drop any foreign key constraints before you drop the table and re build them later, etc.)Standards should be designed to improve performance, not hinder it.JayEdit: arg, can't a guy get a word in edgewise around here? :) . . . I once had a database where column position mattered for audit purposes (created,createdby,modified,modifiedby). We made those the 1st four columns in the tables, so we would never have your problem. Just a thoughtEdited by - Jay99 on 03/15/2002 08:49:40 |
|
|
|
dba and prog
Starting Member
5 Posts |
Posted - 2002-03-15 : 08:47:40
|
quote:
OK From my understanding, what Enterprise Manager does when you do it there is this.Copies your data to a temp table.Drops your tableRecreates it with the new insert columnCopies your data back inSo, you could do something like that, or use EM.Damian
Sorry, I cannot drop the table either, they would want me to do this without dropping the tables. |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-15 : 08:48:59
|
| The you are S.O.L., my friend.Edited by - Jay99 on 03/15/2002 08:50:05 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-15 : 08:49:44
|
| If you use Enterprise Manager, you can select and drag the last 5 columns down one notch, it will leave a blank space where you can fill in the new column.No offense, but whoever thought of that standard shouldn't be allowed near a database, EVER, certainly not to suggest design requirements. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-03-15 : 08:51:19
|
Basically...tough  You have to compromise on something. Why can't you drop a table ? Sure there might be constraints, indexes, etc etc, but they can all be scripted.Or is the table going to be changing a lot ? If that is the case, there might be some better ways of designing some of it.Damian |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-15 : 08:51:28
|
quote:
If you use Enterprise Manager...
Using EM will drop and recreate the table when you save the design . . .Jay |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-15 : 08:52:44
|
quote:
...If that is the case, there might be some better ways of designing some of it.Damian
Like putting your column position specific field at the beginning of the table . . .Jay |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-15 : 08:54:14
|
| The other thing you can try is to add the column to the end, then re-design the table in EM and drag the new column to it's final position.I hope this sounds silly, because it really is. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-03-15 : 08:54:31
|
That's it Jay, short posts, you can get them in quicker   Damian |
|
|
|
dba and prog
Starting Member
5 Posts |
Posted - 2002-03-15 : 08:54:59
|
quote:
quote:
If you use Enterprise Manager...
Using EM will drop and recreate the table when you save the design . . .Jay
You guys, I know EM will do the trick, or dropping the table will to, but they were asking me to come up with a script for production w/out dropping anything? |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-03-15 : 08:56:38
|
| Then "they" don't know what they are on about .. Stupid they!Sorry man, something has to give.Why can't you drop a table and recreate it ?Damian |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-15 : 09:03:30
|
quote:
... but they were asking me to come up with a script for production w/out dropping anything?
ok, kill all the active spids, pause the server, take a backup of the production database, copy the back-up to your beta or dev server, restore that backup there, either us EM or the drop re-create meathod to do this on your beta or dev server (where EM and drop/create is hopefully allow), backup your changed database, copy it back to production, restore the database, unpause the server, give you boss a big middle finder (EDIT: that should be big middle finger  ). . . I think that'll do the trick.JayEdited by - Jay99 on 03/15/2002 09:12:32 |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-03-15 : 09:10:16
|
| Tell they that if they want an automoated way of keeping a column in a certain position other than the first position, then you must drop and re-add the table. If this in unacceptable, then they have one of two choices: 1) Relax on the specific position requirement or 2) realize this process will never be automated.Have they given a reason as to why you cannot drop and re-create the table? This ought to be interesting. Jeremy |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-03-15 : 09:12:52
|
| "They"The mysterious overlords of SQL Server DBA policy.Damian |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-03-15 : 11:07:39
|
| I always thought it was "Them"!===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-03-15 : 12:31:38
|
| This reminds me of Graz's article: SQL Query Asked by a Recruiter where my favorite of Graz's solutions ends with "Ask them why they don't provide the proper tools for their development teams." Only in this case it is "Ask them why they don't let their developers use the proper tools that they already have?"<edit>It sounds to me like the real answer is to redesign whatever system is requiring the fields to be in a specific order. As has been mentioned so many times, field order, while convenient, should never be necessary.</edit>------------------------GENERAL-ly speaking...Edited by - AjarnMark on 03/15/2002 12:33:15 |
|
|
|
DGMelkin
Starting Member
24 Posts |
Posted - 2002-03-15 : 13:17:41
|
| Would this fit the requirement?1. Rename old table and associated indexes, constraints, etc. something like tableOld2. Create new table, named table with the columns in the correct order and all assocated indexes, constraints, permissions, etc.3. Do an insert from the old table to the new one.this way, you haven't dropped the original table and you can recover easily in the case of a problem. This can be done very nicely in a script and you can even add checks to end when errors occur. I do this for our upgrade scripts.-d |
|
|
|
Next Page
|
Altering Tables by adding columns in the middle