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

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]

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.

Answer 2

If it's a single script, store your identities in variables:

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