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!

"Once a record is inserted into OtherTable, the resulting primary key (which, mind you, is a guid stored in varchar(40)) must be written away in yet another table." Can you explain it little more and what you expect in output. @AyatollahOfRockNRolla — Sreemat, Jan 18 '17 at 10:28
I would recommend to do it at first without any dynamic SQL, just to understand what goes where. Dynamic SQL makes it more difficult to compose the queries. When you understand really well how it works in principal, look into converting it into dynamic SQL. — Vladimir Baranov, Jan 18 '17 at 10:29
@Sreemat I updated the text. Basically I make use of NEWID(), which I also need to capture for inserting into yet another table. — AyatollahOfRockNRolla, Jan 18 '17 at 10:31
@P.Salmon Not yet. The dynamic sql was a starting point, so I try to figure it out from there. — AyatollahOfRockNRolla, Jan 18 '17 at 10:32

score 0 · Accepted Answer · answered Jan 18 '17 at 10:44

0

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.

answered Jan 18 '17 at 10:44

Vladimir Baranov

31,799
5
53
90

Exactly! Though I have one more question, and that is about the cursor. Do you know that if I were to put this query into a cursor, will it insert one guid at a time? Or will it go through the records and insert it all into ThirdTable at once? – AyatollahOfRockNRolla Jan 18 '17 at 10:53
1

I don't understand what is the role of the cursor here. The example in my answer selects all rows from `TestTable` at once and inserts all of them into `OtherTable` and into `ThirdTable`. If you don't need all rows, you should add a `WHERE` clause that limits the rows to what you need. If you show in the question the code of your cursor I could answer more specifically. – Vladimir Baranov Jan 18 '17 at 11:09
The code that you wrote, INSERT INTO ... OUTPUT ... SELECT, is what must go into the cursor. The reason being that my colleague wants to insert everything, even if one row gives an error. I guess he means that he doesn't want that nothing gets inserted. – AyatollahOfRockNRolla Jan 18 '17 at 11:34
Still I don't understand what your cursor does. In any case, the `OUTPUT` clause inserts same number of rows into the `ThirdTable` as the main `INSERT`. – Vladimir Baranov Jan 18 '17 at 13:28

OUTPUT in Dynamic SQL

1 Answers1