Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
satya068
Posting Yak Master
233 Posts |
 Posted - 2012-10-25 : 04:19:58
|
| Hello,I need to add a column with datatype varchar50 to my existing table.i dont want to truncate the data present in the table,when i try to add a column through design its not allowing me to add.Is there any other way to add a column to the table without truncating the data?Thanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-10-25 : 04:50:47
|
ALTER TABLE yourTable ADD newColumnName VARCHAR(50)
Too old to Rock'n'Roll too young to die. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-10-25 : 05:03:58
|
are you modifying an existing column or adding a new column ?If you are adding a new column, there is not issue of data get truncated.If you are modifying an existing column, from a larger size to a smaller size, for example varchar(100) to varchar(50), the data will be truncated. If you want to preserve the existing data, add a new column same size as the existing, copy the data from old column to new. Change the old column to your required size. Note that these only preserved your old data. When you modify the old column from varchar(100) to varchar(50), data in the old column will still be truncated.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
satya068
Posting Yak Master
233 Posts |
Posted - 2012-10-25 : 05:26:23
|
| Hi ,Thanks for your reply,when i alter the table,a new column is added at the end, but i need that column to be included in the middle because at the end of the table there are CURRENT_FLAG,DATE_FROM AND DATE_TO fields present,i dont want amy columns present after these fields.Thanks |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-10-25 : 05:40:44
|
| The order of the columns shouldn't matter.To do this you will need to create a new table and copy the data.The reason you can't do this in the desingner is because the option is set to not allow operations that will cause the table to be copied.Think you can change it under tools.You can also generate the script and run that which is probably a better optionNote that if the table is large this can take a long time.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Script for adding a column to a existing table