OUTPUT in Dynamic SQL

Question

The following sql query is part of the query I have. The idea is that there will be a loop (cursor) over the result set inserting records one by one in another table. It is dynamic sql because of reasons not entirely known to me, but which I have to work with. I left the cursor and a lot of column names out for clarity.

    DECLARE @db_name VARCHAR(500)
    SET @db_name = '[TESTDB].DBO.[TestTable]'

    DECLARE @guid UNIQUEIDENTIFIER
    SET @guid = '15D372FC-63AF-415B-8404-46A15ABE9488'

    DECLARE @sql NVARCHAR(max)
    SET @sql = 'INSERT INTO dbo.OtherTable
               (
                    guid,
                    description
               )
               SELECT NEWID(),
                      description
               FROM ' + @db_name

    DECLARE @result varchar(max)
    EXEC sp_executesql @sql,
    N'OUTPUT',
    OUTPUT;

So the idea is that the records from the result statement gets looped over, and the data from each row is inserted into new row in OtherTable. Once a record is inserted into OtherTable, the unique identifier that was generated with NEWID() (which, mind you, is a guid stored in varchar(40)) must be captured so it can be written away in yet another table.

I would please like some help with this. Thank you!

Answer 1

With your edit the overall setup becomes a bit more clear. If I understood it correctly, then without any dynamic SQL the query should be simple INSERT with OUTPUT clause.

INSERT INTO dbo.OtherTable
(
    guid,
    description
)
OUTPUT inserted.guid AS ID INTO dbo.ThirdTable(ID)
SELECT 
    NEWID(),
    description
FROM dbo.TestTable
;

Rows from TestTable are inserted into OtherTable. Values generated by NEWID go into the OtherTable via INSERT and into ThirdTable via OUTPUT clause.

With dynamic SQL you simply build this string by concatenating the name of the source table and then run it with simple sp_executesql without any OUTPUT parameters.