Solved

exec store proc and insert values into new table

Posted on 2011-09-20
14
418 Views
Last Modified: 2012-05-12
Hello ,

I have a stored procedure that creates 2 columns with joined values , i was wanting to run this store proc and insert the results into a new table, Is this possible? (It is a big list and was wanting to run this as a job overnight so the new table is available for users. So would like it to update the table or just delete the contents and enter the new values?)

any help would be grand.

 
0
Comment
Question by:deanmachine333
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36566689
In using OPENROWSET we need to turn on the ability to run distributed queries on your server. To enable this is simple, given you have the appropriate permissions. It’s a simple process when we do not have to pass in parameters.

sp_configure 'Ad Hoc Distributed Queries',1  

RECONFIGURE WITH OVERRIDE

Using OPENROWSET for a Stored Procedure with No Parameters
To get the result set is simple, simply call OPENROWSET passing the parameter of driver name, connection string and command text and we’re finished.

SELECT *  

INTO #Jobs  

FROM OPENROWSET('SQLNCLI', 'server=SERVERNAME\INSTANCENAME;trusted_connection=yes',  

'set fmtonly off exec msdb.dbo.sp_help_job')


refer for more details
http://sqlserverplanet.com/sql/insert-stored-procedure-results-into-table/
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 36566694
Hi,

you can execute INSERT, DELETE, UPDATE, SELECT statements and so on inside your stored procedure. I cannot see what your problem is...

Maybe more info can be useful.


Cheers

0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 36566699
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 21

Expert Comment

by:JestersGrind
ID: 36566711
You can insert the results of a stored procedure into a table.  You first need to create the table with the same columns and appropriate data types as what is outputted from the stored procedure.  They you simply run:

INSERT INTO YourTable
EXECUTE YourStoredProcedure

Greg

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36566763

if you procedure produces just a single result set , and contains all the
columns that are needed for the table being populated then  like this

INSERT INTO YourTable
 (list of columns in the order the procedure outputs them)
EXECUTE YourStoredProcedure  @parm1,@parm2,...
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 36566779
why you want to execute

INSERT INTO table
EXECUTE stored_proc

when you can execute the INSERT statement inside the stored procedure ??

can anybody explain if there's any benefit ?

cheers
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36566819
It sounds like from the description, correct me if I'm wrong, that the author want to put the results of a stored procedure into a table.  There's no need to change the stored procedure to do that.  In fact, if there is anything else using the same stored procedure, you would not want to change it's behavior.

Greg

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36566948
the requirement is presumably transient, so why increase the plan size of the existing stored procedure?

also the requirement may be to be able to put the results into many different tables...

0
 

Author Comment

by:deanmachine333
ID: 36567152
Thanks for all replies i have tried the normal insert into _ exec _ but my store proc is nested so wont allow this to insert the rows.

any ideas?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 36567178
@raulggonzalez:

"why you want to execute

INSERT INTO table
EXECUTE stored_proc

when you can execute the INSERT statement inside the stored procedure ??'

If stored procedure's primary scope is to populate the table then I agree, you should add the insert into the procedure's code. However if the stored procedure is used mainly to just generate an output then you don't want it to insert data in any table. In that case when the need arise, for whatever reason, to grab the result into a table for further use you can use the INSERT INTO ... EXEC proc feature.

Important here is more that is possible and very useful and less why you do it one way or another.
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 36567442
@Zbertoc

Can be as simple as an optional parameter to control whether to execute it as SELECT or INSERT.

I don't really like doing this outside, but if for whatever reason the SP cannot be modified, will work as a workaround.

That's only my opinion. Is true that there's no only one way to achieve this task, but wrapping into an insert wouldn't be my choice.

cheers.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 36567504
Is not a good idea to write a stored procedure that will do a different thing every time you change a parameter. You will lose the advantages that comes with the stored procedure, caching and per-compiling.

You also have to think of the scenario of existing stored procedures that were build with the purpose of returning some results, a very common situation. It doesn't make sense to modify those just because you want to insert its data into a table. You simply use INSERT INTO ... EXEC proc.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36570714
sorry not following your procedure is nested so not allowing to insert?

what error do you get?

why can't you just execute the sub procedure?

please explain further
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36573067
Yup you can do it

Create Job In SQL Agent using Right click => new Job

Write code in Text Block and select the target database in drop down.


Insert Into Table (Col1, Col2) Select Col1, Col2 From Table1 Inner Join Table2 on Table1.ID = Table2.ID
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question