none
SQL - How to delete multiple rows from table RRS feed

  • Question

  • Hi,

    I have a SQL table with a column called id. I have a list with 170k ids in it. Wat is the best way to delete all this rows using a stored procedure?

    Regards,

    Ben

    Wednesday, August 28, 2019 8:52 AM

Answers

  • One of the known old methods is passing a comma-separated string with your IDs. Then the stored procedure will have to split it and somehow use in WHERE condition.

    Next method is building a simple XML that includes elements with your values. Then the stored procedure can use XML features of MS SQL.

    Another method is to place all of your IDs into DataTable and use a Table-Valued parameter: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters#passing. You will have to declare a new type in SQL using CREATE TYPE.

    But, the modern SQL includes a useful function — STRING_SPLIT. In C# you can merge the values using

       string s = string.Join(“,”, myList).

    Then pass it to a parameter of varchar(max) type.

    In SQL, do this:

    delete from MyTable

    where Id in (select value from STRING_SPLIT(@myparam, ','))

     

    If the number of values is enormous, you can perform several calls.

    • Edited by Viorel_MVP Wednesday, August 28, 2019 9:31 AM
    • Marked as answer by loftty Wednesday, August 28, 2019 3:53 PM
    Wednesday, August 28, 2019 9:28 AM

All replies

  • One of the known old methods is passing a comma-separated string with your IDs. Then the stored procedure will have to split it and somehow use in WHERE condition.

    Next method is building a simple XML that includes elements with your values. Then the stored procedure can use XML features of MS SQL.

    Another method is to place all of your IDs into DataTable and use a Table-Valued parameter: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters#passing. You will have to declare a new type in SQL using CREATE TYPE.

    But, the modern SQL includes a useful function — STRING_SPLIT. In C# you can merge the values using

       string s = string.Join(“,”, myList).

    Then pass it to a parameter of varchar(max) type.

    In SQL, do this:

    delete from MyTable

    where Id in (select value from STRING_SPLIT(@myparam, ','))

     

    If the number of values is enormous, you can perform several calls.

    • Edited by Viorel_MVP Wednesday, August 28, 2019 9:31 AM
    • Marked as answer by loftty Wednesday, August 28, 2019 3:53 PM
    Wednesday, August 28, 2019 9:28 AM
  • Hello,

    My question is how do you identify that many rows to delete? And is it the entire table or what is the criteria for deletion and are there relations to other tables and if so are there cascading delete rules in place. From there dictates the best method to remove this many rows be it in code or in SSMS. Lastly, consideration should be taken to use a transaction when performing this type of operation. Also, consider deleting rows in chunks for performance considerations.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, August 28, 2019 10:05 AM
    Moderator
  • Hi Viorel,

    Thanks for your suggestion. It works amazing, went from 1hr to 1 min :)

    Thanks again,

    Ben

    Wednesday, August 28, 2019 3:53 PM