Identity Function

Question

Identity Function

Mike2265

Right there with Babe

Points: 753
More actions
August 5, 2003 at 4:29 am

#358098

Hey,
I'm using the identity function to insert an identity field (as it happens) into a temporary table. I'm seeding the value based on the max from another table.
IDENTITY(int,@Seed1, 1) AS QualitySpecificationID,
Now although this essentially works in that it provides a unique value, the starting value (7990448) bears no relationship to @Seed1 (1). If I remove the parameter @Seed1 and enter a random starting integer then it works fine.
Is there a bug here or am I not using this right?
Cheers,
Mike.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Mike,

quote:
I'm using the identity function to insert an identity field (as it happens) into a temporary table. I'm seeding the value based on the max from another table.
...
Now although this essentially works in that it provides a unique value, the starting value (7990448) bears no relationship to @Seed1 (1). If I remove the parameter @Seed1 and enter a random starting integer then it works fine.

can you post the whole statement?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65143 More actions · Answer 2

There is nothing in SQL7 BOL either way about using a variable. Try this as an alternative

create table #temptable (rowID int IDENTITY(1,1),...)

DBCC CHECKIDENT (#temptable, RESEED, @Seed1)

insert into #temptable ...

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi David,

quote:
There is nothing in SQL7 BOL either way about using a variable. Try this as an alternative
create table #temptable (rowID int IDENTITY(1,1),...)
DBCC CHECKIDENT (#temptable, RESEED, @Seed1)
insert into #temptable ...

??? äh, a little bit confused, due to the unnormal hot weather, I think

You're speaking of IDENTITY property, right?

When reading the original post



IDENTITY(int,@Seed1, 1) AS QualitySpecificationID,

I thought the IDENTITY function was meant ?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65143 More actions · Answer 4

No, I was referring to the function. According to my BOL (SQL7) you can supply a seed for the IDENTITY function but it does not imply that you can use a variable and this seems to be true as in the original post.

The alternative I gave uses the IDENTITY property by creating the temp table first (good practice anyway).

p.s. Yes rather hot indeed. Thank heavens for air con

Edited by - davidburrows on 08/05/2003 07:26:31 AM

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
No, I was referring to the function. According to my BOL (SQL7) you can supply a seed for the IDENTITY function but it does not imply that you can use a variable and this seems to be true as in the original post.

yes, I forgot this. My BOL states that the IDENTITY function is only used with SELECT INTO statements like this



SELECT IDENTITY(int, 1,1) AS ID_Num

INTO NewTable

FROM OldTable



--(2)

SELECT ID_Num = IDENTITY(int, 1, 1)

INTO NewTable

FROM OldTable

while the IDENTITY property



USE pubs

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

      WHERE TABLE_NAME = 'new_employees')

   DROP TABLE new_employees

GO

CREATE TABLE new_employees

(

 id_num int IDENTITY(1,1),

 fname varchar (20),

 minit char(1),

 lname varchar(30)

)

anyway I think doing this dynamically should work. Something like



DECLARE @stmt varchar(400)

DECLARE @max varchar(10)



SET @max = (SELECT MAX(id) FROM mails_header)



SET @stmt = 'CREATE TABLE FRANK ([id] [int] IDENTITY(' +@max+ ', 1) NOT NULL, 

 [image] NULL ,

[filename] [varchar] (50) NULL ,

[description] [varchar] (100) NULL ,

[sender] [varchar] (50) NULL) 

ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'

--EXEC sp_executeSQL @stmt

PRINT @stmt

quote:
p.s. Yes rather hot indeed. Thank heavens for air con

Strange company you work with.

No money for SQL 2k, but air condition

BTW. we don't have such a thing, but SQL 2K. Not really sure, which is better

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Mike2265 Right there with Babe Points: 753 More actions · Answer 6

Thanks David - That works.

It's a bit messy as there's twice as much code but will do. Below is what I'm doing. I find it strange that the identity function can't accept variables, particularly when it doesn't error but just seeds with a ridiculous value. Incidently - we use SQL7 and 2000 as that's what our clients have. And what's BOL?

cheers,

Mike

declare @Seed int

set @Seed = 1 + (Select Coalesce(max(QualitySpecificationID),0)

From tblGeologicalYield)

Select

*,

IDENTITY(int,1, 1) AS QualitySpecificationID,

0 as GeologicalTargetID

Into #tblGeologicalImport

From tblGeologicalImport

Where Period = -123456789

DBCC CHECKIDENT (#tblGeologicalImport, RESEED, @Seed)

insert into #tblGeologicalImport

Select

*,

0 as GeologicalTargetID

From tblGeologicalImport

David Burrows SSC Guru Points: 65143 More actions · Answer 7

BOL = Books OnLine. It's SQL Server help manual if you like. It is installed when you install SQL Server or the client tools. It is the first place I look for answers.

Far away is close at hand in the images of elsewhere.
Anon.