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.
Best Answer
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 additionalinsert
addsservername
.Just make sure you truncate the table variable before each invocation of the stored procedure within your loop.