delete rows with a condition from all tables

Question

I am having 100 plus tables with millions of rows and having ID column and other columns too in 1 database on dev server ,

i will have to delete all rows from all tables except id 3000( which i will have to keep data in tables)

...

use xyz

delete from table1 where ID<>3000

i know i can use this for each table ,shrink the log files  and then delete another table and so on ...

i cannot use TRUNCATE as i am having a  where condition ...

can any one please suggest me a better way to do this ..delete all tables data where id<>3000 and should take of log files in parallell

Thanks

Answer 1

>> I have 100 plus tables with millions of rows and having ID column and other columns too in 1 database on dev server << 

This is insane. There is no magical, universal “id” in RDBMS. Each table should model a set of one and only one kind of entity or relationship. That means each key in each table is totally different kind of attribute.  

But in your magic world an “id” can be a squid, an automobile, Lady Gaga or anything in the universe! What you have done is create a physical locator or fake record number to re-invent sequential file processing! Google “Kabbalah Numbers”; this is a form of Jewish magic that believes that god put a magic on everything and if you do certain magic things with these numbers, you can have powers over them. 

You need to start over.  

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 2

An alternative to do this, you can easily use the truncate command and no need to clear the log files.

create table table1 (id int , accountdate datetime)
insert into table1 (id, accountdate)
select 3000,'6/12/2012' union
select 1234,'6/13/2012' union
select 5678,'6/14/2012'
 
select * into #temp from table1
where id=3000
 
truncate table table1
 
insert into table1
select * from  #temp 
  
drop table #temp

Please mark as answer if works for you.

Thanks

---

Amar Deep Singh

Answer 3

If you are sure that all tables have an id column and the column name is always ID then you can do this:

exec sp_msforeachtable 'delete from ? where ID <> 3000'

---

m@te