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.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy