If the restrictions I mentioned don't apply, the following extension of Adam's code illustrates a single-step alternative:

Setup:

USE tempdb;

GO

CREATE TABLE dbo.MasterRecord

(

master_id INTEGER IDENTITY PRIMARY KEY,

col CHAR(1) NOT NULL,

);

CREATE TABLE dbo.DetailRecord

(

detail_id INTEGER IDENTITY NOT NULL,

master_id INTEGER NOT NULL,

col_desc VARCHAR(50) NOT NULL,

);

CREATE TABLE dbo.SomeOtherTable

(

col CHAR(1) NOT NULL,

data VARCHAR(50) NOT NULL,

);

INSERT dbo.SomeOtherTable

(col, data)

VALUES ('a','This is a better description');

Single step solution:

INSERT dbo.DetailRecord

(master_id, col_desc)

SELECT INS.master_id, INS.data

FROM (

MERGE dbo.MasterRecord MR

USING (

SELECT SOT.col, SOT.data

FROM (VALUES ('a')) New (data)

JOIN dbo.SomeOtherTable SOT

ON SOT.col = New.data

) MS

ON (MS.col = MR.col)

WHEN NOT MATCHED BY TARGET

THEN INSERT (col) VALUES (MS.col)

OUTPUT INSERTED.master_id, INSERTED.col, MS.data

) INS;

Results and cleanup:

SELECT * FROM dbo.MasterRecord

SELECT * FROM dbo.DetailRecord

-- Cleanup

DROP TABLE dbo.DetailRecord;

DROP TABLE dbo.MasterRecord;

DROP TABLE dbo.SomeOtherTable;