insert @@identity using a cursor

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

Transact-SQL (2000)

insert @@identity using a cursor

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-21 : 11:39:42

I have a table looks like


M_id  servicedate c_id  d_id   
1001   03/24/04    34     56
1001   03/24/04    44     77
1001   04/30/04    56     89
1002   05/01/04    33     56
1003   05/23/04    11     12
1003   05/23/04    30     30

I would like to insert column called status ID ( @@identity) group by m_id and servicedate using a cursor.

The output should looks like:


M_id  servicedate c_id  d_id   status_id
1001   03/24/04    34     56        1
1001   03/24/04    44     77        1
1001   04/30/04    56     89        2
1002   05/01/04    33     56        3
1003   05/23/04    11     12        4
1003   05/23/04    30     30        4

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-07-21 : 13:35:26

I think that you are thinking wrongly about the @@Identity.
( an identity column cannot contain duplicates )

Anyway, I hope this might help:

set nocount on
create table #serv(M_id int,servicedate datetime,c_id int,d_id int)

insert #serv(M_id,servicedate,c_id,d_id)
select 1001,'03/24/04',34,56
union all select 1001,'03/24/04',44,77
union all select 1001,'04/30/04',56,89
union all select 1002,'05/01/04',33,56
union all select 1003,'05/23/04',11,12
union all select 1003,'05/23/04',30,30

print 'identity column is useless'
alter table #serv add status_id int identity
select * from #serv
alter table #serv drop column status_id

print 'add ordinary int column and update... '
alter table #serv add status_id int
go

-- set based update using temporary table
create table #sequence(id int identity,M_id int,servicedate datetime,primary key clustered(M_id,servicedate))
insert #sequence(M_id,servicedate)
select M_id,servicedate from #serv group by M_id,servicedate order by M_id,servicedate

update #serv set status_id = id
from #sequence where #serv.M_id = #sequence.M_id and #serv.servicedate = #sequence.servicedate

drop table #sequence

select * from #serv

drop table #serv

--/rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-07-21 : 13:37:24

Oh, Sorry about the Cursor..
It is always better to work with sets. Cursors are muuucccchhhh slower.

/rockmoose

Kristen
Test

22859 Posts

Posted - 2004-07-21 : 14:30:14

"an identity column cannot contain duplicates"

Shouldn't happen, but can happen. Best to put a unique index on it to stop it happening.

Kristen


create table MyTempTable
(
	MyID int identity(1,1),
	MyStuff varchar(10)
)

INSERT INTO MyTempTable(MyStuff)
SELECT 'FOO1' UNION ALL
SELECT 'BAR2'
GO

SELECT * FROM MyTempTable
GO

SET IDENTITY_INSERT MyTempTable ON
GO

INSERT INTO MyTempTable(MyID, MyStuff)
SELECT 4, 'FOO4' UNION ALL
SELECT 5, 'BAR5'

SET IDENTITY_INSERT MyTempTable OFF
GO

SELECT * FROM MyTempTable ORDER BY MyID
GO
-- 1,2,4,5

DBCC CHECKIDENT (MyTempTable, RESEED, 3)
GO

INSERT INTO MyTempTable(MyStuff)
SELECT 'FOO3' UNION ALL
SELECT 'BAR4'
GO

SELECT * FROM MyTempTable ORDER BY MyID
GO
-- 1,2,4,4,5,5 - Oh Dear!!

DROP TABLE MyTempTable
GO

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-07-21 : 14:44:35

Sorry my mistake, ignore erroneus info jung1975.

Gosh Kristen !, You must be doing some very evil stuff to your databases.

DBCC CHECKIDENT (MyTempTable, RESEED, 3) thats just wonderful, lets see.., used it last week I think ;-)

Happy Sqling
/rockmoose

Kristen
Test

22859 Posts

Posted - 2004-07-21 : 15:33:04

Indeeie, that WON'T happen - except in evil circumstances - but SQL can take a dive and create the same effect (well, that certainly used to be true - maybe MS have absolutely and for definitely fixed it in SQL2K SPnn!) IMO Its best to have a Unique Constraint on it and then if SQL does do a Tripple Toe Salco at least it will go Splat! rather than squirting an erroneous value into your table.

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-07-21 : 15:45:18

Thanks for the enlightnment.
Actually 100% of the times I have used an Identity in a table it has also been the PK, and 95% of the times Clustered.
I have a hard time figuring out why one would want an Identity that is not PK, (UIX).

/rockmoose

Kristen
Test

22859 Posts

Posted - 2004-07-21 : 16:28:41

That's easy - afterthought! (as in "I wish I'd used an identity ...)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources