Re: Creating temp table in SQL Server

["Mani, Bharati" <Bharati_Mani@WRIGHTEXPRESS.COM>]

At the begining of the program why don't you check all_objects to see if the
table exists. If it does then drop it and continue with the create otherwise
just create it

> -----Original Message-----
> From: White, Denise [SMTP:DWHITE@SYSTEMS.TEXTRON.COM]
> Sent: Thursday, October 21, 1999 5:08 PM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Re: Creating temp table in SQL Server
>
> We are Oracle, not SQL Server, but I would create a 'permanent' table at
> the
> beginning of the program and then drop it at the end.  As Bob notes, the
> program could abort before it reaches the drop, leaving the table in place
> and causing an error next time the job is run and tries to do the create.
> Good programming practice (at least, as I was taught) is to do a drop at
> the
> BEGINNING of the program (before the create), as well as at the end (you
> can
> just set up one procedure and call it from both places, if you want; I do
> separate drops so I can handle errors differently - see next statement).
> The BEGIN-SQL needs to have ON-ERROR=WARN, so that under normal
> circumstances (when the table has been dropped correctly in the previous
> run) the non-existence of the table will not cause the program to abort.
> You may need to warn your users so they don't call you when they see the
> warning message!  You can also use ON-ERROR=SKIP, which will not produce a
> warning message, but I like to be able to see if some other message shows
> up, indicating a real problem.
>
> Denise White
> Sr. IT Application Developer
> Textron Systems
> > ------------------------------
> >
> > Date:    Tue, 19 Oct 1999 09:32:47 -0400
> > From:    Bob Buford-Abba Systems <bbuford@GALEIND.COM>
> > Subject: Re: Creating temp table in SQL Server
> >
> > 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
> >