| Author |
Topic |
|
fan2005
Yak Posting Veteran
85 Posts |
 Posted - 2011-01-30 : 23:58:45
|
| hi How is it possiblt to alter a column of bigint datatype to identity(bigint,1,1)by script |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-01-31 : 01:57:31
|
| You cannot do it using script.PBUH |
 |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-31 : 02:08:14
|
| and perhaps you cannot do it via design mode to a table which already holds any data. In order to do the required changes you will need to re-design a table with same structure with the addition of Identity key. Once added turn off the Identity property so that you can insert explicit values into the identity column and move all data from the existing table into this new. and then turn on the identity property so that incremented values are inserted automatically. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-01-31 : 04:00:44
|
quote:
Originally posted by MIK_2008
and perhaps you cannot do it via design mode to a table which already holds any data....
That is not at all true.It will create a new identity value (referencing the last value present in the table) for any new record added as soon as the column identity property is changed to Yes.PBUH |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-31 : 04:05:32
|
| Well Sachin i tried it as a test for a table .. removed all existing rows (delete from tab1) and changed the Datatype from int to Bigint and with the identity=yes ... which said Changes made need to drop and re-create! Would you like to shed some light on this please? |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-01-31 : 04:25:51
|
| In SSMS Goto Tools>>Options >>Designers>>Table and Database Designers.There you will find an option "Prevent saving changes that require table re-creation".Uncheck it if it is checked and then try.PBUH |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-31 : 04:51:30
|
| yup it works, thanks for sharing. :)However just confused of what you said "You cannot do it using script." This is first time i came to know that a DDL which can be done via SSMS and not via Script. Any specific reason for this? Or any link where i can find my answer and some more info over such cases? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-01-31 : 06:30:43
|
| It can be done via script (in fact this is what SSMS always does) but SSMS is "faking" it by creating a new table with a temporary name that has the new column design, moving all the rows to the new table using SET IDENTITY_INSERT ON, drop the old table and then rename the new table to the old tables name. You could have done this by script yourself quite easily but what sachin meant was that it's not possible to change the column to an identity on the existing table.- LumbagoMy blog-> www.thefirstsql.com |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-31 : 07:23:46
|
| aah i see .. thanks for the clarification .. Actually Sachin just told me an easy way to do the task i use to do long way :Dmany thanks to both! Cheers! |
|
|
|
fan2005
Yak Posting Veteran
85 Posts |
Posted - 2011-01-31 : 14:35:38
|
| in a table with over 30milion records it fails to error when I do it manually |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-01 : 03:15:05
|
Is fan2005 and MIK_2008 the same person? Confusing...fan2005: what is the error message? And are you doing it using the ssms designer or through t-sql code in a query window? Here is the pseudo-code for the process:create table tmpMyTableName ( myID bigint identity(1, 1) primary key clustered, ...)begin transet identity_insert tmpMyTableName oninsert into tmpMyTableName (myID, ...)select (myID, ...) from MyTableName set identity_insert tmpMyTableName offexec sp_rename 'MyTableName', 'MyTableName_old'exec sp_rename 'tmpMyTableName', 'MyTableName'commitdrop table MyTableName_old - LumbagoMy blog-> www.thefirstsql.com |
|
|
|
fan2005
Yak Posting Veteran
85 Posts |
Posted - 2011-02-02 : 05:46:09
|
quote:
Originally posted by Lumbago
Is fan2005 and MIK_2008 the same person? Confusing...
no I'm just fan2005  i got timeout i thinkI'll try your code thanks |
|
|
|
|
alter column to identity