August 5, 2003 at 4:29 am
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.
August 5, 2003 at 4:47 am
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]
August 5, 2003 at 6:13 am
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.
August 5, 2003 at 7:04 am
Hi David,
quote:
There is nothing in SQL7 BOL either way about using a variable. Try this as an alternativecreate 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]
August 5, 2003 at 7:26 am
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.
August 5, 2003 at 7:50 am
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]
August 5, 2003 at 8:29 pm
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
August 6, 2003 at 2:08 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply