Insert records into a table with an identity value

• Randy Powell-473969

  Old Hand

  Points: 343

  More actions

  December 6, 2014 at 9:58 am

  #397743

  This should be pretty straight forward. But I am having a bit of a problem.

  I would like to INSERT records into the hrToeSignature table, using the results set from the query of the HRTermsOfEmployment table.

  The hrToeSignature table has an IDENTITY field that I would like to auto-increment as I INSERT records from the query.

  I know the syntax below will not work. Because my INSERT has more values then my SELECT. What do I need to add to the SELECT in order to auto-generate the new ID for the existing table.

  SET IDENTITY_INSERT hrToeSignature ON

  INSERT INTO [dbo].[hrToeSignature]

  (

  Id

  ,[ContractYear]

  ,[EmployeeID]

  ,[Email]

  ,[Position]

  ,[Guid]

  ,[Confirmed]

  ,[ConfirmedOn]

  ,[Reason]

  ,[OnBeHalfOf]

  ,[OnBeHalfOfEmployeeID]

  ,[OnBeHalfOfModifiedDate]

  ,[AssignedTo]

  ,[Status]

  ,[Issue]

  )

  --// select data from this table to use in the INSERT above

  SELECT DISTINCT

  [CurrentContractYear]

  ,[Employee]

  ,[Email]

  ,[Position]

  ,NEWID() AS [GUID]

  ,NULL AS [Confirmed]

  ,NULL AS[ConfirmedOn]

  ,NULL AS[Reason]

  ,NULL AS[OnBeHalfOf]

  ,NULL AS[OnBeHalfOfEmployeeID]

  ,NULL AS[OnBeHalfOfModifiedDate]

  ,NULL AS[AssignedTo]

  ,NULL AS[Status]

  ,NULL AS[Issue]

  FROM [dbo].[HRTermsOfEmployment]

  WHERE [CurrentContractYear] = 2015 AND Employee = 9999999

  ORDER BY Employee DESC

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  December 6, 2014 at 11:09 am

  #1763126

  Do not use SET IDENTITY INSERT ON – that is telling SQL Server that you wish to override the auto-increment for some reason. So given a table X

  Id int identity (1,1,) primary key,

  Desc varchar(100)

  insert X (Desc)

  select Desc from table

  You will see that the Identity column is not mentioned in the above – it takes care of itself.

  ---