While the OUTPUT clause does have limitations is it perfect for this type of action. You cannot directly insert into a table with keys as suggested, but you can insert the data into an intermediate local temp table. You can then use this table to drive your joins and insert into the detail table.

USE [tempdb]

GO

CREATE TABLE [Master](id INT IDENTITY PRIMARY KEY CLUSTERED,col CHAR(1))

CREATE TABLE [Detail](Id INT IDENTITY, MID INT REFERENCES [Master](Id), Col_Descr VARCHAR(50));

CREATE TABLE [SomeOtherTable](Col CHAR(1), Descr VARCHAR(50))

INSERT INTO dbo.[SomeOtherTable] VALUES ('a','This is a better description')

GO

IF OBJECT_ID('tempdb.dbo.#M') IS NOT NULL

BEGIN

DROP TABLE #M;

END;

CREATE TABLE #M(ID INT PRIMARY KEY CLUSTERED,Col CHAR(1));

INSERT INTO dbo.[Master] OUTPUT INSERTED.Id, INSERTED.Col INTO #M

SELECT 'a'

INSERT INTO dbo.Detail (MID,Col_Descr)

SELECT M.ID, sot.Descr

FROM #M m

INNER JOIN dbo.[SomeOtherTable] sot ON m.[Col] = sot.Col

SELECT * FROM dbo.[Master]

SELECT * FROM dbo.[Detail]

/*

id col

----------- ----

1 a

Id MID Col_Descr

----------- ----------- --------------------------------------------------

1 1 This is a better description

*/

GO

--cleanup

DROP TABLE dbo.Detail

DROP TABLE dbo.[Master]

DROP TABLE dbo.[SomeOtherTable]