Solved

exec store proc and insert values into new table

Posted on 2011-09-20
14
390 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now