SQL插入多个表中的数据 [英] SQL Insert with data from multiple tables

问题描述

我有四个表:Messages，MessageCategory，MessageStatus和MessageLevel.

I have four tables: Messages, MessageCategory, MessageStatus and MessageLevel.

MessageCategory，MessageStatus和MessageLevel都只有三个字段:标识(主键)，代码和描述.消息引用这三个字段，并具有其他一些数据字段，包括身份(主键)MessageText和Order.身份字段是自动递增的字段.

MessageCategory, MessageStatus and MessageLevel all just have three fields: Identity (primary key), Code and Description. Messages refers to these three fields and has a few other data fields, including Identity (primary key) MessageText and Order. The Identity fields are auto-incremented fields.

我现在需要编写一个SQL脚本，以将一些默认数据添加到所有四个表中.问题是，我需要创建一个脚本，该脚本将发送给客户，然后由客户执行该脚本.我无法编写更智能的代码来进行整个更新.尽管三个表只是简单的插入语句，但正是Messages表使我有些头疼.

I now need to write an SQL script to add some default data to all four tables. Problem is, I need to create a script which will be sent to a customer who will then execute this script. I cannot write a bit more intelligent code to do the whole update. And while three tables are just simple insert statements, it's the Messages table that causes me some additional headaches.

我无法删除任何索引，并且我不能假定它的主键从1开始计数.

I cannot remove any indices and I cannot assume it starts counting at 1 for the primary keys.

例如，她的一些数据:

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

这些消息需要这样的内容:

And the messages would need something like this:

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
);

那是行不通的.那么，使它起作用的诀窍是什么? (也保持代码可读性...)

That won't work, though. So, what's the trick to get this working? (Keeping the code readable too...)

不幸的是，我无权访问另一个数据库.我可以对其进行测试，但是一旦它看起来可行，那就只是发送并祈祷它可以解决的问题了……

Unfortunately, I don't have access to the other database. I can test it but once it seems to work, it's just a matter of send-and-pray-it-works...

推荐答案

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语句.

The above would work for SQL Server. Note that both Identity and Order are reserved T-SQL keywords and should not be used for column names. Also note that sub-queries must not return more than one row for this to work, to ensure that I have included TOP 1 statements.

接下来要注意的是，列别名(AS Category等)不是严格必需的.他们的顺序很重要.为了便于阅读，我将它们包括在内，尤其是当SELECT列表变长时.

The next thing to note is that the column aliases (AS Category etc.) are not strictly necessary. Their order is what counts. I would include them for readability, especially when the SELECT list gets longer.

这篇关于SQL插入多个表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！