Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Returning stored procedure results into a CTE or temp table? Expand / Collapse
Author
Message
Posted Tuesday, August 20, 2013 8:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 2,133, Visits: 3,406
Hi all . . .

Is it possible to return the results of a stored procedure into either a CTE or temp table?

In other words, is it possible to do this:
with someCTE as (
exec someStoredProc
)

or this:
exec someStoredProc into #tempTable

???

Thanks in advance!
Post #1486286
Posted Tuesday, August 20, 2013 8:53 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 1,959, Visits: 2,893

INSERT INTO #tempTable
EXEC dbo.someStoredProc


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1486287
Posted Tuesday, August 20, 2013 9:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
The INSERT ... EXEC can be used to insert into a table variable or temp table. A CTE however, despite its name, is not a table and hence can not have anything inserted into it. Think of a CTE as a temporary view, so a select statement.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1486294
Posted Tuesday, August 20, 2013 9:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 2,133, Visits: 3,406
Awesome -- thanks!
Post #1486295
Posted Tuesday, August 20, 2013 9:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
GilaMonster (8/20/2013)
A CTE however, despite its name, is not a table and hence can not have anything inserted into it.


Acutally you can insert into a cte, just like you can delete from them.

create table cteInsert
(
SomeValue varchar(20)
);

with myCTE as
(
select SomeValue
from cteInsert
)

insert myCTE
select 'Inserted'

select * from cteInsert;

with myDeleteCte as
(
select SomeValue
from cteInsert
)

delete myDeleteCte
select * from cteInsert;

drop table cteInsert

In reference to the thread however it doesn't make much sense.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1486312
Posted Tuesday, August 20, 2013 9:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
Sean Lange (8/20/2013)
GilaMonster (8/20/2013)
A CTE however, despite its name, is not a table and hence can not have anything inserted into it.


Acutally you can insert into a cte, just like you can delete from them.


No, actually you can't. What you can do is insert into a table (permanent, temporary or variable) through a CTE, just as you can with a view (if it is updateable).
You're not inserting into the CTE because a CTE has no persistent storage, it's just a select statement. The insert is into the base tables that the CTE is defined upon.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1486317
Posted Tuesday, August 20, 2013 9:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
GilaMonster (8/20/2013)
Sean Lange (8/20/2013)
GilaMonster (8/20/2013)
A CTE however, despite its name, is not a table and hence can not have anything inserted into it.


Acutally you can insert into a cte, just like you can delete from them.


No, actually you can't. What you can do is insert into a table (permanent, temporary or variable) through a CTE, just as you can with a view (if it is updateable).
You're not inserting into the CTE because a CTE has no persistent storage, it's just a select statement. The insert is into the base tables that the CTE is defined upon.


True enough. I should have said that you can use them as the source for those operations but the data will really reside in the base table. Thanks for the correction.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1486320
Posted Wednesday, August 21, 2013 6:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:16 PM
Points: 24, Visits: 157
Excellent suggestion. Also,

insert into #tab
exec (@MyDynamicCode)

But "insert into" will only insert into an existing table. What about creating a new table?
Post #1487014
Posted Wednesday, August 21, 2013 11:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,714, Visits: 31,164
cmerrell (8/21/2013)
Excellent suggestion. Also,

insert into #tab
exec (@MyDynamicCode)

But "insert into" will only insert into an existing table. What about creating a new table?


Use SELECT/INTO with OPENROWSET to call the proc. If you want to pass parameters to the proc, it's all going to have to be dynamic SQL because OPENROWSET can't take parameters.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1487053
Posted Wednesday, August 21, 2013 11:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:16 PM
Points: 24, Visits: 157
Thanks , but I'm trying to run some dynamic sql which I have built into a memory variable, rather than calling a stored procedure. Something like:

select *
into #tab
from exec (@MyDynamicCode)

which I know is wrong. I may not have rights to use openrowset, and anyway, using OLE DB for dealing with SQL server tables within SQL Server seems somehow inappropriate.
Post #1487061
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse