Bulk records deletion

[ranj_s20]

Hi,

Can any one suggest the method to delete bulk records upto 1 lakh without specifying any condition? There's no Id to be specified in checking in the where condition. Please suggest an idea .

Thanks In advance

[pritaeas]

Can you please explain with your table definition and a small example what it is that you want to achieve ?

[ranj_s20]

Hi,

I just want to delete the old data of datbase. Hence i need to delete about 1 lakh records from different tables. Hence i need the query to delete the records.

[pritaeas]

DELETE FROM tablename

But wouldn't it be easier to just drop everything and recreate all tables ?

[ranj_s20]

No yaar i want to delete the first 1 lakh record,how should i get the count,rather to delete this much record time out will show on executing query

[pritaeas]

See this thread on how to do it. If you omit the where in the sub-query, then the natural order of the table will be used.

DELETE FROM tablename WHERE id IN (SELECT TOP 5 id FROM tablename)

[Netcode]

You need am ID to specify a WHERE condition. Else, you may end up losing the whole data in the table. In order to remove the data from the table while retaining table structure, do this:

TRUNCATE TABLENAME

but you can delete a selected number from the table just as specified below:

DELETE TOP 1000 [ID]
      ,[Reference_Number]
      ,[FirstName]
      ,[LastName]
  FROM [HumanResource].[dbo].[Employees]