identity

• Bruna

  SSC Journeyman

  Points: 87

  More actions

  November 25, 2005 at 11:36 am

  #90929

  Hi guys,

  How can I change the status of the column(identity) that to on or off ?

  Not using E.M, using T-SQL

  thanks

  Bruna

   

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  November 25, 2005 at 12:45 pm

  #605904

  CREATE TABLE #Test( PrimaryKey integer IDENTITY,

                                     CharacterField varchar(10))

  INSERT INTO #Test

  SELECT 'First' UNION ALL

  SELECT 'Second' UNION ALL

  SELECT 'Third'

  SELECT * FROM #Test

  DROP TABLE #Test

  CREATE TABLE #Test2( PrimaryKey integer IDENTITY,

                                     CharacterField varchar(10))

  SET IDENTITY_INSERT #Test2 ON

  INSERT INTO #Test2( PrimaryKey, CharacterField)

  SELECT 4, 'First'

  INSERT INTO #Test2( PrimaryKey, CharacterField)

  SELECT 8, 'Second'

  INSERT INTO #Test2( PrimaryKey, CharacterField)

  SELECT 2, 'Third'

  SET IDENTITY_INSERT #Test2 OFF

  SELECT * FROM #Test2

  DROP TABLE #Test2

  I wasn't born stupid - I had to study.

• peterhe

  SSChampion

  Points: 11469

  More actions

  November 25, 2005 at 12:45 pm

  #605903

  To explicitly assign a value for an identity column, run:

  SET IDENTITY_INSERT YourTable ON

  To turn it to automatically asign value, run:

  SET IDENTITY_INSERT YourTable OFF