Using the @@IDENTITY in UPDATE Statement 1

[skispray]

I have an Identity (AutoNumber) column in SQL that I need to use in an UPDATE statement, but I can't figure out how to use it combined with other text. This has been simplified.

Table1(ID, Col1)

Code:
INSERT INTO Table1 (Col1) Values ('temp'); UPDATE Table1 Set Col1='Display.aspx?ID=@@IDENTITY'

This works fine if I just Set Col1=@@IDENTITY. If the Identity is 5 then I'd like for Col1 to show Display.aspx?ID=5. Anyone know how to do this?

 

[swampBoogie]

INSERT INTO Table1 (Col1) Values ('temp'); UPDATE Table1 Set Col1='Display.aspx?ID=' + cast(@@IDENTITY as varchar)

 

[RickMurph]

I am not sure If I fully understand your problem, but here are a couple things you can do:

1) Check to see what the current identity field is
select Ident_current ('Table1')
or
DBCC CHECKIDENT('Table1')

2)
SET IDENTITY_INSERT [dbo].[Table1] ON
go
Insert Into [dbo].[Table1] (ID, Col1)
values (1,1)
go
Set Identity_insert [dbo].[Table1] Off

3) You can reseed the Identity to a different value as follows:
DBCC CHECKIDENT(Table1, RESEED,2) Note, 2 is the number you want the identity to be set to.

Hope this helps.
Rick