-2

Of course I know I can issue delete statement for each table in DB but my question is there any trick to do this automatically with one statement or not really. I have large amount of data and for testing purposes I need a shrunk version os DB. top 10 records only will be fine.

Zulu Z
  • 1,103
  • 5
  • 14
  • 27
  • 2
    `TOP 10` without any indication of how to **order** your data is pointless and undefined .... so `TOP 10` in which order?? What is the `ORDER BY` clause? – marc_s Aug 25 '14 at 12:50
  • Why is this marked as a duplicate? To remove all non-top-ten records of a table is something entirely different from deleting all non-top-ten records from a whole relational database. (However from the accepted answer it shows that this question is worded extremely incorrect and should rather be "remove random 90% of all data regardless of any relations". This is even farther from the suggested "Delete all but top n from database table in SQL".) – Thorsten Kettner Aug 25 '14 at 14:34
  • it's not duplicate question - firstly no answer on other thread to my question, secondly I don't care about specific top 10 records. It was figure of speech. 10% is good as well. – Zulu Z Aug 26 '14 at 09:45

5 Answers5

2

I have a suggestion of Deleting 90% data from your database by the using the following command, to keep 10 records you need a bit more table specific query, but if you want to delete 90% data from all the tables in your database you can use the following system stored procedure.

EXECUTE sp_MSforeachtable 'DELETE TOP (90) PERCENT FROM  ?'
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • thanks, I think that what I was looking for. Of course I understand issues with indexes, integrity etc but I need this only for dirty tests. – Zulu Z Aug 25 '14 at 13:55
  • Also I dont know myself if there is any issues with this system stored procedure but a sql server consultant (Aaron Bretrand) told me that this system stored procedure has a bad reputation for missing out tables. Well since it is only a test db you can live with some little bit mess ups :) – M.Ali Aug 25 '14 at 13:55
  • Here the solution is given to delete top 90 percent of data and keep bottom records - as per the question this will not solve the purpose to keep the top 10 records - Just to be more specific no grievances – Murtaza Aug 26 '14 at 02:06
1

As per your Question i understand that you want to delete all the records from the table and keep only top 10 records to perform some testing

thats really simple - just follow these steps 

   1.  select top 10 *  INTO #tmp_toprecords FROM  [table_name]

   2.  truncate table [table_name]

   3.  INSERT INTO [table_name] SELECT * FROM #tmp_toprecords

What i have done in above code is -

  1. selected all top 10 records from main table to a temporary table

  2. delete or truncate data from main table insert the data back to

  3. main table from temporary table.

I hope the above solution helps you to quickly get the data reduced in multiple tables and commence your testing...

Murtaza
  • 3,045
  • 2
  • 25
  • 39
  • 1
    It may sound a simple solution, but Identity Columns, FK Constraints will cause many issues with this deletions or Insertions of rows. – M.Ali Aug 25 '14 at 13:10
  • @M.Ali - Yes i do understand the Constraints - but i have given is solution as these are to be performed for dirty test with less data in the system. – Murtaza Aug 26 '14 at 02:03
0

You can try the below query.

Assuming you've got a Unique key (id), you can proceed as follows;

Delete From TableX
where  Id not in (Select Top (10) Id form TableX order by Id)
Nadeem_MK
  • 7,533
  • 7
  • 50
  • 61
0

You would need one statement per top table, i.e. a table that doesn't reference another. In COUNTRY, ADDRESS, PERSON, where each person has an address and the addresses are located in countries, the COUNTRY table would be such a top table. For each of these tables you would have to write a delete statement that defines what the top 10 records for that table are. (For countries the top 10 records could be those used in most addresses or those being first in alphabet or those being closest to Japan or whatever. You need rules to define the top records for each table.)

Then, provided all tables are related via ON DELETE CASCADE, your delete statements will remove all linked records of the dependant tables. However the dependent tables themselves can still have more than 10 records, so again, for every depending table, define the top 10, write an appropriate statement and delete. And so on.

If tables are not linked via ON DELETE CASCADE, then you would have to start from the other side, with the last-leaf records, i.e. PERSON in above example. You would have to write a (rather complex) delete statement to remove all persons except those whose address is in one of the top 10 countries and whose address itself is considered top-10. Then, if more than 10 records remain, remove those you don't consider top 10 in PERSON. Then go on with the next level (ADDRESS for instance), till at last you delete from the top tables.

So, no, there is not one statement to reduce your relational database. There is much work to do.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
0

You have to provide column_name aginst which you want to delete rows. Please use following query. In my case I used 'ID' column_name and 'delete_top_10' as table_name.

 DELETE TOP (10) 
 FROM delete_top_10
 WHERE id=1
V2Solutions - MS Team
  • 1,129
  • 6
  • 6