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

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


CLOSE 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.


1 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
EXEC sp_readerrorlog 0, 1, 'Login failed'

INSERT INTO FailedLogins
      (LogDate,  ServerName,ProcessInfo,Text)
SELECT LogDate,@@SERVERNAME,ProcessInfo,Text FROM @readerrorlog_results

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.