exec store proc and insert values into new table

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.

 
deanmachine333Asked:
Who is Participating?
 
Pratima PharandeCommented:
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
 
raulggonzalezCommented:
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
JestersGrindCommented:
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
 
LowfatspreadCommented:

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
 
raulggonzalezCommented:
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
 
JestersGrindCommented:
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
 
LowfatspreadCommented:
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
 
deanmachine333Author Commented:
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
 
ZberteocCommented:
@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
 
raulggonzalezCommented:
@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
 
ZberteocCommented:
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
 
LowfatspreadCommented:
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
 
Alpesh PatelAssistant ConsultantCommented:
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
All Courses

From novice to tech pro — start learning today.