0

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?

nquincampoix
  • 508
  • 1
  • 4
  • 17
IWriteApps
  • 973
  • 1
  • 13
  • 30

1 Answers1

1

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
  • 38,138
  • 7
  • 87
  • 101
  • 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 :) – IWriteApps Oct 20 '10 at 16:57
  • @Gio ahh.. well I added another possibility to my answer. Also, you didn't mention if it was in a loop or not? – Fosco Oct 20 '10 at 17:41
  • oh yeah sorry, not in a loop. – IWriteApps Oct 20 '10 at 17:49
  • 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 Oct 20 '10 at 19:28
  • @HLGEM. I question your comment. I think the resulting col data are completley predictable and cobntrollable and furthermore, I would argue that using a SELECT INTO may be more resilient to table data type changes. – Chad May 27 '11 at 12:11