Erzeugen Ausgabedatei

Update ParentKeys

Überprüfung

Kode zum erzeugen der Ausgabedatei

WITH
PC (ZeilenKey, ZeilenKeyAN, Parent, ParentAN,Title,FullName, Ebene) AS
(
SELECT           E.EmployeeID as ZeilenKey,
                        'abc' + Cast(E.EmployeeID AS VARCHAR(500)) as ZeilenKeyAN,
                        E.ManagerID as Parent,
                        'abc' + CAST(E.ManagerID AS VARCHAR(500)) as ParentAN, 
                        E.Title,
                        C.FirstName + ' ' + CASE WHEN C.MiddleName IS NULL THEN '' ELSE C.MiddleName + ' ' END + C.LastName AS FullName,
                        0 AS Ebene
FROM HumanResources.Employee AS E
            INNER JOIN Person.Contact AS C ON
                        E.ContactID = C.ContactID
WHERE E.ManagerID IS NULL
UNION ALL
SELECT           E.EmployeeID as ZeilenKey,
                        'abc' + Cast(E.EmployeeID AS VARCHAR(500)) as ZeilenKeyAN,
                        E.ManagerID as Parent,
                        'abc' + CAST(E.ManagerID AS VARCHAR(500)) as ParentAN, 
                        E.Title,
                        C.FirstName + ' ' + CASE WHEN C.MiddleName IS NULL THEN '' ELSE C.MiddleName + ' ' END + C.LastName AS FullName,
                        Ebene + 1
FROM HumanResources.Employee AS E
            INNER JOIN Person.Contact AS C ON
                        E.ContactID = C.ContactID
            INNER JOIN PC AS P ON
                        P.ZeilenKey = E.ManagerID
)
SELECT ZeilenKeyAN, ParentAN,Title,FullName, Ebene
FROM PC        
ORDER BY Ebene, FullName

Kode für das Update des Parentkeys

WITH CTE (LfdNr,ParentAN)
AS
(
SELECT DISTINCT A.LfdNr,B.ParentAN
FROM dbo.ParentChildDemo AS A
            INNER JOIN dbo.ParentChildDemo AS B ON
                        A.ZeilenKeyAN = B.ParentAN
)
UPDATE dbo.ParentChildDemo
SET ParentKey = CTE.LfdNr
FROM CTE
WHERE CTE.ParentAN = ParentChildDemo.ParentAN
AND LEN(dbo.ParentChildDemo.ParentAN) > 0

Kode zum Überprüfen

WITH
PC (ZeilenKey, ZeilenKeyAN, Parent, ParentAN,Title,FullName, Ebene, Pfad) AS
(
SELECT           E.EmployeeID as ZeilenKey,
                        'abc' + Cast(E.EmployeeID AS VARCHAR(500)) as ZeilenKeyAN,
                        E.ManagerID as Parent,
                        'abc' + CAST(E.ManagerID AS VARCHAR(500)) as ParentAN, 
                        E.Title,
                        C.FirstName + ' ' + CASE WHEN C.MiddleName IS NULL THEN '' ELSE C.MiddleName + ' ' END + C.LastName AS FullName,
                        0 AS Ebene,
                        CAST('abc' + CAST(E.EmployeeID AS VARCHAR(500)) AS VARCHAR(4000)) as Pfad
FROM AdventureWorks.HumanResources.Employee AS E
            INNER JOIN AdventureWorks.Person.Contact AS C ON E.ContactID = C.ContactID
WHERE E.ManagerID IS NULL
UNION ALL
SELECT           E.EmployeeID as ZeilenKey,
                        'abc' + CAST(E.EmployeeID AS VARCHAR(500)) as ZeilenKeyAN,
                        E.ManagerID as Parent,
                        'abc' + CAST(E.ManagerID AS VARCHAR(500)) as ParentAN, 
                        E.Title,
                        C.FirstName + ' ' + CASE WHEN C.MiddleName IS NULL THEN '' ELSE C.MiddleName + ' ' END + C.LastName AS FullName,
                        Ebene + 1,
                        CAST(Pfad + '|' + 'abc' + CAST(E.EmployeeID AS VARCHAR(500)) AS VARCHAR(4000))
FROM AdventureWorks.HumanResources.Employee AS E
            INNER JOIN AdventureWorks.Person.Contact AS C ON E.ContactID = C.ContactID
            INNER JOIN PC AS P ON P.ZeilenKey = E.ManagerID
),
-- Vergleichswerte nach SSIS Process
PCV (LfdNr, ZeilenKeyAN, ParentKey, ParentAN,Title,FullName, Ebene, Pfad) AS
(
SELECT           T.LfdNr,
                        T.ZeilenKeyAN,
                        T.ParentKey,
                        T.ParentAN, 
                        T.Title,
                        T.FullName,
                        0 AS Ebene,
                        CAST(T.ZeilenKeyAN AS VARCHAR(4000)) AS Pfad
FROM tempdb.dbo.ParentChildDemo AS T
WHERE T.ParentKey = 0
UNION ALL
SELECT           T.LfdNr,
                        T.ZeilenKeyAN,
                        T.ParentKey,
                        T.ParentAN, 
                        T.Title,
                        T.FullName,
                        T.Ebene + 1,
                        CAST(Pfad + '|' + T.ZeilenKeyAN AS VARCHAR(4000))
FROM tempdb.dbo.ParentChildDemo AS T
            INNER JOIN PCV AS P ON P.LfdNr = T.ParentKey
)
-- und jetzt vergleichen
SELECT PC.ZeilenKeyAN, PC.ParentAN,PC.Title,PC.FullName, PC.Ebene, PC.Pfad
                        ,PCV.LfdNr, PCV.ParentKey, PCV.ZeilenKeyAN, PCV.ParentAN,PCV.Title,PCV.FullName, PCV.Ebene, PCV.Pfad
FROM PC         INNER JOIN PCV ON PC.Pfad = PCV.Pfad
WHERE  PC.FullName = PCV.FullName
ORDER BY      PC.Ebene, PC.FullName