December 6, 2014 at 9:58 am
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
December 6, 2014 at 11:09 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply