Insert data into column from a stored procedure

Question

Insert data into column from a stored procedure

sql_usr

Mr or Mrs. 500

Points: 500
More actions
June 19, 2008 at 2:17 am

#192940

I am creating a table where the rows for one of the columns are rows returned by a stored procedure. How do I insert/update that particular column using the results set the stored procedure? Here's what I mean:
For example, when creating mytable (Age,Class,Name) -->I want to populate column 'Class' with data from my stored procedure as I populate 'Age' and 'Name' column with a JOIN statement.
As of now, I populated 'Age' and 'Name' column and got NULL for 'Class' Column. Is there any way to update the 'Class' Column from NULL to results set of my stored procedure.
Pls. let me know if this is not clear enough to understand.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

It is hard to describe what to do, if we don't know anything about the procedure (what columns are returned, what can be used as a key for joining to mytable etc.)... Generally, you can insert the result of a procedure into a table using

INSERT sometable

EXECUTE someprocedure

"sometable" has to be defined so that the columns match resultset of the procedure. So, one possibility is to create a temporary table, insert the resultset of procedure into it, and then join the temp table to your base table and do UPDATE.

However, it might be better to change the process and insert all data into mytable at once (including class) instead of doing it in two steps. But that's something we can hardly describe without knowing table structures, what the procedure does and so on.

Lonely Rogue SSCrazy Points: 2579 More actions · Answer 2

[font="Comic Sans MS"]@Vladan:

I guess we can't Insert the records returned by a SProc Into a table . Sql Server doesn't allow us to capture the rows returned by a Sproc since Sprocs dont have ability to return a result set. Whereas, In contrast(quite Suprisingly), one can capture the records returned by Extended Sprocs ( like system procs starting with 'XP_').

We only have provision to capture the returned rows in case of User Defined Functions and Functions. So as per your answer

INSERT sometable

EXECUTE someprocedure

fails here.

@sql_usr :

Dear, You can use SSIS in capturing the required result set from any of the Sprocs.. insert the records in "Destination" by executing required SProc in the "Source" Task.[/font]

--In 'thoughts'...
Lonely Rogue

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

Hi Vampire.

What you have said above is not 100% correct.

You can infact insert rows into a table from a SP as mentioned.

For example:

[Code]

CREATE PROCEDURE dbo.MyProc

AS

SET NOCOUNT ON

SELECT

1 as [Col1],

'B' as [Col2],

1.98 as [Col3]

RETURN

GO

DECLARE @MyTable TABLE

(Col1 INT,Col2 VARCHAR(10),Col3 DECIMAL(10,2))

INSERT INTO @MyTable

EXEC dbo.MyProc

SELECT *

FROM @MyTable

[/code]

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 4

Vampire (7/31/2008)
Sql Server doesn't allow us to capture the rows returned by a Sproc since Sprocs dont have ability to return a result set.

Absolutely not true in SQL Server... 😉 Where did you learn such a thing? I ask so that if they're in a position to teach publicly, I can fix their terrible error. Porkchops will be in order here... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lonely Rogue SSCrazy Points: 2579 More actions · Answer 5

Thanks Christhopher .

I came to this conclusion after I struggled to capture an Output from a System Sproc into a table for my analysis. Consider the below code,

[font="Comic Sans MS"]

CREATE TABLE #JobInfo (

Job_idUNIQUEIDENTIFIER null,Originating_server NVARCHAR null,

Namesysname null,Enabledtinyint null,

Descriptionnvarchar null,Start_step_idint null,

Category sysname null,Owner varchar(50) null,

Notify_level_eventlogint null,Notify_level_emailint null,

Notify_level_netsendint null,Notify_level_pageint null,

Notify_email_operator int null,Notify_netsend_operator int null,

Notify_page_operator int null,Delete_levelint null,

Date_createddatetime null,Date_modifieddatetime null,

Version_numberint null,Last_run_date int null,

Last_run_time int null,Last_run_outcome int null,

Next_run_date int null,Next_run_time int null,

Next_run_schedule_id int null,Current_execution_status int null,

Current_execution_step sysname collate database_default null,

Current_retry_attempt int null,Has_step int null,

Has_schedule int null,Has_target int null,

Type int null

)

INSERT INTO #JobInfo(Job_id, Originating_server, Name, Enabled, Description,Start_step_id ,

Category ,Owner,

Notify_level_eventlog ,Notify_level_email ,

Notify_level_netsend ,Notify_level_page ,

Notify_email_operator ,Notify_netsend_operator ,

Notify_page_operator ,Delete_level ,

Date_created ,Date_modified ,

Version_number ,Last_run_date ,

Last_run_time ,Last_run_outcome ,

Next_run_date ,Next_run_time ,

Next_run_schedule_id ,Current_execution_status ,

Current_execution_step ,

Current_retry_attempt ,Has_step ,

Has_schedule ,Has_target ,

Type ) EXEC msdb.dbo.SP_HELP_JOB @execution_status =1[/font]

Here, I tried to store the output of SP_HELP_JOB into my temp table...but i couldn't..as it resulted into an Error

At last I used SSIS to do this. In my case, please teach me how i can gather the returned records into a table using TSql.

As you said that I am not 100% correct..Is there any case where I am right.

--In 'thoughts'...
Lonely Rogue

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

Hi,

I'm not sure that it is possible with this procedure.

Because of this error.

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 58

An INSERT EXEC statement cannot be nested.

Which isn't because your code is bad, I think it's because the proc has another proc inside it causing a nest.

I guess you could take the info out of that proc and make you own proc to return what you need 🙂

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Carl Federl One Orange Chip Points: 25384 More actions · Answer 7

Ran the provides SQL and the error message is:

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.

So if you have an "insert exec sp" and any stored procedure within the call hierarchy contains an "insert exec sp" command, then this is not supported. Also tested with SQL Server 2008 and got the same error.

Reproduction:

use tempdb

go

create procedure dbo.SSC_SPR_Table_Sub

AS

set nocount on

select sysobjects.id from dbo.sysobjects

go

create procedure dbo.SSC_SPR_Table_Main

AS

set nocount on

create table #Main (id int not null)

insert into #Main (id)

exec dbo.SSC_SPR_Table_Sub

SELECT id from #Main

go

exec dbo.SSC_SPR_Table_Main

go

create table #Outer (id int not null)

insert into #Outer

exec dbo.SSC_SPR_Table_Main

go

drop procedure dbo.SSC_SPR_Table_Sub

drop procedure dbo.SSC_SPR_Table_Main

go

SQL = Scarcely Qualifies as a Language

Lonely Rogue SSCrazy Points: 2579 More actions · Answer 8

Thanks for Clarifying us Carl,

I have verified and found that if the Sproc is already constructed with an INSERT INTO stmt then our attempt to Insert the records into a table Fails.

😎

--In 'thoughts'...
Lonely Rogue