inserting into temp table.

[Bill Crawley]

Hi all,

I have declared a temp table in a Stored Proc of the form:

Declare @temp table ([user_id] char(3), prod_id numeric(15,0), version char(1), impression smallint, department char(13), sap_code char(7), ......


I now have a select statement where I want to put all the values obtained into the @temp table. How do I do that. I thought I could get away with:

select......
INTO @temp
where......

When the select statement outputs in the format of the @temp table. but SQL doesn't like it.

I then thought I could get away with
Insert into @temp Values (select.......)

But it still complained. I'm using SQLServer 2005. I need to get the stuff into a temp table so that I can do more on it later in the SP. I'm trying to avoid using a cursor.

Thanks

[Shuja Ali]

You don't have to use Values when you are using a subquery inside the Insert statement. It should be something like this Insert Into @TEMP Select FIELD1, FIELD2, ... FROM TABLE1

[Bill Crawley]

I've droped my explicit table declaration and instead done


Select.....
into #temp
where ......


Done my stuff and then did a

Select from the #temp table before doing a drop #temp.

I was concerned that droping the table in an SP straight after the select from #temp would cause the output to be lost going back to the user class, but it seems I was wrong. the final select statement still gets returned by the sp. so problem solved.

Thanks as always Shuja

[Shuja Ali]

The only problem that occurs with global temporary tables(the one that you have created in your stored procedure #temp) is that when multiple users try to access the SP at the same time, the chances are that it might fail with an error saying that £temp already exists. Also Select Into statement are slower than Insert Into. So I would suggest that you go with your first approach and use the solution that I have provided.