Delete data from SQL Tables

Question

I have 10 tables , say Table_1,Table_2,Table_3,Table_4 ...,Table_10.. I have to delete data from all the tables except Table_4. Is there any single line query to do it.(Using 'LIKE','IN' etc) "Delete * from tablename like Table_ where Table NOT IN ('Table_4')"..

What database? I haven't thought it through yet, but you might be able to get the table names from the schema and then do... something with them. — cha0site, Jan 18 '12 at 11:59

score 1 · Answer 1 · answered Jan 18 '12 at 12:07

1

If the number of tables exceeds 10, you don't want to list all tables in the delete statement. You should stick to the catalog and use a cursor:

declare @table nvarchar(max)
delcare @cur cursor

set @cur = cursor fast_forward for
  select name
  from sys.tables
  where name like 'Table_%'
  and name not like 'Table_4'

open @cur
fetch next from @cur into @table

while(@@fetch_status = 0)
begin
  sp_executesql 'DELETE FROM ' + @table

  fetch next from @cur into @table

end
close @cur
deallocate @cur

Edit: this answer is for MS SQL only :)

answered Jan 18 '12 at 12:07

Matten

17,365
2
42
64

It's not your's "**fault**" but it seems that writing the tables names is better... Less writing, More Readability, Works with every DB. =) – gdoron Jan 18 '12 at 12:19
And if there are 10000 tables? :-) – Matten Jan 18 '12 at 12:27
1

So you would have a lot more problems then delete their data... **lol** – gdoron Jan 18 '12 at 12:29

score 0 · Answer 2 · answered Dec 08 '16 at 10:26

use [db_name]

declare @sql nvarchar(max)

select @SQL = 
(select ';
DELETE FROM ' + quotename(TABLE_SCHEMA) + '.' + 
quotename(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
and TABLE_NAME not in ('mytab1', 'mytab2')

ORDER BY Table_Schema, TABLE_NAME
FOR XML PATH(''), type).value ('.','nvarchar(max)')

print @SQL -- verify

It will create query for delete and use this query delete the table u required and skip u not required.

score -1 · Answer 3 · answered Jan 18 '12 at 12:08

-1

write a delete query with out Table_4

delete from Table_1,Table_2,Table_3,Table_5,......Table10

answered Jan 18 '12 at 12:08

Nighil

4,099
7
30
56

what if you have thousands of tables?? will you keep on writing it manually? – Fahim Parkar Jan 18 '12 at 16:25
here there is only ten tables so i suggested – Nighil Jan 18 '12 at 18:03
if I say I have 1000 tables and say what is your suggestion? – Fahim Parkar Jan 19 '12 at 03:09
must use a T SQL for that purpose – Nighil Jan 19 '12 at 04:28

Delete data from SQL Tables

3 Answers3