How to load a temp table with the result set of a sproc

Question

How to load a temp table with the result set of a sproc

dndaughtery

SSChampion

Points: 11290
More actions
May 14, 2012 at 10:25 am

#274073

I need to load a temp table with the result set from a sproc sort of like the following:
Select into #Temp
Exec usp_LoadTemPTableSproc
How can I do this?

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

dndaughtery (5/14/2012)
I need to load a temp table with the result set from a sproc sort of like the following:
Select into #Temp
Exec usp_LoadTemPTableSproc
How can I do this?

Close. The temp table needs to already exist.

INSERT INTO #Temp

Exec usp_LoadTemPTableSproc;

Geoff A SSChampion Points: 11511 More actions · Answer 2

or even better, declare a table variable.

declare @temp table (MyColumns varchar(128),......)

insert into @temp

exec usp_myprocedure

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Geoff A (5/14/2012)
or even better, declare a table variable.
declare @temp table (MyColumns varchar(128),......)
insert into @temp
exec usp_myprocedure

I have to ask, what's better about a table variable?

Geoff A SSChampion Points: 11511 More actions · Answer 4

Hi Lynn,

Q1: Why were table variables introduced when temporary tables were already available?

A1: Table variables have the following advantages over temporary tables: •As mentioned in the SQL Server Books Online "Tables" article, table variables, such as local variables, have a well defined scope at the end of which they are automatically cleared.

•Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.

•Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.

from http://support.microsoft.com/kb/305977

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Geoff A (5/14/2012)
Hi Lynn,
Q1: Why were table variables introduced when temporary tables were already available?
A1: Table variables have the following advantages over temporary tables: •As mentioned in the SQL Server Books Online "Tables" article, table variables, such as local variables, have a well defined scope at the end of which they are automatically cleared.
•Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
•Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.
from http://support.microsoft.com/kb/305977

Nice textbook answer, unfortunately we don't know how this table (table variable or temporary table) is going to be used. I have seen where table variables work well, but I have also seen where replacing table variables with temporary tables dramaticaly improved processing of stored procedures/scripts. So, it really comes down to what you are doing and being sure to test, test, and test again.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Geoff A (5/14/2012)
Hi Lynn,
Q1: Why were table variables introduced when temporary tables were already available?
A1: Table variables have the following advantages over temporary tables: •As mentioned in the SQL Server Books Online "Tables" article, table variables, such as local variables, have a well defined scope at the end of which they are automatically cleared.
•Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
•Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.
from http://support.microsoft.com/kb/305977

Geoff:

That says why they were introduced by Microsoft, but it does not tell us why it might be better than a temporary table in this case. Table variables do have disadvantages as well...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Ack! Lynn beat me by 55 seconds!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

Geoff A (5/14/2012)
Hi Lynn,
Q1: Why were table variables introduced when temporary tables were already available?
A1: Table variables have the following advantages over temporary tables: •As mentioned in the SQL Server Books Online "Tables" article, table variables, such as local variables, have a well defined scope at the end of which they are automatically cleared.
•Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
•Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.
from http://support.microsoft.com/kb/305977

BWAAA-HAAAA!!! Ok... Good friend and respected fellow DBA, I simply MUST take the contrary stance here, Geoff. 😛

So far as I'm concerned, the only reasonable answer to the question is because temporary tables can't be used in functions. 😉

Temporary tables also have a very well defined scope. They go out of scope when the proc stops running. They also have the benefit of being available in "downward" scope making it quite easy to pass volumes of information to other stored procs called by a main proc. Although table variables may result in fewer recompiliations, it always necessary to recompile at the statement level to get a table variable to register more than one row with the optimizer. To wit, the recompiles caused by populating a Temp Table frequently save a developer's unwitting bacon. Further, table variables do not and cannot be made to use statistics no matter how big the table variable gets. While it may true that a table variable will do less logging, table variables still use TempDB and are frequently missused because of that misunderstanding. It's also true that Temp Tables will use memory just like a table variable does until they both get to big to fit.

The really bad part about the scope of table variables is that they don't even persist in a run in SSMS which means that special coding must frequently be done to reveal their contents before the run stops and, once the run stops, the run must be restarted to re-reveal their contents. Temp tables are nice enough to persist in SSMS for troubleshooting purposes. And, no... Temp Tables don't require a "drop" to cleanup in a stored procedure.

Personally, if Temp Tables were allowed in functions, I'd never use a Table Variable. But, that's just me. 😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 9

dndaughtery (5/14/2012)
I need to load a temp table with the result set from a sproc sort of like the following:
Select into #Temp
Exec usp_LoadTemPTableSproc
How can I do this?

You can use SELECT INTO to build temp tables on the fly from the output of a stored procedure when you use OPENROWSET or OPENQUERY.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

Here's an example that serves me very well for some of the larger ETL projects I work on. Do note that you have to change "yourseverinstancenamehere" appropriately.

SELECT IDENTITY(INT,1,1) AS RowNum, *

INTO #MyFiles

FROM OPENROWSET('SQLOLEDB','Server=yourserverinstancenamehere;Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.xp_DirTree ''C:\Temp'',1,1')

WHERE [File] = 1

ORDER BY SubDirectory

SELECT * FROM #MyFiles;

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Geoff A SSChampion Points: 11511 More actions · Answer 11

Jeff Moden (5/14/2012)
Geoff A (5/14/2012)
Hi Lynn,
Q1: Why were table variables introduced when temporary tables were already available?
A1: Table variables have the following advantages over temporary tables: •As mentioned in the SQL Server Books Online "Tables" article, table variables, such as local variables, have a well defined scope at the end of which they are automatically cleared.
•Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
•Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.
from http://support.microsoft.com/kb/305977
BWAAA-HAAAA!!! Ok... Good friend and respected fellow DBA, I simply MUST take the contrary stance here, Geoff. 😛
So far as I'm concerned, the only reasonable answer to the question is because temporary tables can't be used in functions. 😉
Temporary tables also have a very well defined scope. They go out of scope when the proc stops running. They also have the benefit of being available in "downward" scope making it quite easy to pass volumes of information to other stored procs called by a main proc. Although table variables may result in fewer recompiliations, it always necessary to recompile at the statement level to get a table variable to register more than one row with the optimizer. To wit, the recompiles caused by populating a Temp Table frequently save a developer's unwitting bacon. Further, table variables do not and cannot be made to use statistics no matter how big the table variable gets. While it may true that a table variable will do less logging, table variables still use TempDB and are frequently missused because of that misunderstanding. It's also true that Temp Tables will use memory just like a table variable does until they both get to big to fit.
The really bad part about the scope of table variables is that they don't even persist in a run in SSMS which means that special coding must frequently be done to reveal their contents before the run stops and, once the run stops, the run must be restarted to re-reveal their contents. Temp tables are nice enough to persist in SSMS for troubleshooting purposes. And, no... Temp Tables don't require a "drop" to cleanup in a stored procedure.
Personally, if Temp Tables were allowed in functions, I'd never use a Table Variable. But, that's just me. 😉

Thanks Jeff. you know i would never ever disagree with anything you say. you are a truely a master when it comes to optimizing code in SQL Server.

as a Systems DBA, i was only trying to offer a different way to populate a 'temporary' staging table. my comments were coming from the place i work (your previous employer). all their code contains at least 1 "into #temp" statement. and often , their code has several "into #temp" statements.

i have found that CTE's are usually the best, but when a larger set of data is coming back, i prefer @table variables with a defined index on one of the columns by defining a primary key or unique constraint.

while defining a primary key or unique constraint on a #temp table is also possible, i find that I then have to use of (Keep Plan) or (Keep Fixed Plan) hints to avoid recompilation.

so, not to ruffle any feathers, i should have added to my comment that "i like to use" instead of "even better" 😛

so not to make this a full on debate, this is just what "works for me where i work". everyone elses mileage will vary. 😉

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 12

Absolutely no problem there, Geoff. I actually feel dwarfed by your knowledge of SQL Server in many areas especially on the system side.

Yeah, I remember all the temp tables that people used there. :sick: Sometimes it was a good thing and sometimes it was just folks being lazy and sometimes it was people that just couldn't figure out a better way. As you mention, CTE's and other forms of "derived tables" are excellent tools they didn't really seem to embrace there.

I just didn't want people to think that using Table Variables was a performance and resource panacea for the replacement of temp tables. Again, as you very appropriately mentioned, CTE's and other forms of "derived tables" should also be considered.

Shifting gears, we've got to get together and talk about "old times" there. Maybe lunch or a couple of sandwiches at the picnic table someday?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Jeff Moden (5/14/2012)
Here's an example that serves me very well for some of the larger ETL projects I work on. Do note that you have to change "yourseverinstancenamehere" appropriately.
SELECT IDENTITY(INT,1,1) AS RowNum, *
INTO #MyFiles
FROM OPENROWSET('SQLOLEDB','Server=yourserverinstancenamehere;Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.xp_DirTree ''C:\Temp'',1,1')
WHERE [File] = 1
ORDER BY SubDirectory
SELECT * FROM #MyFiles;

Thought I'd seen something like this before but a cursory look at Books Online wasn't enough to jog the memory. Thanks for the assist on this.

Geoff A SSChampion Points: 11511 More actions · Answer 14

Jeff Moden (5/15/2012)
Absolutely no problem there, Geoff. I actually feel dwarfed by your knowledge of SQL Server in many areas especially on the system side.
Yeah, I remember all the temp tables that people used there. :sick: Sometimes it was a good thing and sometimes it was just folks being lazy and sometimes it was people that just couldn't figure out a better way. As you mention, CTE's and other forms of "derived tables" are excellent tools they didn't really seem to embrace there.
I just didn't want people to think that using Table Variables was a performance and resource panacea for the replacement of temp tables. Again, as you very appropriately mentioned, CTE's and other forms of "derived tables" should also be considered.
Shifting gears, we've got to get together and talk about "old times" there. Maybe lunch or a couple of sandwiches at the picnic table someday?

absolutely. and i know when you say "couple of sandwiches", you mean "a few beers". I'm there, buddy. 😛