Advertisements
Home > SQL > How to delete large amount of rows from table in SQL

How to delete large amount of rows from table in SQL


How to delete huge amount of data from a table in SQL

In some scenario we have to delete large amount of rows from sql table and it will going to timeout if the table has very large amount of rows (Some tables in the database has more than crore rows). In this scenario we need to delete some small amount of records and from the table and continue the process until all records in the table deleted.

Query for recursive deletion from the table in SQL 

Below codes delete 50000 rows recursively untill all records int the table deleted. So it never meets the timeout exception and will complete execution faster than normal query

WHILE exists (
SELECT * FROM myTable WHERE name like ‘%ab%’
 )
DELETE TOP (50000) scanned_cont_dtls WHERE name like ‘%ab%’;
Advertisements
  1. February 3, 2014 at 8:55 am

    Your style is unique compared to other people I have read stuff from.
    Many thanks for posting when you have the opportunity, Guess I will just bookmark this
    blog.

  2. February 3, 2014 at 11:48 am

    Heya i’m for the first time here. I found this board and I find It truly useful & it helped me out much.
    I hope to give something back and aid others like you helped me.

  3. February 3, 2014 at 5:03 pm

    In its infancy, network marketing was a much more
    difficult business to run. Your company will carry logos of these bodies
    in their websites and brochures if they are members.
    A good example of this is the diet cookies that were popular at one
    time.

  4. February 13, 2014 at 11:04 pm

    Greetings! Very useful advice in this particular post!

    It is the little changes that will make the largest changes.

    Thanks for sharing!

  5. February 14, 2014 at 5:26 am

    I leave a response whenever I like a article on a website or I have something to contribute to the conversation.
    It is triggered by the sincerness displayed in the article I read.
    And after this post How to delete large amount of rows from
    table in SQL | Tuvian : Discussion forum for
    ASP.Net,C#,Ajax,JQuery,JavaScript,HTML,CSS… I was actually moved enough to drop a
    commenta response 😛 I do have 2 questions for you if it’s okay.
    Is it simply me or does it seem like some of the responses come across like they are coming
    from brain dead folks? 😛 And, if you are writing at other online sites, I’d like
    to keep up with everything fresh you have to post.

    Would you list the complete urls of all your social sites
    like your linkedin profile, Facebook page or twitter feed?

  6. February 14, 2014 at 6:09 am

    whoah this weblog is wonderful i really like studying your posts.
    Stay up the good work! You know, many people are hunting around for this
    information, you can help them greatly.

  7. February 26, 2014 at 12:50 am

    Use HTML tags to denote an important title or place emphasis on key paragraphs.
    The Internet Marketing Company job demands a 24*7 working willingness.
    Some search terms might only produce a couple, 10 or
    20 searches a day, and other search terms might have several thousand a day.

  8. March 2, 2014 at 8:12 pm

    There is significant profit available to app creators on the Apple store, if developers can tap into the
    market. The Apple app store is famous for containing a large range of different apps,
    many of which are simple, but make profit despite this.
    Popularity of IPhone applications span form everyday use apps to
    business and corporate applications.

  9. March 22, 2014 at 12:49 am

    Hello! I know this is somewhat off topic but I was wondering which blog platform are you using for this site?
    I’m getting sick and tired of WordPress because I’ve had
    issues with hackers and I’m looking at options for another platform.

    I would be awesome if you could point me in the direction of a good platform.

  10. April 1, 2014 at 2:34 am

    I am chris and i make money online playing
    games!!! http://make-money-playing-games.com

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: