sql - 如何通过生成 id SSIS 将表拆分为 Master/Detail？

Question

我将一个平面文件存储到一个表中。

标签P是给master的标记 I 用于详细信息

总是在 P 行之前有 (n) 行 I 行。

问题是，I行没有加入P行的ID。

需要生成一个 ID 来连接 P 行和 I 行。

P 行有两个有用的字段。“SequenceNumber”字段存储顺序 ID。“NumberOfItems”字段存储有多少 I 行属于 P 行。

我需要使用标识 ID 将数据拆分到两个表的主/详细信息中。我附上一张带有数据的图片

非常感谢

Original Table
RecordType SequenceNumber NumberOfItems TicketHeaderKey UnitID
P          1              3             ;               1
I          19900          0             FA              19900
I          3000           0             BK              3000
I          0              0             BK              0
P          2              1             ;               1
I          19900          0             FA              19900
P          3              2             ;               1
I          19900          0             FA              19900
I          3000           0             BK              3000

Need Split into two tables some like this

Master Table
RecordType SequenceNumber NumberOfItems TicketHeaderKey UnitID
P          1              3             ;               1
P          2              1             ;               1
P          3              2             ;               1

Detail Table
RecordType SequenceNumber idMasterTable TicketHeaderKey UnitID
I          19900          1             FA              19900
I          3000           1             BK              3000
I          0              1             BK              0
I          19900          2             FA              19900
I          19900          3             FA              19900
I          3000           3             BK              3000

SQL TABLE TO SPLIT

Answer 1

我不确定您要插入每个主表和明细表的数据，但这是一种方法。

对于 MasterTable:

INSERT INTO MasterTable
SELECT 
    SequenceNumber AS id, 
    UnitId, 
    NumberOfItems, 
    TicketHeaderKey, 
    PrintType 
FROM tabletosplit 
WHERE RecordType = 'P' 
ORDER BY SequenceNumber;

对于明细表:

WITH auxtable(id, RecordType, UnitId, SequenceNumber, NumberOfItems, TicketHeaderKey, PrintType, auxnumber, parentNumberOfItems, parentid) 
AS (
SELECT 1, RecordType, UnitId, SequenceNumber, NumberOfItems, TicketHeaderKey, PrintType, 1, NumberOfItems, SequenceNumber 
FROM tabletosplit WHERE SequenceNumber = 1
UNION ALL
SELECT
    auxtable.id + 1,
    T.RecordType, T.UnitId, T.SequenceNumber, T.NumberOfItems, T.TicketHeaderKey, T.PrintType, 
    CASE WHEN T.RecordType = 'P' THEN 1 else auxtable.auxnumber + 1 END, 
    CASE WHEN T.RecordType = 'P' THEN T.NumberOfItems ELSE auxtable.parentNumberOfItems END, 
    CASE WHEN T.RecordType = 'P' THEN T.SequenceNumber ELSE auxtable.parentid END 
    FROM auxtable 
    INNER JOIN (Select ROW_NUMBER() OVER(ORDER BY (SELECT NULL))  AS id, RecordType, UnitId, SequenceNumber, NumberOfItems, TicketHeaderKey, PrintType
                FROM tabletosplit 
                WHERE SequenceNumber <> 1) AS T
    ON auxtable.id = T.id 
)
INSERT INTO DetailTable
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS id, auxtable.UnitId , auxtable.TicketHeaderKey, auxtable.PrintType, auxtable.Parentid, auxtable.auxnumber -1 as DetailIndex
FROM auxtable
WHERE auxtable.RecordType = 'I';

结果:

工作样本 here希望对你有帮助。