Re: Creating temp table in SQL Server

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

Hi Denise
   all_objects is a database view (Oracle). It lists everything in your
user_objects view , plus objects that u have access to. Go to sqlplus and
type desc all_objects , this will give u the columns that the view
contains(object_name, object_type, owner etc..).
U can check to see if the table that u created exists in the database as
follows
  select  'yes' into v_exists from
   all_objects where object_name = <<tablename>> and
   object_type = 'TABLE' and
   owner = <<owner of the tavle>>.

Based on this select u can determine if the table exists in the db. If
v_exists = 'Yes' then drop the table and continue with the create.
Bharathi


> -----Original Message-----
> From: White, Denise [SMTP:DWHITE@SYSTEMS.TEXTRON.COM]
> Sent: Monday, October 25, 1999 9:02 AM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Re: Creating temp table in SQL Server
>
> Bharati,
>
> What is 'check all_objects'?  I tried looking up a 'check' command and an
> 'all_objects' keyword in every SQR reference I could think of, and
> couldn't
> find either of them.  Is it specific to SQL Server?  We are Oracle.
>
> Also, thanks to those who pointed out that the ON-ERROR clauses I
> specified
> (WARN & SKIP) can only be used in the BEGIN-SETUP section.  I knew there
> was
> a reason I was doing the drop in two different places!
>
> Denise White
> Sr. IT Application Developer
> Textron Systems
>
> > -----Original Message-----
> > From: Automatic digest processor [SMTP:LISTSERV@list.iex.net]
> > Sent: Friday, October 22, 1999 2:03 AM
> > To:   Recipients of SQR-USERS digests
> > Subject:      SQR-USERS Digest - 21 Oct 1999 to 22 Oct 1999
> >
> > Date:    Fri, 22 Oct 1999 08:43:24 -0400
> > From:    "Mani, Bharati" <Bharati_Mani@WRIGHTEXPRESS.COM>
> > Subject: Re: Creating temp table in SQL Server
> >
> > 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
> > > > ------------------------------