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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-10-03 : 07:53:16
|
| Jugal Shah writes " I have project to migrate data from old database to new database.ex. Create Table X(IdNo int Identity(1,1),Name Varchar(20)) Table Containing Rows 1. Jugal shah 2. Bill gates Now Migrate It Create Table New_X(IdNo int Identity(1,1),Name Varchar(20)) but I don't want to use insert_identity on I drop Identity from new_x and insert data in New_X Create Table New_X(IdNo int,Name Varchar(20)) 1. Jugal shah 2. Bill gates Now I want to add identity begin from seed 3,increment by 1 Alter table New_X Alter Column IdNo int identity(3,1) null this query not worked plz give the correct query above my mail address I am thanking you Yours faithfully Jugal shah " |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-03 : 07:56:26
|
| You need to use EnterPrise Manager to add identity property to a columnMadhivananFailing to plan is Planning to fail |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-03 : 09:42:38
|
| No you don't. Enterprise Manager basically just makes a new table when you do this. It creates a temp table, dumps the records into it. Deletes the original table, creates it with a new identity column, inserts the records.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-03 : 09:57:12
|
| So doing the same in Query is dropping that column and add new column with the same name with identity property?MadhivananFailing to plan is Planning to fail |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-03 : 12:27:45
|
Why don't you run the change in EM and watch what it does.  MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-03 : 13:01:19
|
| ... Or make the change in E.M. and use the "Save Change Script" button - and you will have a script to make the change.You can change the "Next Number" for IDENTITY using DBCC(IIRC)Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-03 : 22:25:39
|
lose the null, you can't have identity and null at the same time  quote:
Originally posted by AskSQLTeam
Alter table New_X Alter Column IdNo int identity(3,1) null
--------------------keeping it simple... |
|
|
|
|
|
|
|
|
Alter Identity Column