tatman blue


I'm baffled by this error but I'm guessing once someone points it out, it will a oh duh! moment. I have a proc. I want to get the results of the proc into a temp table. I've tried both SELECT INTO and INSERT INTO. Both give me the error "Invalid object name #HACK" which is the tmp table name I've used.

CREATE PROCEDURE tmp_weed

AS

SELECT * FROM Invoice WHERE InvoiceID = 10007

GO

INSERT INTO #HACK

EXEC tmp_weed

Invalid object name '#HACK'.

hope someone can point out the obvious...thnx

Matt




Re: INSERT INTO woes

ShawnNWF


My first guess would be that you haven't defined a temp table named #HACK yet. In the full script, did you perform an operation like:

Code Snippet

CREATE TABLE #HACK (

Column1 Datatype,

Column2 Datatype,

...
)

If you already have that built in the script, could you post the entire thing so we can take a look





Re: INSERT INTO woes

tatman blue

No I didnt create the table. I was hoping that SQL Server would just create it on the fly based the output of the proc. I was "hoping" for a generic way to play around with stored proc output on as needed basis w/o having to necessarily know what the output was before hand.

Creating the temp table first does work however....






Re: INSERT INTO woes

Tom Phillips

Nope. You have to create the table in EXACTLY the format of the return set, otherwise it will fail.

There is no "easy" way to do what you want without knowiing the result set.




Re: INSERT INTO woes

Hugh Qu - MSFT

INSERT INTO requires the table to pre-exist. On the other hand, SELECT INTO does create a new table, but I don't think it can take the output of a SP. It needs to be of this format:

SELECT <column list>

INTO <new table>

FROM <data source>

WHERE ...

The <data source> can be a table or view, but can't be "EXEC <sp>". If you need to abstract out things like using parameters, you could use a Table-Valued-Function.

HTH.






Re: INSERT INTO woes

rusag2

Of course there is this:

The stored proc can create a "global temp" table which persists in a larger scope than a "#" table.

Read from the "##" table into the "#" table. then explicity drop the ## table.

This is horrible practice...but in the real world...sometimes quick is the only criteria.

Code Snippet

CREATE PROCEDURE tmp_weed

AS

SELECT * into ##HACK_SUB1 FROM Invoice WHERE InvoiceID = 10007

GO

exec tmp_Weed;

select * into #HACK from ##HACK_SUB1

drop table ##HACK_SUB1

select * from #HACK






Re: INSERT INTO woes

rusag2

I dont' know how close your example is to what you really want....

Code Snippet

Create PROCEDURE tmp_weed

AS

SELECT * FROM Invoice WHERE InvoiceID = 10007

GO

--This will create the temp table's

--structure, based on the Invoice table

select top 0 * into #Hack from Invoice where 1 = 2

insert into #Hack

exec tmp_Weed

select * from #Hack






Re: INSERT INTO woes

Louis Davidson

Well, one thing you shouldn't be doing is using SELECT * in production code. Instead, explicitly name the columns. When tables change, you should make the changes to your code, as this is a far better way to maintain your code and expect things to go wrong on a table change, not just have them happen to you without your knowledge.

And yes, you are going to have to create the #hack table before running your query, and you will need to know the interface of the procedure's data set to do this. A trick to do this is to do:

SELECT * into tempdb..tableName FROM Invoice

and then script the table.

However, if you are just trying to get one row to work with in a different query, consider changing your procedure:

CREATE PROCEDURE tmp_weed

AS

SELECT * FROM Invoice WHERE InvoiceID = 10007

GO

Into a function:

create function dbo.invoice$getFunction
(
@invoiceId int
)
returns table
as
return(
select invoiceId
from invoice
where invoiceId = @invoiceId)
go

Now, if you must use a temp table, you can use select into:

select invoiceId

into #hack

from dbo.invoice$getFunction(10007)

Or just use this function instead of the temp table:

select columnList

from table1

join dbo.invoice$getFunction(10007) as notAHack

on notAHack.invoiceId = table1.invoiceId






Re: INSERT INTO woes

tatman blue

Thnx. All of these are good answers.

This question wasn't for production code. I needed to do some "data mining" to understand a defect and just thought there might be a easier way than I was approaching it.

I would be neat if there was a generic way to convert proc output into tables without having to mod the procs.....*hints* Smile

M