October 11, 2018 at 5:53 pm
Hi All,
There has to be a way to use the Merge Command.
Use: When it finds a matching record checks a column in record that has changed
Example when matched and T.EmployID <> S.EmployID THEN
Employee ID has changed
Insert a new record into table
Then update old record to point. Its FK to the new record
Something like:MERGE TargetAD as T
USING SourceAD as S
on T.IMSuserID = S.IMSuserID
when matched and T.EmployID <> S.EmployID 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] -- this is Primary Key
-- and then insert new record
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]
)
;
Thank You, I appreciate your help!
October 12, 2018 at 9:21 am
Use the OUTPUT clause of the MERGE to insert the new record.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 15, 2018 at 4:58 pm
Hi Allen,
Thank You for the reply. I used your reply as a research topic. I found more detailed answers on other sites as well as SSC. Here is my solution.
I first had to create a temp table or table variable. I also included code that worked but happens to be commented out.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
)
MERGE TargetAD as T
USING SourceAD as S
on T.IMSuserID = S.IMSuserID
--Re-Enable disabled Accounts
--when matched and T.IsDisabled = 1 THEN
--update set
-- T.[ModifiedBy] = System_user,
-- T.[UpdateDate] = Getdate(),
-- T.[IsDisabled] = 0,
-- T.[AccountClosedDate] = null
----End Disable Accounts
--when matched and T.EmployID <> S.EmployID 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 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
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
--Delete Values Mark IsDiabled field to true
WHEN NOT MATCHED BY SOURCE and T.[IsDisabled] != 1 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,
deleted.EmployID as [deleted EmployID],
inserted.EmployID as [inserted employID],
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(),
SYSTEM_USER,
getdate(),
1,
GETDATE(),
[deleted IMSuserID],
GETDATE()
from
@tmpEmployee
where deletedEmployID != [inserted employID]
--select * from @tmpEmployee
October 16, 2018 at 11:22 am
Hi All,
My
Where Clause does not seem to work correctly.
It works on first use but it keeps adding an duplicate entry not susre why.
I am going to start another thread to determine why
Thank You
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply