Menu
Possible Duplicate: How to SELECT * INTO [temp table] FROM [Stored Procedure]
I am new to T-SQL. I have a stored procedure that selects records. I want to query the records returned by the stored procedure, so I am trying to insert the records into a temporary table. (Stack Overflow posts and other posts say that is how to do it.)
But when I try, I get the error:
object or column name is missing or empty'
When I just run the stored procedure, I get a table that has columns with names.
select * into #temp1
exec alexander.dbo.get_uberrecords '20120101', '20120201', 'labcorp' //throws error saying columns must have names
but
exec alexander.dbo.get_uberrecords '20120101', '20120201', 'labcorp' // Returns cols with names
What am I missing?
459
You don't need anything that fancy, just copy and paste into your SQL editor. Use the column names, sizes, and types to construct a "Create table #x [...]" or "declare @x table [...]" statement which you can use to INSERT the results of the stored procedure.
INSERT INTO SELECT statement reads data from one table and inserts it into an existing table. Such as, if we want to copy the Location table data into a temp table using the INSERT INTO SELECT statement, we have to specify the temporary table explicitly and then insert the data.
Stored procedure in SQLTemporary procedures are stored in tempdb, and there are two types of temporary procedures: local and global. Local procedures are only visible to the current user connection, while global procedures are visible to any user after they are created.
Try creating the temp table first:
CREATE TABLE #temp1
(
COL1 INT,
COL2 VARCHAR(MAX)
)
INSERT INTO #temp1
exec alexander.dbo.get_uberrecords '20120101', '20120201', 'labcorp'
In your case of an extremely wide result set, you may want to use OPENROWSET
In any case, this SO has many options: Insert results of a stored procedure into a temporary table
58
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
