多表数据插入的SQL语句

Question

我有四个表：Messages，MessageCategory，MessageStatus和MessageLevel。

MessageCategory，MessageStatus和MessageLevel只有三个字段：Identity（主键），Code和Description。 Messages引用这三个字段，并具有一些其他数据字段，包括Identity（主键），MessageText和Order。 Identity字段是自增的字段。

我现在需要编写一个SQL脚本，向所有四个表添加一些默认数据。问题是，我需要创建一个脚本，该脚本将被发送给客户，然后由客户执行此脚本。我无法编写更加智能的代码来完成整个更新。虽然三个表只是简单的插入语句，但是Messages表使我额外头疼。

我不能删除任何索引，也不能假定它从1开始计算主键。

所以，以一个示例为例，以下是一些数据：

INSERT INTO MessageCategory (Code) Values ('Cat01');
INSERT INTO MessageStatus (Code) Values ('Status01');
INSERT INTO MessageLevel (Code) Values ('Level01');

而且消息需要类似于这样的内容：

INSERT INTO Messages(Category, Status, Level, MessageText, Order) 
VALUES(
  (SELECT Identity from MessageCategory where Code='Cat01'), 
  (SELECT Identity from MessageStatus where Code='Status01'), 
  (SELECT Identity from MessageLevel where Code='Level01'), 
  'Just some message', 
  1
);

但是那样做行不通。那么，让这个工作的诀窍是什么呢？（同时保持代码可读性...）

不幸的是，我无法访问另一个数据库。我可以测试它，但一旦它似乎可以工作，只需要发送并祈祷它能正常工作...

- Wim ten Brink

Answer 1

INSERT INTO Messages 
  (Category, Status, Level, MessageText, [Order]) 
SELECT
  (SELECT TOP 1 [Identity] from MessageCategory where Code='Cat01')  AS Category,
  (SELECT TOP 1 [Identity] from MessageStatus where Code='Status01') AS Status,
  (SELECT TOP 1 [Identity] from MessageLevel where Code='Level01')   AS Level,
  (SELECT 'Just some message')   AS MessageText, 
  (SELECT 1)                     AS [Order]

以上内容适用于SQL Server。请注意， Identity 和 Order 都是保留的T-SQL关键字，不应用作列名称。此外，请注意，子查询不能返回多行，以确保包括 TOP 1 语句。

接下来要注意的是，列别名（ AS Category 等）并不是必需的。它们的顺序是最重要的。我会为可读性包括它们，特别是当选择列表变得更长时。

- Tomalak

---

我倾向于在SQL脚本中的所有表名和字段名周围始终使用方括号[]。这解决了可能的冲突。我倾向于使用更多保留字作为字段和表名，因此添加括号对我来说是一种自动化操作。 :-) - Wim ten Brink

所以你应该在你的示例代码中也这样做。;-) - Tomalak

是的，我预料到那些括号会让一些人感到困惑，所以我实际上已经将它们删除了。 :-) - Wim ten Brink

请注意，这仅适用于您的表中Code是唯一的情况。在此情况下我默认它是唯一的，但是... - Kendrick

我无论如何都保留了top-1。 :-) 在这些字段上有一个唯一键，所以不可能出现这种情况，但还是... 它展示了插入查询的本质。 - Wim ten Brink

Answer 2

如果是一个单独的脚本，可以将身份信息存储在变量中：

declare MessageCategoryID int;
declare MessageStatusID int;
declare MessageLevel int;
INSERT INTO MessageCategory (Code) Values ('Cat01');
set @MessageCategoryID=scope_identity();
INSERT INTO MessageStatus (Code) Values ('Status01');
set @MessageStatudID=scope_identity();
INSERT INTO MessageLevel (Code) Values ('Level01');
set @MessageLevelID=scope_identity();

INSERT INTO Messages(Category, Status, Level, MessageText, Order) 
    VALUES(
        @MessageCAtegoryID,
        @MessageStatusID,
        @MessageLevelID,
        'Just some message',
        1);

- Kendrick

---

这是一个单一的脚本，但在消息表中会有几十条记录。 - Wim ten Brink

请改用 scope_identity()，而不是 @@identity。如果向表中添加触发器，使用 @@identity 可能会导致严重的数据完整性问题。 - HLGEM

以前从未使用过scope_identity()，但从现在开始会用了。谢谢这个提示！ - Kendrick