SQL Insert with data from multiple tables

Question

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

MessageCategory, MessageStatus and MessageLevel have only three fields: Identity (primary key), code and description. Messages belong to these three fields and have several other data fields, including the Identity (primary key) MessageText and Order. Identity fields are auto-incrementing fields.

Now I need to write an SQL script to add some default data to all four tables. The problem is that I need to create a script that will be sent to the client, which will then execute this script. I cannot write more intelligent code to do the whole update. And although the three tables are just simple inserts, this is the Messages column, which causes me additional headaches.

I cannot delete any indexes, and I cannot assume that it starts counting from 1 for primary keys.

So, as an example, its some data:

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

And messages will 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
);

This will not work. So what is the trick to make this work? (Saving readable code too ...)

, . , , , , -...

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 ..) . - , . , SELECT .

Answer 2

script, :

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