Copying Data returned by a stored procedure....Help please!

Question

Copying Data returned by a stored procedure....Help please!

Senthuran Subramaniam

SSC Veteran

Points: 299
More actions
December 3, 2007 at 9:42 am

#76835

Hi all,
I have a stored procedure that has in it a SELECT statement. This statement returns values from as many as 4 tables after filetring data. I want to get the result set into a Temp Table and then work with this table. When i tried inserting the stored procedure's result into a Temp Table created on the fly, i get the following error
Msg 263, Level 16, State 1, Line 1
Must specify table to select from.
Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
The SQL Statement that i used is as follows
SELECT * INTO
#TempTable
EXEC get_RptStaticPoolCopy1 'AA'
GO
I also tried getting the results into a normal table but that didnt work either. Kindly let me know if there is any other info that i could provide. Thanks for your help
Senthuran

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Judging by the error message, I'd assume the proc you're calling has an unnamed column. Procs can have those. Try running the proc separately and see if one of the columns comes up that way.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Senthuran Subramaniam SSC Veteran Points: 299 More actions · Answer 2

Thanks for the reply GSquared. I tried running the proc separately and it works fine. All the columns have proper names and nothing is left null.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

Can't be done using EXEC... temp table must already exist.

--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: 1004329 More actions · Answer 4

Ummm... maybe you can do it using OPENDATASOURCE or OPENROWSET...

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

Senthuran Subramaniam SSC Veteran Points: 299 More actions · Answer 5

Senthuran Subramaniam

SSC Veteran

Points: 299

December 3, 2007 at 8:15 pm

#756767

Thanks Jeff...i'll try that.

Genie Cool SSC-Addicted Points: 478 More actions · Answer 6

Or May be you can put the values into temp variables and copy them into the temp table. This would also work

Regards,
Genie Cool

Sagesh SSC Enthusiast Points: 160 More actions · Answer 7

Declare a Table variable (INPUT/OUTPUT) and pass it into the store procedure(get_RptStaticPoolCopy1 ) .In the storeprocedure(get_RptStaticPoolCopy1 ) you can fill the table variable which you are getting as a parameter .After filling it return it into your calling procedure and fill it into temptable.

Eg:

EXEC get_RptStaticPoolCopy1 'AA',@TableVariable

SELECT * INTO

#TempTable

FROM

@TableVariable

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

Have you actually tried that and gotten it to work?

Besides, if you're gonna take the time to define a table variable, you might just skip that step and define the Temp table instead.

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

SoCal_DBD Hall of Fame Points: 3381 More actions · Answer 9

How about this:

-- Create a dummy proc

create procedure GetYears as

select 1, 2005, '10/1/2005'

union

select 2, 2006, '10/1/2006'

union

select 3, 2007, '10/1/2007'

go

-- Create a temp table to receive the results of proc

create table #Years

(AssessmentPeriodID int,

AssessmentYear int,

EmployedAsOf datetime)

-- Insert proc results into the temp table

insert into #Years

exec GetYears

-- Display results from temp table

select * from #Years

Lisa

Senthuran Subramaniam SSC Veteran Points: 299 More actions · Answer 10

Thanks Sugesh, Jeff , geniecool and LSadvantage.....i'll try ur solutions and let u know....Thanks a lot for ur time

Sagesh SSC Enthusiast Points: 160 More actions · Answer 11

Sagesh

SSC Enthusiast

Points: 160

December 5, 2007 at 10:59 pm

#757517

Table variable is faster than Temp Table

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

No, Not true. Table variables are NOT faster than TempTables. Are you saying that because you think table variables live only in memory or because you think temp tables always cause recompiles?

Temp tables live in memory, just like table variables... if either overrun memory, they both spool to TempDB. But don't take my word for it... pay particular attention to the 2nd bullet of A3 and all of A4 in the following...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

Tables variables also have the nasty habit of showing up in the execution plan as a single row and that what the optimizer works with.

And, you can avoid midstream recompiles on set based batch jobs by creating the temp tables at the begining of the proc before any DML occurs, just like it says in Books Online.

It's a myth that table variables are always faster than temp tables... sometimes they are and sometimes they're not... it usually depends on how many rows you're working with.

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

Sagesh SSC Enthusiast Points: 160 More actions · Answer 13

Thanks for your information.

I found, when working with Table variables it's faster than Temp table.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 14

Yeah, I've seen that... but usually for smaller sets of rows.

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