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.