August 20, 2013 at 8:50 am
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!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
August 20, 2013 at 8:53 am
INSERT INTO #tempTable
EXEC dbo.someStoredProc
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 20, 2013 at 9:01 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2013 at 9:01 am
Awesome -- thanks!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
August 20, 2013 at 9:22 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2013 at 9:25 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2013 at 9:29 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2013 at 6:49 pm
Excellent suggestion. Also,
insert into #tab
exec (@MyDynamicCode)
But "insert into" will only insert into an existing table. What about creating a new table?
August 21, 2013 at 11:22 pm
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
Change is inevitable... Change for the better is not.
August 21, 2013 at 11:38 pm
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.
August 22, 2013 at 3:28 am
cmerrell (8/21/2013)
But "insert into" will only insert into an existing table. What about creating a new table?
CREATE TABLE ...
INSERT INTO
EXEC ...
Caveat is that you have to know the structure of the result set, but to be honest if you have a procedure that's non-deterministic in terms of the result set it's returning, there's probably something deeper that needs fixing/changing
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2013 at 4:06 pm
The output table is the result of a dynamic pivot, so the columns created depends on the values in the data. Yes, its non-deterministic, but fairly standard practise, is it not?
November 4, 2014 at 6:53 am
Can we insert the results of above CTE into temp table?
November 4, 2014 at 6:09 pm
You can, but you can't read the temp table after your initiating procedure regains control, the table has disappeared!
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy