Identity Function

  • 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.

  • 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]

  • 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.

  • 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]

  • 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.

  • 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]

  • 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

  • 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