SQL Insert with data from multiple tables

Question

I have four tables: Messages, MessageCategory, MessageStatus and MessageLevel. 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. 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. I cannot remove any indices and I cannot assume it starts counting at 1 for the primary keys. So, as an example, her's some data: ``` INSERT INTO MessageCategory (Code) Values ('Cat01'); INSERT INTO MessageStatus (Code) Values ('Status01'); INSER…

Accepted Answer

```
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]
```

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.

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 Insert with data from multiple tables

Solution

Problem