temp table creation in a select query

[nikhil bajaj]

Hi, 

I am trying to create a temp table by inserting data to it using a select query. The code will only create a table if there is no where clause in the select query which seems strange. Can anyone help? 

In the below code, when the red statement is removed, it works fine but when added, it does not create any temp table. I checked that where clause does return data. I tested that using a query fixture. 

!|Execute|!-

Select top 1 detail_id

into #temp

from AdvisoryData_Fiserv..hst_bil_detail 

where qtrend_date = @Qtrenddate

-!|

[nikhil bajaj]

Any help on this will be appreciated. 

[Mark Matten]

I'm guessing you're using SQL Server.

Are you providing a DbFit parameter value for @Qtrenddate ?

Do you see any exceptions occuring?

[Yavor Nikolov]

I am not sure whether this type of statement supports bind variables.

E.g. in Oracle 'create table ... as select ...  ' does not.

A workaround might be to create a stored procedure doing that work and to call it.

--
You received this message because you are subscribed to the Google Groups "dbfit" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dbfit/55ff2d0b-5c2f-45fe-9f47-218b528a0b54n%40googlegroups.com.

[Yavor Nikolov]

Another approach is to just split it into two parts:

1) create the (temp) table

2) use insert to populate it - here you can use bind variables