declare @dt datetime
select @dt = getdate()-4
--select @dt
IF OBJECT_ID('tempdb..#audit') IS NOT NULL DROP TABLE #audit;
IF OBJECT_ID('tempdb..#attribute') IS NOT NULL DROP TABLE #attribute;
IF OBJECT_ID('tempdb..#value') IS NOT NULL DROP TABLE #value;
CREATE TABLE #audit(
[id] [bigint] IDENTITY(1,1) NOT NULL,
objectid uniqueidentifier,
objectTypeCode int,
attributemask nvarchar(max),
changedata nvarchar(max),
operation int,
changedOn datetime,
changedBy nvarchar(200)
)
CREATE TABLE #attribute(
[id] [bigint] IDENTITY(1,1) NOT NULL,
objectid uniqueidentifier,
objectTypeCode int,
atribute int,
operation int,
changedOn datetime,
changedBy nvarchar(200)
)
CREATE TABLE #value(
[id] [bigint] IDENTITY(1,1) NOT NULL,
objectid uniqueidentifier,
objectTypeCode int,
value nvarchar(max)
)
INSERT INTO #audit(objectid,attributemask,changedata, objectTypeCode, operation, changedOn, changedBy)
select
objectid ,substring(attributemask,2,len(attributemask)-2) AttributeMask,ChangeData, objectTypeCode, Operation, CreatedOn, UserIdName
from Audit a
WHERE
a.operation in (1,2)
AND a.Action in (1,2)
AND a.ObjectTypeCode = 2
and a.CreatedOn >= @dt
--and a.useridname = 'sa_CRM_Delta, sa_CRM_Delta'
AND a.ChangeData!=''
declare @rowNum bigint
DECLARE @objectid uniqueidentifier
declare @attributemask nvarchar(max)
declare @changedata nvarchar(max)
DECLARE @auxAtrib xml
DECLARE @auxVals xml
declare @objectTypeCode int
declare @operation int
declare @changedOn datetime
declare @changedBy nvarchar(200)
declare @splitAttrib char(1)
set @splitAttrib = ','
declare @splitVals char(1)
set @splitVals = '~'
DECLARE audit_cursor CURSOR FOR
SELECT id,objectid,attributemask,changedata, objectTypeCode,operation, changedOn, changedBy
FROM #audit
open audit_cursor
FETCH NEXT FROM audit_cursor
INTO @rowNum,@objectid,@attributemask,@changedata, @objectTypeCode,@operation,@changedOn,@changedBy
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @auxAtrib = CONVERT(xml,' <root> <s>' + REPLACE(@attributemask,@splitAttrib,'</s> <s>') + '</s> </root> ')
SELECT @auxVals = CONVERT(xml,' <root> <s>' + REPLACE( replace(@changedata,'&','&') ,@splitVals,'</s> <s>') + '</s> </root> ')
INSERT INTO #attribute(objectid, objectTypeCode , atribute,operation,changedOn,changedBy)
SELECT @objectid id, @objectTypeCode, atrib = T.c.value('.','varchar(20)') , @operation, @changedOn, @changedBy
FROM @auxAtrib.nodes('/root/s') T(c)
INSERT INTO #value(objectid, objectTypeCode, value)
SELECT @objectid id, @objectTypeCode, val = T.c.value('.','varchar(20)')
FROM @auxVals.nodes('/root/s') T(c)
FETCH NEXT FROM audit_cursor
INTO @rowNum,@objectid,@attributemask,@changedata, @objectTypeCode,@operation,@changedOn,@changedBy
END
CLOSE audit_cursor;
DEALLOCATE audit_cursor;
--truncate table StagingDB.dbo.Audit
--INSERT INTO StagingDB.dbo.Audit
SELECT
--v.objectid
--,v.value
--,a.atribute
CASE a.operation
WHEN 1 then 'Create'
WHEN 2 then 'Update'
END operation
,a.changedOn
,cast(a.changedOn as date) changedOn_date
,a.changedBy
,at.Name
,c.hb_ConsumerId
,c.Address1_Country
FROM #value v
INNER JOIN #attribute a
ON v.id = a.id
INNER JOIN Row2015_MSCRM.dbo.Contact c
ON v.objectid = c.ContactId and c.statecode=0
INNER JOIN (
SELECT DISTINCT ar.name, ar.ColumnNumber
FROM MetadataSchema.Attribute ar INNER JOIN
MetadataSchema.Entity en ON ar.EntityId = en.EntityId
WHERE en.ObjectTypeCode=2 ) at
ON a.atribute = at.ColumnNumber
IF OBJECT_ID('tempdb..#audit') IS NOT NULL DROP TABLE #audit;
IF OBJECT_ID('tempdb..#attribute') IS NOT NULL DROP TABLE #attribute;
IF OBJECT_ID('tempdb..#value') IS NOT NULL DROP TABLE #value;
