Deleting query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Deleting query

Author

Topic

Nesu
Starting Member

15 Posts

Posted - 2008-02-15 : 07:13:22

I've created a programme that creates a number of tables that are, unfortuantly, need for the whole programme to run.
Once its reached the end, and the programme has given me the output table, is there anyway I can specify in the query to delete these superfluous tables as I'm going to make the query loop and so recreate the tables but with different data in them each time.
Thanks for any help you can provide.
Nesu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-15 : 07:22:54

Didnt understand your scenario. you can delete table using statement
DROP TABLE <tablename>
but really didnt understand why you want to delete and then create table. Can you explain scenario?

Nesu
Starting Member

15 Posts

Posted - 2008-02-15 : 07:49:58

The issue I have is that I have 1600 different categories on which this needs to run. Rather then create 1600 * 25 tables I would rather just loop the programme so that it creates generic tables which once its finsihed and provided me with the relevently named output, will then delete the generic tables, or empty them, and start the whole process over again but for the next category on the input list.
The only table that is kept is the output file.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 07:55:40

If you want my opinion, this is a terrible design.
Why don't you create a new column in the table with "CategoryID" or simmilar?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-15 : 07:55:47

ok. this can be done in two ways. one by trucating table after each processing in loop or by placing a field designating category inside table and simple populating each time . we can distinguish each categories data by means of this category field.

TRUNCATE TABLE <table name>

or

INSERT INTO #processingtable (fields,Category)
SELECT fields,..,<catefgoryname>
FROM....

then use, SELECT * FROM #processingtable where Category=<current category>
to get recent category's data.

Nesu
Starting Member

15 Posts

Posted - 2008-02-15 : 08:12:46

Peso, I'm sure this is a terrible design. Thing is, I'm not an expert on this. All I've done Microsofts QUerying SQL Server 2000 with Transact-SQL course. The book and training didn't cover this, nor was it expected that this would be needed but things have changed and I now need to produce it.

I have provided the code I'm working on for a test sample which give the correct number for 2002. I have repeat this for 2003-2006 as well but thats no great chore.

What I need is someway to ensure that all the rules are followed in the program. By then making this step through for each code, it saves me having to edit by hand so many times. I'm sure there is a more efficient design, but this what I came up with. I just want to know if its possible to delete the all the created tables except the last one, g40num.

select * into test from notes
where code = 'G40'

update test
set dereg = '2002-12-31 00:00:00.000'
where dereg is null

select pNo, Age, Sex, DateofBirth, Reg, DeReg, dateevent, code into test1 from test
group by pNo, Age, Sex, DateofBirth, Reg, DeReg, dateevent, code

select distinct pno into testbefore2002 from test1
where dateevent < '2002-01-01 00:00:00.000'

delete from test1
from events.dbo.test1 as a
inner join testbefore2002 as b on a.pno = b.pno
where a.pno = b.pno

select distinct pno into testafter2002 from test1
where dateevent > '2002-12-31 00:00:00.000'

delete from test1
from events.dbo.test1 as a
inner join testafter2002 as b on a.pno = b.pno
where a.pno = b.pno

select pno into testeventbeforereg from test1
where dateevent < reg

delete from test1
from events.dbo.test1 as a
inner join testeventbeforereg as b on a.pno = b.pno
where a.pno = b.pno

select pno into testderegbeforeevent from test1
where dereg < dateevent

delete from test1
from events.dbo.test1 as a
inner join testderegbeforeevent as b on a.pno = b.pno
where a.pno = b.pno

select distinct pno into g40num from testepilepsy1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources