我目前正在探索一些奇怪的做事方式,并且想知道是否有人对以下方法有任何经验。
背景
我有一些 NF3 表和 NF1 源数据。这是其中的一部分:
SourceData : (ProjectID, Manager, City, Status, Cost)
Destination Data:
Projects (ProjectID, ManagerID, CityID, StatusID, Cost)
Managers (ManagerID, ManagerName, [...])
Cities (CityID, CityName, [...])
Status (StatusID, StatusName, [...])
Projects_View (ProjectID, Manager, City, Status, Cost)
期望的结果
对于源数据中的给定行,我想将其插入到项目中,同时根据需要插入到 FK 表 Managers、Cities 和 Status 中。我使用的方法是首先合并到所有 FK 表中,然后在满足 FK 约束后插入到项目中。
在这种方法中我不喜欢的部分是加入 varchar 列来获取 ID - 特别是因为我刚刚在事务的早期合并了这些 ID。例如:
INSERT INTO Projects
sd.ProjectID
, m.ManagerID
, c.CityID
, s.StatusID
, sd.Cost
FROM SourceData AS sd
LEFT JOIN Managers AS m ON sd.ManagerName = m.ManagerName
LEFT JOIN Cities AS c ON sd.CityName = c.CityName
LEFT JOIN Status AS s ON sd.StatusName = s.StatusName
显然,我可以在这些 ...Name 列上创建索引,但使用该值来检索键感觉是倒退的。第一个问题-考虑到用例,这种反向索引是好的做法吗?
方法 1 - 输出恒等对
作为替代方案,我的第一遍是使用合并的输出子句将标识值捕获到临时表/表变量中,如下所示:
DECLARE @ManagerOutput TABLE (ProjectID int, ManagerID int);
MERGE INTO [Managers] AS tgt
USING (SELECT ProjectID, ManagerName FROM SourceData) as src
ON tgt.ManagerName = src.ManagerName
WHEN NOT MATCHED THEN
INSERT (ManagerName)
VALUES (src.ManagerName)
WHEN MATCHED THEN
UPDATE SET tgt.ManagerName = tgt.ManagerName
OUTPUT src.ProjectID, inserted.ManagerID INTO @ManagerOutput;
[...]
INSERT INTO Projects
sd.ProjectID
, m.ManagerID
[...]
FROM SourceData AS sd
LEFT JOIN @ManagerOutput AS m ON sd.ProjectID = m.ManagerID
LEFT JOIN [...]
它确实让我得到了更好的 int->int 连接,但不仅创建表变量有开销,而且在合并中强制将现有 ID 包含在插入的伪表中的假更新也效率低下。我认为对此的试金石是针对大型数据集执行这两种方法,并相互比较它们。
一种奇怪的伎俩?
它确实让我思考是否可以以非正统的方式组合表数据。使用上面方法 1 中的 Merge 语句,这是我的计划:
方法 2 - 哑连接
- 按项目 ID 订购数据源
- 订单管理器按项目 ID 输出
- 将两者毫无逻辑地结合起来
- 插入项目
第二个问题——这可能吗?- 我知道我可以使用ROW_NUMBER() OVER列创建几个 CTE ,并连接行号,但我确实在寻找一个完全愚蠢的 SELECT 语句,其功能类似于 UNION ALL,只不过不是在末尾附加两个表 -最后,它将它们并排附加。
方法 3 - 哑更新
我最后的想法是(几乎)完全避免连接,而有利于在现有行的顶部插入/更新。我的意思是这样的:
DECLARE
@ManagerOutput TABLE (ProjectID int, ManagerID int),
@StatusOutput TABLE (ProjectID int, StatusID int),
[...]
@IDs TABLE (
ProjectID int
, ManagerID NULL int
, StatusID NULL int
[...]
)
INSERT INTO @IDs (ProjectID, ManagerID)
SELECT * FROM @ProjectOutput
其想法是,如果所有表均按 ProjectID 排序,则获取 @StatusOutput.StatusID 的所有行并将它们“粘贴”到 @ID 中现有的 NULL 值上将与 ProjectID 完全关联。
但显然,这并不是一种非常 SQL 的思维方式。我知道我可以通过合并或更新连接来实现这一点,甚至是一些非常愚蠢的事情,比如删除 @ID 的内容,将删除的行与 @StatusOutput 一起输出,然后递归地将它们插入回 @ID 中。没有一种解决方案比一开始就进行连接更好。
第三个问题-是否可以在没有底层逻辑的情况下盲目插入/更新整个列?
我怀疑第二个问题和第三个问题的答案都是——“不,绝对不是。这个功能不存在是为了保护您的数据免受您的侵害。” 但我很想听听任何可能探索过这些可能性的人的意见。与此同时,我将继续制作原型并比较实际可行的方法。
谢谢!