如何建立自我参照表 [英] how to build self referencing table

问题描述

在源表中，有两列，如下快照所示:

In the source table, there are two columns as following snapshot shows:

然后用于目标表，它应该是这样的:

Then for destination table, it should be something like this:

("DimLocationKey"是自动生成的代理密钥)

如何在SSIS中实现自引用效果?我尝试了以下方法，但由于查找中没有匹配项，因此无法正常工作.

How could I achieve self-referencing effect in SSIS? I tried following approach but it's not working because there would be no matches in the lookup.

推荐答案

如果该列可为空，则可以加载location_ID的唯一值，然后让辅助过程返回并注意更新现有的并可能添加新的.

If the column is nullable, then you could load the unique values for location_ID and then have a secondary process come back through and take care of updating existing and possibly adding new.

1 NULL A NULL
2 NULL B NULL 
3 NULL C NULL
4 NULL D NULL

我想如果它不能为空，那么您可以预先计算数据流中的这些ID，并为其分配当前行和父级.作为开发人员，我可能会为此而讨厌您；)

I suppose if it's not nullable, then you could precompute those ids in a data flow and assign current row and parent to themselves. As a developer, I might hate you for that though ;)

这时，一个问题就是表中应该有8行还是4行(无论源数据指示什么).对于业务用户来说，这是一个问题，可以适当地简化".我已经在等级问题中看到了两个答案-总统向谁报告?"总统在一处没有向任何人报告，这意味着费用请求已自动获得批准.另一个地方有首席执行官报告，这意味着他们的费用报告仍然必须由他们自己批准.我想这是为了确保他们具有执行问责制，因为没有什么是自动的.

At this point, it becomes a question of whether there should be 8 rows in the table or 4 (whatever your source data indicates). This becomes a question for business users, appropriately "dumbed down". I've seen both answers in my hierarchy questions - "Who does the President report to?" At one place, the President reported to no one which meant expense requests were automatically approved. A different place had the CEO report to themselves which meant their expense reports still had to be approved by themselves. I guess it was to ensure they had executive accountability as nothing was automagic.

如果答案是8行，那么您的数据流将看起来正确.如果为4，则将使用现有的数据流，但改为更新行.如果行少，几百行，则可以使用OLEDB命令并编写更新语句.只需意识到它将针对命中该组件的每一行发出一个UPDATE语句.由于效率极低，这可能会使您的处理陷入停顿.

If the answer is 8 rows, then your data flow would look about right. If it's 4, then you'd use the existing data flow but update the rows instead. If it's a small set of rows, hundreds, then you can use the OLEDB Command and write your update statement. Just realize that it will issue an UPDATE statement for every row that hits the component. That can bring your processing to a standstill as it's terribly inefficient.

更新的更有效途径是使用OLE DB目标，并且在完成数据流之后，让Execute SQL任务发出基于集合的UPDATE语句.请参阅安迪·伦纳德(Andy Leonard)的集成服务阶梯系列，以获取有关如何做这个.

The more efficient route for updates is to use the OLE DB destination and the after the Data Flow completed, have an Execute SQL task issue a set-based UPDATE statement. See Andy Leonard's Stairway to Integration Services series for a well written example of how to do this.

如果它不能为null，并且不允许引用其自身的节点，那么看来您的数据模型不能正确描述

If it's not nullable and nodes referencing themselves is not allowed, then it seems your data model does not accurately describe

这篇关于如何建立自我参照表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！