how to reliably delete (or drop) tables

Hi folks,
I need to create three tables on the fly that change once a w (they are
frozen crosstabs) and I've decided to create them as report tables for sheer
speed.
I obviously want to create these tables in the database and not in temp and
I have complete permissions when running any code on the server
how can I reliably test for a table and either drop it or clear it only to
recreate it instantly.
I ask because I was getting plenty of errors when I did this the other day
'invalid object name etc..' 'table already exists'
should I use Object_id or the msys tables...
a little code snippet of how you do it would be very helpful
also, is it impossible to put GO between BEGIN and END or even in the body
of a sproc?
many thanks in andvance
CharlesAHere are 2 ways
if exists (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE'
AND TABLE_NAME = ?)
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TableName]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
http://sqlservercode.blogspot.com/|||DECLARE @.o INT;
SET @.o = OBJECT_ID('dbo.tablename');
IF @.o IS NOT NULL AND OBJECTPROPERTY(@.o, 'IsUserTable') = 1
BEGIN
PRINT 'dropping dbo.tablename';
DROP TABLE dbo.tablename;
END;
CREATE TABLE dbo.tablename
(
..
);
"CharlesA" <CharlesA@.discussions.microsoft.com> wrote in message
news:46529FC1-68AA-4425-A6F8-A84B79A5BFC5@.microsoft.com...
> Hi folks,
> I need to create three tables on the fly that change once a w (they are
> frozen crosstabs) and I've decided to create them as report tables for
> sheer
> speed.
> I obviously want to create these tables in the database and not in temp
> and
> I have complete permissions when running any code on the server
> how can I reliably test for a table and either drop it or clear it only to
> recreate it instantly.
> I ask because I was getting plenty of errors when I did this the other day
> 'invalid object name etc..' 'table already exists'
> should I use Object_id or the msys tables...
> a little code snippet of how you do it would be very helpful
>
> also, is it impossible to put GO between BEGIN and END or even in the body
> of a sproc?
> many thanks in andvance
> CharlesA|||Judging from your post you may have several issues but here goes:

> how can I reliably test for a table and either drop it or clear it only to
> recreate it instantly.
> I ask because I was getting plenty of errors when I did this the other day
> 'invalid object name etc..' 'table already exists'
Create a regular base table. A permanent one.

> should I use Object_id or the msys tables...
if object_id('<owner>.<table>') is not null
begin
print 'exists'
end
else
begin
print 'does not exist'
end

> also, is it impossible to put GO between BEGIN and END or even in the body
> of a sproc?
It's possible ;) but it would be syntactically incorrect.
Why would you think you need to create object dynamically? Please elaborate.
ML
http://milambda.blogspot.com/|||for ur first part let assume that the table name is --[mytable] ur
code should be...
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[mytable]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo.mytable]
else
print 'Table not present'
u can remove else part to not print message.....
i think it will help..
ya i think it is not possible to put GO between BEGIN and END...|||hi ML,
I'm answering your question, 'why do you need to create the tables
dynamically'
because
it's a crosstab and it's a moving window on months of the year (12 at a
time) so the columns change once a month and I wish to automate the process
of creating them. It's also a read-only reporting table and not a
transactional or updatable table.
so my plan is to create the table dynamically and then to use dynamic SQL to
populate it, that bit is working already, I had incomprehensible (to me)
errors when I tried to delete and re-create these types of reporting tables
recently
Regards
CharlesA|||:-)
CharlesA|||If it's a 12 month window, then why not just do:
CREATE TABLE dbo.ReportingTable
(
DataPoint VARCHAR(32),
m1 INT,
m2 INT,
m3 INT,
m4 INT,
m5 INT,
m6 INT,
m7 INT,
m8 INT,
m9 INT,
m10 INT,
m11 INT,
m12 INT
)
GO
-- in other words, there is no reason to store '2005-01-01'
-- as a column name. This is data, not metadata.
CREATE TABLE dbo.ReportingStartMonth
(
dt SMALLDATETIME
)
GO
Now, have your starting month stored in the latter table, and the reporting
app can figure out column headers based on that (e.g. month1 =
DATEADD(MONTH, ReportingStartMonth.dt, 0), month2 = DATEADD(MONTH,
ReportingStartMonth.dt, 1), etc).
Or, better yet, store your data in a regular table, however this will
require more changes to the app:
CREATE PROCEDURE dbo.BetterReportingTable
(
Month SMALLDATETIME,
DataPoint1 INT,
DataPoint2 INT
-- , ... etc etc
)
Now you can delete data based on the time range, so you have 12 rows instead
of 12 columns, and also only process one month at a time (because it is
unlikely that data from 11 months ago really needs to be re-generated every
month). The query that retrieves this data can easily pivot it if need be,
but in my experience, most reports are going to show the data in that format
anyway, or can easily work with that format when developing the actual
display. Remember that the table structure does not have to imitate the
physical display for the end user...
Both of these solutions do not require dropping and re-creating the table;
the first is a mere truncate, the latter only requires deletes *if you need
them*... the latter also allows you to keep around years of history if you
need to, and re-create reports at will. The main reportng query can still
be rigged to only get the last 12 months...
A
"CharlesA" <CharlesA@.discussions.microsoft.com> wrote in message
news:FF70F337-5342-4182-AE7A-54E6B7A67B7D@.microsoft.com...
> hi ML,
> I'm answering your question, 'why do you need to create the tables
> dynamically'
> because
> it's a crosstab and it's a moving window on months of the year (12 at a
> time) so the columns change once a month and I wish to automate the
> process
> of creating them. It's also a read-only reporting table and not a
> transactional or updatable table.
> so my plan is to create the table dynamically and then to use dynamic SQL
> to
> populate it, that bit is working already, I had incomprehensible (to me)
> errors when I tried to delete and re-create these types of reporting
> tables
> recently
> Regards
> CharlesA
>|||I for one wouldn't mind seeing some DDL. It sounds like a job for an indexed
view. But I could be wrong.
ML
http://milambda.blogspot.com/|||Thanks Aaron,
your suggestions sound quite sane and well worth following up.
I suppose the purpose behind my hack, was the very underperformant Access
crosstab that was working with a smallish dataset (brought back via a
passthrough query), and I wanted to fix a problem under great deadline
pressure.
your suggestion that the table should not be dropped and recreated is of
course the one to follow
thanks again
regards
Charles Asql