Inserting result of stored procedure into a table with additional column

Question

I've found hundreds of articles about how to insert output of stored procedure into a table but nobody goes a step further and explains how to insert additional data as column.

I'm looping through a table and execute store procedure which I insert details into tempTable however I need to add additional column with an ID from second table. I could change stored procedure to return that additional ID however I need to run it on 20+ databases on different servers which would be time consuming.

DECLARE @c_id_view varchar(255)
DECLARE @c_id int
DECLARE cur CURSOR FOR SELECT c_id FROM #temp where ins != 'Health'
OPEN cur
FETCH NEXT FROM cur INTO @c_id_view
WHILE @@FETCH_STATUS = 0 BEGIN
set @c_id = (select c_id from claim where c_id_view = @c_id_view)
insert into #tempbedsNONIL (c_id_view,room_Type,ward_name,common_bed_name,bed_name,no_of_bed,no_of_days) values (@c_id_view, usp_patient_transfer_retrieve_forPDF @c_id) -- call your sp here
FETCH NEXT FROM cur INTO @c_id_view
END
CLOSE cur 
DEALLOCATE cur

I know that to insert SP results into a table I just need to do INSERT INTO #TEMP EXEC sp_test but in my case I need to enter extra value. This might be a stupid question but I can't figure out how to achieve it.

Answer 1

A possible option is to use the technique from this post.

Basically, you declare a table variable to hold the results of the stored procedure and then use an additional insert statement to add the extra column.

In the following example, 3 columns are returned from the sp_readerrorlog stored procedure and the additional insert adds servername.

Just make sure you truncate the table variable before each invocation of the stored procedure within your loop.

DECLARE @readerrorlog_results TABLE (
 LogDate date,
 ProcessInfo varchar(max),
 Text varchar(max)
)
INSERT INTO @readerrorlog_results
 (LogDate,ProcessInfo,Text)
EXEC sp_readerrorlog 0, 1, 'Login failed'
INSERT INTO FailedLogins
 (LogDate, ServerName,ProcessInfo,Text)
SELECT LogDate,@@SERVERNAME,ProcessInfo,Text FROM @readerrorlog_results