October 16, 2018 at 12:05 pm
Hi All,
This is a continuation of another thread. I am attempting to use the Merge statement to perform these activity.
I am just doing an insert for new columns and updating existing records.
The update I want keep track of slowly changing dimensions.
This is my logic:
for an insert : the inserted table is not null and the deleted table is null
for an update : both the inserted and deleted table are not null
I believe the problem is in the where clausedeletedEmployID != [inserted employID]
This code works on the first attempt but it continues to add the same record over and over againdeletedEmployID != [inserted employID] and --Check change in EmploeeID
( deletedEmployID is not null and [inserted employID] is not null)
The same with this code
Here is a the complete code:--Create Table Variable to store data for output insert
declare @tmpEmployee table(
[Act] nvarchar(10) not null,
[deletedEmployID] nvarchar(50) null,
[inserted employID] nvarchar(50) null,
[deleted IMSuserID] bigint null,
[inserted IMSuserID] bigint null,
deletedDisplayName nvarchar(max) null,
insertedDisplayName nvarchar(max) null,
deletedFirstName nvarchar(50) null,
insertedFirstName nvarchar(50) null,
deletedInitials nvarchar(50) null,
insertedInitials nvarchar(50) null,
deletedLastName nvarchar(50) null,
insertedLastName nvarchar(50) null ,
deletedEmail nvarchar(100) null,
insertedEmail nvarchar(100) null,
deletedSamAccountName nvarchar(50) null ,
insertedSamAccountName nvarchar(50) null ,
deletedDistinguishedName nvarchar(max) null,
insertedDistinguishedName nvarchar(max) null,
deletedObjectGUID nvarchar(100) null,
insertedObjectGUID nvarchar(100) null ,
deletedObjectSid nvarchar(100) null,
insertedObjectSid nvarchar(100) null,
deletedphysicalDeliveryOfficeName nvarchar(50) null,
insertedphysicalDeliveryOfficeName nvarchar(50) null,
deletedCreateDate datetime null,
insertedCreateDate datetime null,
deletedModifiedBy nvarchar(50) null,
insertedModifiedBy nvarchar(50) null,
deletedUpdateDate datetime null,
insertedUpdateDate datetime null,
deletedIsDisabled bit null,
insertedIsDisabled bit null,
deletedDisabledDate datetime null,
insertedDisabledDate datetime null,
deletedAccountClosedDate datetime null,
insertedAccountClosedDate datetime null ,
deletedIMS_USER_ID_FK bigint null,
insertedIMS_USER_ID_FK bigint null
) --End Table Variable
--Begin Workload
MERGE TargetAD as T --Target Table to Load
USING SourceAD as S -- Source Table of Original data
--The only unique column is the SamAccountName
on T.SamAccountName = S.SamAccountName
--when matched THEN
--update set
-- T.[EmployID] = S.[EmployID],
-- T.[DisplayName] = S.[DisplayName],
-- T.[FirstName] = S.[FirstName],
-- T.[Initials] = S.[Initials],
-- T.[LastName] = S.[LastName],
-- T.[Email] = S.[Email],
-- --T.[SamAccountName] = S.[SamAccountName],
-- T.[DistinguishedName] = S.[DistinguishedName],
-- T.[ObjectGUID] = S.[ObjectGUID],
-- T.[ObjectSid] = S.[ObjectSid],
-- T.[physicalDeliveryOfficeName] = S.[physicalDeliveryOfficeName],
-- T.[ModifiedBy] = System_user,
-- T.[UpdateDate] = Getdate()
-- --T.[IsDisabled] = 1,
-- --T.[DisabledDate] = getdate(),
-- --T.[AccountClosedDate] = getdate(),
-- --T.[IMS_USER_ID_FK] = T.[IMSuserID]
when not matched by target then
--This Inserts NEW Record from Source into Target Table
INSERT (
[IMSuserID]
,[EmployID]
,[DisplayName]
,[FirstName]
,[Initials]
,[LastName]
,[Email]
,[SamAccountName]
,[DistinguishedName]
,[ObjectGUID]
,[ObjectSid]
,[physicalDeliveryOfficeName]
,[CreateDate]
,[ModifiedBy]
,[UpdateDate]
,[IsDisabled]
,[DisabledDate]
,[IMS_USER_ID_FK]
,[AccountClosedDate]
)
VALUES (
S.[IMSuserID],
S.[EmployID],
S.[DisplayName],
S.[FirstName],
S.[Initials],
S.[LastName],
S.[Email],
S.[SamAccountName],
S.[DistinguishedName],
S.[ObjectGUID],
S.[ObjectSid],
S.[PhysicalDeliveryOfficeName],
S.[CreateDate],
S.[ModifiedBy],
S.[UpdateDate],
S.[IsDisabled],
S.[DisabledDate],
S.[IMS_USER_ID_FK],
S.[AccountClosedDate]
)
--End Inserts NEW Record from Source into Target
--Delete Values Mark IsDiabled field to true
--WHEN NOT MATCHED BY SOURCE and T.[IsDisabled] != 1 and T.AccountCloseDDate is not null THEN
--UPDATE SET
-- T.[ModifiedBy] = System_user,
-- T.[UpdateDate] = Getdate(),
-- T.[IsDisabled] = 1,
-- T.[DisabledDate] = getdate(),
-- T.[AccountClosedDate] = getdate()
--End delete
output $action as Act,
--Capture All changes to target Table
deleted.EmployID as [deleted EmployID],
inserted.EmployID as insertedemployID,
deleted.IMSuserID as [deleted IMSuserID],
inserted.IMSuserID as [inserted IMSuserID],
deleted.DisplayName as deletedDisplayName,
inserted.DisplayName as insertedDisplayName,
deleted.[FirstName] as deletedFirstName,
inserted.[FirstName] as insertedFirstName,
deleted.[Initials] as deletedInitials,
inserted.[Initials] as insertedInitials,
deleted.[LastName] as deletedLastName,
inserted.[LastName] as insertedLastName ,
deleted.[Email] as deletedEmail,
inserted.[Email] as insertedEmail ,
deleted.[SamAccountName] as deletedSamAccountName ,
inserted.[SamAccountName] as insertedSamAccountName ,
deleted.[DistinguishedName] as deletedDistinguishedName,
inserted.[DistinguishedName] as insertedDistinguishedName,
deleted.[ObjectGUID] as deletedObjectGUID,
inserted.[ObjectGUID] as insertedObjectGUID ,
deleted.[ObjectSid] as deletedObjectSid ,
inserted.[ObjectSid] as insertedObjectSid,
deleted.[physicalDeliveryOfficeName] as deletedphysicalDeliveryOfficeName ,
inserted.[physicalDeliveryOfficeName] as insertedphysicalDeliveryOfficeName ,
deleted.[CreateDate] as deletedCreateDate,
inserted.[CreateDate] as insertedCreateDate,
deleted.[ModifiedBy] as deletedModifiedBy,
inserted.[ModifiedBy] as insertedModifiedBy,
deleted.[UpdateDate] as deletedUpdateDate,
inserted.[UpdateDate] as insertedUpdateDate,
deleted.[IsDisabled] as deletedIsDisabled,
inserted.[IsDisabled] as insertedIsDisabled,
deleted.[DisabledDate] as deletedDisabledDate,
inserted.[DisabledDate] as insertedDisabledDate,
deleted.[AccountClosedDate] as deletedAccountClosedDate,
inserted.[AccountClosedDate] as insertedAccountClosedDate ,
deleted.[IMS_USER_ID_FK] as deletedIMS_USER_ID_FK,
inserted.[IMS_USER_ID_FK] as insertedIMS_USER_ID_FK
into @tmpEmployee ;
insert into TargetAD (
[IMSuserID]
,[EmployID]
,[DisplayName]
,[FirstName]
,[Initials]
,[LastName]
,[Email]
,[SamAccountName]
,[DistinguishedName]
,[ObjectGUID]
,[ObjectSid]
,[physicalDeliveryOfficeName]
,[CreateDate]
,[ModifiedBy]
,[UpdateDate]
,[IsDisabled]
,[DisabledDate]
,[IMS_USER_ID_FK]
,[AccountClosedDate]
)
select
NEXT VALUE FOR [dbo].[sequence_test],
deletedEmployID,
deletedDisplayName,
deletedFirstName,
deletedInitials,
deletedLastName,
deletedEmail,
deletedSamAccountName,
deletedDistinguishedName,
deletedObjectGUID,
deletedObjectSid,
deletedphysicalDeliveryOfficeName,
GETDATE(), -- Record Creation Date
SYSTEM_USER, --Modified By
getdate(), --Update Date
1, --True
GETDATE(), -- Disable Date
[deleted IMSuserID], --FK Self Join reference new Primary key
GETDATE() --Close Account Date
from
@tmpEmployee
where --Check Change in values
deletedEmployID != [inserted employID]
This seems like it should be a straight forward thing. I would appreciate any help!
Thank You
October 17, 2018 at 8:10 am
Quick thought, can you post the DDL for the table and some sample data as an insert statement please, will make the effort of answering the (trivial) question much easier.
😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply