Merge data in SQL Server 2016

Question

I have a really generic question about SQL Server.

I have a SP that is having as input a @reportID parameter and is returning the history of that report. As I have about 3000 reports and need to process the history of each report, I created a temporary table where to insert the returned data, but without the report ID is useless.

OPEN cursor_reportStats
FETCH NEXT FROM cursor_reportStats INTO @ReportID

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @temp
EXEC dbo.GetHistory @ReportID

FETCH NEXT FROM cursor_reportStats INTO @ReportID
END

So what I need is to attach the @ReportID to each line returned by GetHistory

Many thanks

Answer 1

Add second @temptable which will have the ReportID column + the rest of the data. Your @temp table will be buffer table and will be deleted on each iteration. At the end of each iteration you will insert the current @ReportID value and the data from the buffer table in the second @temptable.

So, you will have something like this:

OPEN cursor_reportStats
FETCH NEXT FROM cursor_reportStats INTO @ReportID

WHILE @@FETCH_STATUS = 0
BEGIN

DELETE FROM @temp;

INSERT INTO @temp
EXEC dbo.GetHistory @ReportID


INSERT INTO @temptable
SELECT @ReportID, *
FROM @temp

FETCH NEXT FROM cursor_reportStats INTO @ReportID
END