setting identity property to field with records

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

setting identity property to field with records

Author

Topic

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-03-02 : 23:58:22

Hi all,

I have a table with records and the have set the Primary key to a field and all the record id are unique.

But for an insert i need to find out the max of the existing id and then add 1 to that and then insert my record.

when i have to insert into the table from the result of a select statement, then i am facing the heat.

ie : insert into tbdet(id,name)
select * from #tempdet

when there are bulk records in the #tempdet then this creates problem.

So ithought of setting the Identity property to the tbdet.

I don wanna delete the records but still create the identity. If possible where should it start its initial value????

If No possibility, then how to get the highest id and insert the bulk records???

Thanks in advance.

Regards,
satish.r

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-03-03 : 00:25:30

Hi,
What you can do is create a table with identity column name .
Set
set Identity_insert <newtablename > on
and insert the old table values to the new table..
after inserting values
set Identity_insert <newtablename > off

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-03-03 : 00:56:33

Can i set the identity property when there are records??
If so, then my problem will be solved

Regards,
satish.r

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-03-03 : 00:58:38

My Records are already unique. i don wanna create another table.
I would like to change the existing one itself

Regards,
satish.r

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-03-03 : 01:11:14

I dont see any problem in this if records are unique.
Go to Enterprise Manager table design view and set the column as identity.

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-03-03 : 01:38:26

Any other option other than going for enterprise manager.
(I don have Enterprise Manager)

Regards,
satish.r

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-03-03 : 01:54:36

Suppose i am creating a temp table and moving records from the live table to temp table and then dropping the live table and adding the constraints to the live table and moving the records back from the temp table then, my doubt is,

Where should the Identity value start???

For Ex: My live db has 10 records with id from 91 - 100
then after dropping the table when adding the identity what shall be the seed???? 91 or 101

Regards,
satish.r

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-03-03 : 01:56:02

Hi,
I dont think you can do it through Transact-SQL. If anybody knows about it . please do let me know. The only way as far as i know is through Enterprise Manager --> Design View.
But how come you dont have Enterprise Manager ??

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-03-03 : 02:12:48

Not installed in my system. Some security Problem.

Ya sure i would let you know..

Regards,
satish.r

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-03-03 : 02:22:44

quote:
Originally posted by a_r_satish

Suppose i am creating a temp table and moving records from the live table to temp table and then dropping the live table and adding the constraints to the live table and moving the records back from the temp table then, my doubt is,

Where should the Identity value start???

For Ex: My live db has 10 records with id from 91 - 100
then after dropping the table when adding the identity what shall be the seed???? 91 or 101

Regards,
satish.r

To answer this identity will start from 101

follow these steps
Create temp table with Identity property set. and before inserting records to temp table
Set Identity_Insert temptable on
insert records
Set Identity_Insert temptable off

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-03-03 : 05:41:42

Thanks Yaar,
I did the same way u suggested. To wind up ::

1. Created a table with same structure and set the identity constraint
2.Set Identity_Insert newtbtrntabledet1 on
3. moved records from the live db to temp table
4. Set Identity_Insert newtbtrntabledet1 off
5. Renamed table using sp_rename

Regards,
satish.r

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources