store output of stored proc into temp table
-
In SQL2005 it is easy to store the output of an stored procedure into a table variable.
But how do i do this in SQL2000?
-
Can't be done, must be a temp table.
-
Cool,
do i need to declare the temp table first?
When inserting is it just?
INTO INTO #t1
EXEC sp @param1, @param2
-
Yes you need to create it first, including column definitions.
-
Ninja's_RGR'us (9/18/2008)
Yes you need to create it first, including column definitions.Heh... not exactly true...
SELECT *
INTO #MyHead
FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who')
--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)
Intro to Tally Tables and Functions -
Cheers guys.:)
-
Jeff Moden (9/18/2008)
Ninja's_RGR'us (9/18/2008)
Yes you need to create it first, including column definitions.Heh... not exactly true...
SELECT *
INTO #MyHead
FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who')
Yes it is [:)]
MadhivananFailing to plan is Planning to fail
-
Jeff Moden (9/18/2008)
Ninja's_RGR'us (9/18/2008)
Yes you need to create it first, including column definitions.Heh... not exactly true...
SELECT *
INTO #MyHead
FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who')
Hey Jeff, when you revise the code for production, which version do you preffer to use (both for performance and clarity)?
-
Madhivanan (9/19/2008)
Jeff Moden (9/18/2008)
Ninja's_RGR'us (9/18/2008)
Yes you need to create it first, including column definitions.Heh... not exactly true...
SELECT *
INTO #MyHead
FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who')
Yes it is [:)]
Why do you say "Yes it is"? There's no need to precreate the temp table for the code I wrote... it's created on the fly. Even you say it the very blog link you posted...
I prefer using method 2 as you dont need to create temp table and also you dont need to worry about the structure of the procedure
--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)
Intro to Tally Tables and Functions -
Ninja's_RGR'us (9/19/2008)
Jeff Moden (9/18/2008)
Ninja's_RGR'us (9/18/2008)
Yes you need to create it first, including column definitions.Heh... not exactly true...
SELECT *
INTO #MyHead
FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who')
Hey Jeff, when you revise the code for production, which version do you preffer to use (both for performance and clarity)?
For performance, I prefer the SELECT/INTO. For clarity, either one will do. But, like anything else, it all depends... what if I don't need all of the columns produced by the stored procedure? That would require precreation of the table and then doing an Insert/Select.
--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)
Intro to Tally Tables and Functions -
Jeff Moden (9/20/2008)
For performance, I prefer the SELECT/INTO. For clarity, either one will do. But, like anything else, it all depends... what if I don't need all of the columns produced by the stored procedure? That would require precreation of the table and then doing an Insert/Select.No, not SELECT/INTO!
SELECT/INTO lock table in system catalog for all the procedure execution time.
And those locks are not shared.
If to take this approach as a standard in busy environment deadlock are guaranteed.
It's not theoretical assumption. Been there, saw this.
And it's very hart to fix because SQL Server does not give correct error messages when it happens.
_____________
Code for TallyGenerator -
Man, I really appreciate you watching out for me, Sergiy. I've learned a lot from you. Thanks...
Ok... like I've done before when this controversy of Select/Into has come up, I ran one very long running Select/Into and 4 short ones that all ran simultaneously 10 seconds after the long running one started. If the System Tables were locked for a long term, then the 4 smaller ones would not have been able to complete before the larger one.
Here's the code for the very long running Select/Into... I hope you've got your TempDB properly configured so it doesn't need to grow... 😛
WAITFOR TIME '20:31:00'PRINT CONVERT(VARCHAR(30),GETDATE(),121)
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
-- Jeff Moden
SELECT TOP 5000000
RowNum = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
SomeHex12 = RIGHT(NEWID(),12)
INTO #JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
PRINT CONVERT(VARCHAR(30),GETDATE(),121)
DROP TABLE #JBMTest
PRINT CONVERT(VARCHAR(30),GETDATE(),121)
... and here's the code for the 4 smaller ones... you, of course, need to run this code in 4 different windows than the larger one...
WAITFOR TIME '20:31:10'PRINT CONVERT(VARCHAR(30),GETDATE(),121)
SELECT *
INTO #JBMTest
FROM Master.dbo.spt_Values
WHERE Type = 'P'
PRINT CONVERT(VARCHAR(30),GETDATE(),121)
SELECT TOP 3 Number
FROM #JBMTest
PRINT CONVERT(VARCHAR(30),GETDATE(),121)
DROP TABLE #JBMTest
PRINT CONVERT(VARCHAR(30),GETDATE(),121)
I used WAITFOR TIME to start the larger Select/Into. I also used WAITFOR TIME to start the smaller ones all at the same time, all 10 seconds after the larger one had started.
When I run the code, the 4 smaller ones complete before the large one does which would be impossible if there were any long term table locks on the System Tables. Just so you don't think I'm trying to snooker you, please see the following graphic and notice that the 4 smaller Select/Into's on the right have completed before the larger one completed. Also note the times that they completed.
After the long running Select/Into finishes, here's what the final screen shot looks like. Again, note the times...
Again... none of this could happen if the System Tables were locked long term by the long running or any of the other queries that use Select/Into.
To summarize, the myth that using Select/Into locks System Tables for the duration of the query was true way back in version 6.5... but it hasn't been true for a long time. It's nothing more than an old wive's tale since way back when.
If you're afraid of it, don't use it. Personally, I'm not not going to let such a valuable, high performance tool go by the wayside because of tales. Now, if you can prove that my code example is a bunch of hooey, then I'll definitely change my tune because code rules. 🙂
--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)
Intro to Tally Tables and Functions -
By the way... if you believe in paper instead of code, please read the Microsoft article at the following link...
http://support.microsoft.com/kb/153441/EN-US/
See where it says "NOTE: This problem does not apply to SQL Server 7.0 and later."
--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)
Intro to Tally Tables and Functions -
Jeff Moden (9/20/2008)
Madhivanan (9/19/2008)
Jeff Moden (9/18/2008)
Ninja's_RGR'us (9/18/2008)
Yes you need to create it first, including column definitions.Heh... not exactly true...
SELECT *
INTO #MyHead
FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who')
Yes it is [:)]
Why do you say "Yes it is"? There's no need to precreate the temp table for the code I wrote... it's created on the fly. Even you say it the very blog link you posted...
I prefer using method 2 as you dont need to create temp table and also you dont need to worry about the structure of the procedure
My reply "Yes it is" is to your reply "Heh... not exactly true..."
Actually I agreed with your reply 🙂
MadhivananFailing to plan is Planning to fail
-
do u want another permanent temparory table to be created or else u want to work out everything without any temparory table directly all operations to be done inside a stored procedure itself ...............
i dont know ur requirements exactly if it is so just create a temparory table work out and then drop it at the end.........................
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply