July 9, 2012 at 5:39 am
Dear All,
Is it possible to insert a column into a table at any place,
by default it is adding at the end.
Appreciating your help always.
July 9, 2012 at 5:42 am
If you use the GUI Right Click on the table > Design > Right Click on the area you want to insert > Insert Column
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 9, 2012 at 5:51 am
You can't do it by script, except by creating a new table and inserting into it.
SSMS can help you generate the script for that if you follow Andy's instructions, but before applying, click on the Generate Change Script button which looks like a Scroll with a floppy disk.
Hope this is of help.
July 9, 2012 at 5:53 am
There are two ways you can add a column to the table:
1. Table design (GUI)
2. ALTER TABLE statement
Using the table design you can add a column at any place and it gives you a generated script. This script copies data into a temporary table then drops the original table and renames the temporary table to original table back. This script ensures your data is retained after making schema changes to the table. But if the table is huge then it takes long time to execute this script.
Second, when using ALTER TABLE statement the column is added at the end of all the columns, ALTER TABLE statement also retains the data after making schema changes to the table and does not take much time to execute if the table is huge.
Regards,
Ravi.
July 9, 2012 at 6:11 am
SQL Mad Rafi (7/9/2012)
Dear All,Is it possible to insert a column into a table at any place,
.
as others have already said...the answer is yes.
but why would you want to ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 9, 2012 at 6:20 am
J Livingston SQL (7/9/2012)
SQL Mad Rafi (7/9/2012)
Dear All,Is it possible to insert a column into a table at any place,
.
as others have already said...the answer is yes.
but why would you want to ?
It's a little extra work, but I always try to add a new column 'in the right place' where applicable. I concede that it doesn't actually make any difference from a technical standpoint however it pays in terms of 'readability' for me and others who may have to support my database.
This is, of course, a personal preference.
July 9, 2012 at 7:07 am
It's a little extra work, but I always try to add a new column 'in the right place' where applicable. I concede that it doesn't actually make any difference from a technical standpoint however it pays in terms of 'readability' for me and others who may have to support my database.
This is, of course, a personal preference
+1 I agree 100%! 🙂
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 9, 2012 at 11:20 am
So, making clear what others are saying is... No. You cannot simply add a column to an existing table wherever you want. However, by using the Design option in SSMS on the table, you can move columns anywhere you want and it "appears" that this just moves them in the existing table. This is not really the case. The table (as mentioned earlier) must be dropped and then recreated with new DDL. SSMS will move the data to a temporary table while this is being done and will insert it back into the newly created table.
So, IMHO, the actual answer to the question is no.
Jared
CE - Microsoft
July 9, 2012 at 11:33 am
Just to add a little more to this discussion, if there is a large amount of data in the table and you are using SSMS to modify the table; generate the change script, copy it to a query window in SSMS, cancel the GUI update to the table, and run the script directly. The reason is that the GUI update has a good chance of timing out. I have run into that a few times when first starting out.
July 9, 2012 at 11:38 am
Lynn Pettis (7/9/2012)
Just to add a little more to this discussion, if there is a large amount of data in the table and you are using SSMS to modify the table; generate the change script, copy it to a query window in SSMS, cancel the GUI update to the table, and run the script directly. The reason is that the GUI update has a good chance of timing out. I have run into that a few times when first starting out.
+1 Depending on how busy the table is, I may simply create a new table as I want it and insert the data from the current table. sp_rename twice and done. Or a drop table, sp_rename... Whatever is best for the situation and your DR in case of an issue.
Jared
CE - Microsoft
July 9, 2012 at 11:48 am
Personally, I just add new columns to the end of the table. I will do as I am told, but will argue my point as where the column is located in the list of columns has no bearing on where SQL Server actually stores the data in the record.
July 9, 2012 at 12:10 pm
Andy Hyslop (7/9/2012)
It's a little extra work, but I always try to add a new column 'in the right place' where applicable. I concede that it doesn't actually make any difference from a technical standpoint however it pays in terms of 'readability' for me and others who may have to support my database.
This is, of course, a personal preference
+1 I agree 100%! 🙂
hmmm....how do you define "readabilty"..?
do you arrange columns by col_name alphabetically , by data_type, ??
just playing devil's advocate 😛
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 9, 2012 at 12:16 pm
Well, personally, I like my createdBy, createdDAte, modifiedBy, modifiedDate at the end of the table. Of course, it really doesn't matter... but it makes it seem more logically organized. I like my primary key columns to be first. It gives an organization scheme to the table that is easily viewed in SSMS or any GUI that is easier on the brain. Coming from a background in psychology with some study in industrial organizational psych, I can see how a persons' logical interpretation being matched with a visual representation can help make development easier. It is really a preference or psychological thing though.
Jared
CE - Microsoft
July 9, 2012 at 12:22 pm
SQLKnowItAll (7/9/2012)
Well, personally, I like my createdBy, createdDAte, modifiedBy, modifiedDate at the end of the table. Of course, it really doesn't matter... but it makes it seem more logically organized. I like my primary key columns to be first. It gives an organization scheme to the table that is easily viewed in SSMS or any GUI that is easier on the brain. Coming from a background in psychology with some study in industrial organizational psych, I can see how a persons' logical interpretation being matched with a visual representation can help make development easier. It is really a preference or psychological thing though.
If my tables have those columns, I actually prefer them at the front of the table. But then, I always try to add new columns at the end.
July 9, 2012 at 12:32 pm
Lynn Pettis (7/9/2012)
SQLKnowItAll (7/9/2012)
Well, personally, I like my createdBy, createdDAte, modifiedBy, modifiedDate at the end of the table. Of course, it really doesn't matter... but it makes it seem more logically organized. I like my primary key columns to be first. It gives an organization scheme to the table that is easily viewed in SSMS or any GUI that is easier on the brain. Coming from a background in psychology with some study in industrial organizational psych, I can see how a persons' logical interpretation being matched with a visual representation can help make development easier. It is really a preference or psychological thing though.If my tables have those columns, I actually prefer them at the front of the table. But then, I always try to add new columns at the end.
Proves my point (I think) that we all have things that make sense to us and those may differ between individuals, companies, etc. I think from a business standpoint it is good to have some consistent design patterns that can be implemented to make things easier across the organization.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply