Merge Statement and ID Insertion
I am using the Merge statement to enter / update data in my stored procedure.
MERGE [dbo].[tReserveData_4541] AS Target
USING (SELECT * FROM [dbo].[tblcangrowhitesh]) AS Source
ON ( [Source].[PK_ID] = [Target].[PK_ID])
WHEN MATCHED THEN
UPDATE SET [Target].[mgender] = Source.[mgender]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Granularity], [PK_ID], [ROWID],
[mgender], [mma1], [mma2], [mma3],
[mmadmincost], [mmcumulativevolume],
[mmcurrency], [mmdate],
[mmfileimporteddate], [mmfilename])
VALUES ([Source].[Granularity], [Source].[PK_ID], [Source].[ROWID],
[Source].[mgender], [Source].[mma1], [Source].[mma2], [Source].[mma3],
[Source].[mmadmincost], [Source].[mmcumulativevolume],
[Source].[mmcurrency], [Source].[mmdate],
[Source].[mmfileimporteddate], [Source].[mmfilename])
As you can see, I am going to insert the ie id column [PK_ID]
into the statement MERGE
. But I cannot do this.
I'm not familiar with the command MERGE
, but since you are inserting directly into the ID column, you won't need to use the IDENTITY_INSERT command? eg.
SET IDENTITY_INSERT [dbo].[tReserveData_4541] on
MERGE...
SET IDENTITY_INSERT [dbo].[tReserveData_4541] off
Remove the IDENTITY property on the PK_ID if you don't need to create values for the target.
You can add PK_ID_temp column for this UPDATE [dbo].[tReserveData_4541] SET PK_ID_temp = PK_ID
. Drop the PK_ID column and then rename PK_ID_temp back to PK_ID.
It may also require resetting the Primary Key, resetting any linked indexes, and rebuilding all nonclustered indexes if it is a clustered index.