I know this is an old one, but I needed an answer to this and couldn't find one. Below is how I solved the problem, with the sample code addressing your original problem, for anybody else who wanders across this thread. It's not especially efficient (and would naturally be less-so with more columns in the tables) but 'works', and will play nicely in multi-user environment.

BEGIN TRANSACTION

DECLARE @Today DATETIME

SET @Today = GETDATE()

DECLARE @Temp TABLE

(

ID INT ,

[val1] [varchar](50) ,

[val2] [varchar](50) ,

[val3] [varchar](50) ,

[entryDate] [datetime]

)

INSERT INTO [dbo].[TableA]

( val1 ,

val2 ,

val3 ,

entryDate

)

OUTPUT INSERTED.ID ,

INSERTED.val1 ,

INSERTED.val2 ,

INSERTED.val3 ,

INSERTED.entryDate

INTO @Temp

SELECT val1 ,

val2 ,

val3 ,

entryDate

FROM [dbo].[TableC]

INSERT INTO [dbo].[TableB]

( TableAID ,

comID ,

RelationshipID ,

xPercent ,

[status] ,

dateBegin

)

SELECT T.ID AS TableAID ,

C.comID AS comID ,

1 AS RelationshipID ,

'100.00' AS xPercent ,

1 AS [status] ,

@Today AS dateBegin

FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY val1 ASC, val2 ASC, val3 ASC, entryDate ASC ) AS RowNumber ,

ID

FROM @Temp

) AS T

INNER JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY val1 ASC, val2 ASC, val3 ASC, entryDate ASC ) AS RowNumber ,

comID

FROM [dbo].[TableC]

) AS C ON T.RowNumber = C.RowNumber

COMMIT TRANSACTION

EDIT:

Another, more efficient solution to this problem appears to be to use something like the following:

merge into [table1] as t

using [external_table] as s

on 1=0 --modify this predicate as necessary

when not matched then insert (data)

values (s.[col1])

output inserted.id, s.[col2] into [table2]

;