Hi Richard,
When you create the #Table in a BEGIN-SQL paragraph, the database drops that table at the end of that paragraph. From what I understand, there is no noticeable problem in performance to just create a "permanent" table, use it and then drop it before end of execution. The only problem would come if the program died from some error before it dropped the table. Then you would need to manually drop the table to avoid problems the next time you run the program.
HTH,
Bob
-----Original Message-----
From: Shaver, Richard H [mailto:richard.h.shaver@LMCO.COM]
Sent: Tuesday, October 19, 1999 9:25 AM
To: Multiple recipients of list SQR-USERS
Subject: Creating temp table in SQL Server
I can't seem to find the right syntax to create a temporary table in SQL
Server using SQR. SQL Server builds temporary tables when the table name is
preceded by a #. It is simple enough to do within the database using the
native language (i.e. CREATE #TEMP (FIELD TYPE), but when I try to use the
same syntax in SQR I receive the "COUNT field incorrect" error. If I put
brackets around the table name, i.e. CREATE [#TEMP], SQR returns the
"incorrect syntax near [" error. (I got the idea for the brackets from the
SQR Users group page, but it was in regards to Informix.)
I have tried the Create command and the Select * Into command with the same
results.
begin-program
begin-sql
create table #rick (test char(4))
end-sql
end-program
begin-program
begin-sql
select * into [#rick] from srsch
end-sql
end-program
The same SQR syntax creates a permanent table if I remove the # from in
front of the table name, but that sort of defeats the purpose. Any ideas??
tia
Rick Shaver
LM Data Systems
1-360.396.8488