Solved

exec store proc and insert values into new table

Posted on 2011-09-20
14
445 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 27

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 27

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

630 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