Post reply

Copying the id # to another field in your table

  • June 26, 2015 at 9:06 pm

    #398658

    I need to have a table that has a primary key

    CREATE TABLE [dbo].[testing](

    [tid] [int] IDENTITY(1,1) NOT NULL,

    [sometext] [varchar](150) NOT NULL,

    [idcopied] [varchar](50) NULL,

    CONSTRAINT [testing_PrimaryKey] PRIMARY KEY CLUSTERED

    ..and eveytime I add 'sometext' as another row, the tid # needs to be duplicated to idcopied field

    insert into [testing] (sometext,idcopied) values ('some junk',@@identity)

    ???

  • June 27, 2015 at 1:58 am

    #1808585

    Not following.

    If I insert 'This is a string' into that table, where does the ID come from for the IDCopied field?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 27, 2015 at 11:16 am

    #1808608

    That's the mystery right now. Using Scope_identity has its merits, but i believe i need to do something to grab the last entered key id (tid) then increment it +1 when i do these inserts. But of course, what about the very first record entry?

    the column idcopied can be an INT datatype

    HTH

  • June 27, 2015 at 11:46 am

    #1808609

    Huh?

    If you don't know what the value should be, how can anyone else?

    Why the last entered ID value? What's the purpose? What are you trying to do here?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 27, 2015 at 12:53 pm

    #1808610

    Try this -

    INSERT INTO [testing] (sometext) VALUES ('some junk')

    UPDATE testing SET idcopied = SCOPE_IDENTITY()

    WHERE tid = SCOPE_IDENTITY()

    And yes GilaMonster has a valid question - Why you need to do this? If you explain in detail then it may have different and more convenient way to do.

Viewing 5 posts - 1 through 4 (of 4 total)

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