| Author |
Topic |
|
debangshub
Starting Member
3 Posts |
 Posted - 2012-01-04 : 23:22:37
|
| How to add a new field between other fields using sql command |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
debangshub
Starting Member
3 Posts |
Posted - 2012-01-05 : 05:56:29
|
| Actually i want to add field in the table this way ie. suppose in table tempTable fields are field1, field2,field3 so i want to add field between field1 and field2 using ALTER COLUMN column name, how to do |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-05 : 06:24:07
|
| You need to:Create new temporary table (with columns in the desired order, including any new columns)Copy the data across from original tableDrop original tableRename temporary table to original tableAdditionally if you have Constraints, Indexes and Foreign keys etc all that has to be taken care of tooIf you make the change, visually as you want it, in SSMS Table Designer and then use the SCRIPT option (and do NOT use the SAVE menu option!) then SSMS will generate a script that will do all the necessary steps, in the right order, Bobs-your-Uncle ! |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-01-05 : 06:34:10
|
| not that column ordinal number in the database has anything at all to do with storing your data correctly.http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-05 : 11:06:11
|
| Indeed, but I do find it convenient to have columns with similar purpose grouped together - makes picking columns from GUI tools easier than if they are in some random order and I'm scrolling up-and-down trying to find the blighters! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-05 : 12:08:58
|
| Yeah, well half (but not all ... go figure!) the GUI in SSMS lists the columns in alphabetical order, which is far worse ... we have the PK columns at the top, and they are the most needed, but alphabetically they could be any proportion down the scrollable list of fields.I generate most of my DDL scripts using SSMS, I reckon its easier than doing it by hand ... but for all Sprocs and Queries I have an Sproc that I call, with table name, that generates all the column-lists, JOINs etc that I need and can easily then hand-edit as required. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-01-05 : 13:20:43
|
quote:
Originally posted by X002548
quote:
Originally posted by Kristenthe GUI in SSMS lists the columns in alphabetical order
Am I missing something? my are in ordinal positionSSMS?
Mine are too. Maybe he's using a screen we don't use though.I do not adjust the column order EVER, columns get added to the bottom with no exceptions. We can't afford the downtime. If a developer needs to see them in a certain order in the GUI, then I'd require them to throw a view on the table.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-05 : 13:48:49
|
| From memory the Column Select for Foreign Key is in Alpha order |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-05 : 14:03:16
|
I may have been slight economical-with-the-truth by saying "half the GUI in SSMS lists the columns in alphabetical order"  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-05 : 14:04:50
|
I find writing the FKey DDL manually a pain, but I have to say that the SSMS FKey GUI Form must be the worst one in SSMS so I don't reckon you are missing anything by doing those by hand  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
debangshub
Starting Member
3 Posts |
Posted - 2012-01-06 : 00:51:52
|
| Thanks a lot for the prompt reply. It helped me a lot and i'm vary grateful to all of you participated . |
|
|
|
|
Add Field between other fields using sql