Sql Server Delete Where In

Question

I already read many posts about this topic.

I'm trying to perform on a real table the following:

DELETE FROM MYTABLE WHERE ID in (...)

where in clause can contain 1,2 or many (>3000) values (deriving from a list in c#)

I haven't problems about triggers, FK...

I can't find a complete sample using temporary table table value, or type. And I can't understand how can I insert (3000 records) in the temp table/table value/type without having performance problems. I already understand to do the join after.

I would avoid Stored Procedure if possible

I don't understand the question. If you are deleting from the table, what is the performance issue with inserts? Are you using a temporary table, permanent table, or table variable? — Gordon Linoff, May 14 '18 at 10:55
@MitchWheat thanks for comment. Maybe some link? I already read many post on Stackoverflow — Emanuele, May 14 '18 at 10:57
@GordonLinoff my table is real. I can't find a complete sample using the temp table or table value. — Emanuele, May 14 '18 at 10:58
Are you trying to ask how to insert the values in a temporary table and select from it for the `IN`? — sticky bit, May 14 '18 at 11:01
Possible duplicate of [Pass table valued parameter using ADO.Net](https://stackoverflow.com/questions/10409576/pass-table-valued-parameter-using-ado-net). Possible duplicate of [How to pass table value parameters to stored procedure from .net code](https://stackoverflow.com/q/5595353/1260204) — Igor, May 14 '18 at 11:03
@Emanuele, see [Table Valued Parameters](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters). — Dan Guzman, May 14 '18 at 11:03
@DanBracuk I don't use any ORM. `String.Join(",", myList.Select(x => "'" + x + "'"))` — Emanuele, May 14 '18 at 11:04
@Igor It's necessary to use stored procedure? I would avoid it. — Emanuele, May 14 '18 at 11:07
@Emanuele - no, not at all. See the 1st suggested duplicate. It is exactly what you are wanting to do. — Igor, May 14 '18 at 11:08
@Emanuele - just ignore the stored procedure specific code, use your query and do a join with the passed in table type value. — Igor, May 14 '18 at 11:10
The first link of possible duplicate is clearly wrong. I'm reading the second one. @DanGuzman I already read the documentation but I can't understand some things, so I wrote here... — Emanuele, May 14 '18 at 11:12
@Emanuele, not sure what exactly you don't understand. See http://www.sommarskog.se/arrays-in-sql-2008.html for additional C# examples. — Dan Guzman, May 14 '18 at 11:18
@DanGuzman in your linked article where can i find the answer to my question without stored procedure as i asked? It's very interesting. I will read it. Thanks — Emanuele, May 14 '18 at 11:20
@Emanuele, the only difference without a proc is you set the CommandText properly to your `DELETE` statement, set CommandType.Text, and specify thee type name. — Dan Guzman, May 14 '18 at 11:28

score 1 · Accepted Answer · answered May 14 '18 at 11:22

1

Please see Pass table valued parameter using ADO.Net, that is where this answer was derived from.

Create type in SQL Server:

CREATE TYPE [dbo].[MyDataType] As Table
(
    cust_id INT
)

Create DataTable in C#:

DataTable myDataTable = new DataTable("dbo.MyDataType");
myDataTable.Columns.Add("cust_id", typeof(Int32));
myDataTable.Rows.Add(1);
myDataTable.Rows.Add(2);

Create SQL Parameter and execute query:

using(var command = new SqlCommand(connection, "DELETE FROM MYTABLE WHERE EXISTS (SELECT 1 FROM @myData WHERE cust_id = ID)"))
{       
  SqlParameter parameter = new SqlParameter();
  parameter.ParameterName = "@myData";
  parameter.SqlDbType = System.Data.SqlDbType.Structured;
  parameter.Value = myDataTable;
  command.Parameters.Add(parameter);

  // execute delete
  command.ExecuteNonQuery();
}

answered May 14 '18 at 11:22

Igor

60,821
10
100
175

What is the difference with my answer? However, I got two down votes. – Emdad May 14 '18 at 11:24
I received 3 closed without trying to understand the question :D @Emdad you are right but this answer is clearer according to me. And i continue to say that is not a duplicate – Emanuele May 14 '18 at 11:26
@Emdad - you are missing implementations for step 2 and 3. – Igor May 14 '18 at 11:26
Ok, starting from this answer. If I have 3000 numeric Id, I have to write 3000 myDataTable.Rows.Add. Is it possible to avoid it? Or it's better do a batch insert? – Emanuele May 14 '18 at 11:27
@Emanuele - no, if you have values you must pass them. Unless you are asking how to write a for/foreach loop? The end result must be values you want to filter on must be added to the table. – Igor May 14 '18 at 11:29
Ok, i will try this solution. I have never used Type before. Thanks – Emanuele May 14 '18 at 11:30
@Igor, He add only sql server tag. If he will face problem to add C# code, I will add in my answer. But for this case, how I got down vote? – Emdad May 14 '18 at 11:31
@Emdad - I do not know, you are not required to leave a comment when downvoting. The only thing you can do is ask in the comments under your answer and ask the downvoters to explain why the downvoted... maybe they will answer. – Igor May 14 '18 at 11:33
@Igor can you explain me what's the difference between type and table (create, insert, drop)? I think using table i can "stay" in my own database. And with thousands records? – Emanuele May 16 '18 at 10:44

Emdad · Answer 2 · 2018-05-14T11:44:44.483

0

Step 1: Create table variable

CREATE TYPE SampleTable AS TABLE
(
        Id INT  
)
GO

Step 2: Insert your table variable from your C# List

Step 3: Delete your table data

DECLARE @Sample_Id_List SampleTable READONLY
DELETE FROM YourTable WHERE Id IN (SELECT Id FROM @Sample_Id_List)

edited May 14 '18 at 11:44

answered May 14 '18 at 11:04

Emdad

822
7
14

Ok. Finally a starting point. 2 questions. Have i to drop table SampleTableVariable then? How can I perform insert? I have to write 3000 insert clauses? Better a batch? – Emanuele May 14 '18 at 11:06
You're creating a type, not a variable. You can't insert into a type, neither select from it. – sticky bit May 14 '18 at 11:07
@stickybit, Need to create type one time , then insert data from C# code and finally select id from type for delete. – Emdad May 14 '18 at 11:10
@Emanuele, you do not need to drop type data. You will insert data from list. – Emdad May 14 '18 at 11:12
You can declare a table variable with something like `DECLARE @t TABLE (i int);`, you don't need to create the type before. Of course you can create a type and use it, but then you'd still have to declare a variable with that type. You simply don't do that in your answer. – sticky bit May 14 '18 at 11:13
Now you are all undestanding that there isn't already a precise answer. I'm reading the Igor's second linked article – Emanuele May 14 '18 at 11:15
@stickybit, In this case, he need to create type. Because, he will insert data from C# list.So he need to create type one time. – Emdad May 14 '18 at 11:16
Insert (step 2) is a normal insert as in a table? – Emanuele May 14 '18 at 11:17
1

That's got nothing to do with C#, it works well without a type there too. But either way a variable needs to be declared. One can insert into or select from a type. E.g. try `sqlCommand.CommandText = @"DECLARE @t TABLE (i int); INSERT INTO @t VALUES (7), (9); SELECT * FROM @t;";`. – sticky bit May 14 '18 at 11:33
@stickybit, I have updated my answer. Thanks for your clarification. – Emdad May 14 '18 at 11:45
@stickybit you are right but then I will have n insert to write and it's the same to write one delete with n in values.... (any sql limit but same problem according to me) – Emanuele May 14 '18 at 12:39
1

@Emanuele: If you gut _n_ values and there is no way, to select them in the database (e.g. they are in a table and share a common attribute value), you have to transfer them at one point. You can put them in a variable or (temporary) table, to reuse them in subsequent commands without retransfering them but there is no way to circumvent the one transfer. – sticky bit May 14 '18 at 13:06
@stickybit I have never used the type solution. Is it better to use type or temporary table? Thanks – Emanuele May 16 '18 at 08:08
@Emanuele, according to your requirement, you need to use type. Because, you have to create type once but for temporary table, you need to create every time to execute delete query. Thanks. – Emdad May 16 '18 at 08:16

Sql Server Delete Where In

2 Answers2