views:

39

answers:

1

I'm working in SQL Server 2008, and I'm trying to select into a temp table based on a certain condition...for a report, I need up to 18% of the records to be of a certain product type.

if ((@totalRecords * .18) > @productTypeCount)
    select * into #tmpLP_REIT
    from myTable where productType = @productType
else
    select top 18 percent * into #tmpLP_REIT
    from myTable where productType = @productType

I keep getting error "there is already an object named '#tmpLP_REIT' in the database" even though I know it isn't there because when I try to drop such table, it points out it doesn't exist. I also get this error on the else clause.

It seems it's ignoring my if statement and going right through creating the first table, then trying to create it again. any suggestions?

Please and thanks in advanced.

+1  A: 

Is this code in a loop?.. that would explain the error if it were happening the second time though. If not, the syntax checker must just be choking because it sees 2 queries creating the same table, not realizing it is a conditional.

One solution would be to create the table first, and then change the syntax of those 2 queries to insert into queries.

Another solution would be to use dynamic SQL... build the right query in the conditional, and then execute it afterwards. This should get passed the validation you're currently triggering.

Fosco
The reason I was trying to avoid this was because this is only the first of a number of tables I'll end up creating...rather sizable tables, so select into would cut a lot of code and effort :)
Gio
@Gio ahh.. well I added another possibility to my answer. Also, you didn't mention if it was in a loop or not?
Fosco
oh yeah sorry, not in a loop.
Gio
Sorry these really are your only options. It is a bad practice to use selct into anyway as you have no control over datatypes.
HLGEM