INSERT INTO a Table from a SQL Server Stored Procedure Result Set all except one column

Question

So I need to INSERT INTO a Table from a SQL Server Stored Procedure Result Set.

I totally get how to do that. However, for one of my data columns, [NDC], I want to use the variable I've defined rather than what is returned via the SQL Server Stored Procedure.

Do I have to store this to a Table Variable first before actually INSERTing the row to the raw table then?

FETCH NEXT FROM [C]
INTO @New Direction Church ,
@NDC9
;

WHILE @@Fetch _Status = 0
BEGIN
;
BEGIN TRANSACTION
;

	SELECT	@NDC  
	;  
	SELECT	@NDC9  
	;  

	--DELETE	FROM [dbo].[Monographs]  
	--WHERE	[NDC] = @NDC  
	--;  

	INSERT	INTO @MonographsTable  
		(  
		[NDC],  
		[GPI],  
		[DrugName],  
		[SectionCode],  
		[Txt]  
		)  
	EXEC [Eagle].[Patient_Education_Monographs_Load_By_NDC] @NDC_In = @NDC,  
															@NDC9_In = @NDC9  
	;  

	SELECT	*  
	FROM	@MonographsTable  

;

	UPDATE	@MonographsTable  
	SET		[NDC]	=	@NDC  

;

	SELECT	*  
	FROM	@MonographsTable  
	;  

	IF	@@RowCount = 0  
		ROLLBACK  
		;  
	ELSE  
		COMMIT  
		;  

	FETCH NEXT FROM [C]  
	INTO @NDC,  
		@NDC9  
		;  

END  
;  

CLOSE [C]
;
DEALLOCATE [C]
;
GO

Any help would be GREATLY appreciated. I tried a Sub-Select of that Variable to try and directly INSERT into the target table but that doesn't seem to work either.

Thanks in advance for your help.

Answer 1

I did...but was wondering if I could just INSERT directly to the table using the Variable rather than moving it first to a Table Variable...Updating it...and then INSERTing from the variable table

Answer 2

I will have to admit that I cannot think of a better solution. Well, you can look at my article How to Share Data between Stored Procedures for alternatives to INSERT-EXEC, but I suspect that you will find changing the procedure to be too heavy artillery.

Answer 3

Hi @Bobby P ,

In my experience, it seems that this is the most common and convenient method.

Regards
Echo