CODEWITHSUNDEEP
sqlsql-serverazure-sql-database
Vyrotek
Vyrotek

Reputation: 5459

How can I insert new rows and delete missing rows from a table given a collection of values?

Lets say I have a table with the following data.

Id   Value
1  | A
2  | B
3  | C

I need to synchronize the values in this table with a collection of values. I would like to remove any rows which are not in my collection, add rows which are and leave the matching ones alone.

Given this collection of values: 

C,D,E

After the operation I expect the table to contain:

Id   Value
3  | C
4  | D
5  | E

I'm aware of most of the obvious solutions which require multiple queries. What I'm looking for are possible solutions that are more efficient. Can I use the MERGE statement here somehow?

Edit - The collection values are in a C# collection (List<string>) and I am using the standard SqlConnection/SqlCommand in .NET to execute the query.

Here's something I considered to take care of removing values. But this might be frowned upon because I would have to do a bit of string concatenation to create it.

DELETE FROM [MyTable] 
WHERE [Value] NOT IN ('C','D','E')

But then to add values it seems like I would have to create multiple IF NOT EXISTS THEN INSERT query statements for each value in my collection.

Upvotes: 1

Views: 667

Answers (2)

sgeddes
sgeddes

Reputation: 62851

I don't think you can do this in a single SQL statement, but you could create a stored procedure to do the job:

create procedure upsertnewrecords(
   @collection varchar(max)
  ) as
begin
  delete 
  from yourtable 
  where charindex(','+value+',', ','+@collection+',') = 0

  ;with cte as (
    select split.t.value('.', 'VARCHAR(100)') newvalue  
    from (
      select cast ('<M>' + replace(@collection, ',', '</M><M>') + '</M>' as xml) as String    
    ) t 
    cross apply String.nodes ('/M') AS Split(t)  
  )

  insert into yourtable
  select newvalue 
  from cte
  where newvalue not in 
    (select value from yourtable)
end

This stored procedure first uses CHARINDEX to delete values that aren't in your current list, then uses CROSS APPLY to convert your comma delimited list to a table-valued list and finally insert those via a common table expression.

Upvotes: 1

cost
cost

Reputation: 4480

Okay, so your collection is in a C# list. That makes this easier. This isn't the most efficient way to do it since it involves a lot of queries, and it would work better using a Dictionary, but it's a solution if you aren't pressed for time and don't want to use string concatenation to make a complicated query.

using (SqlConnection connection = new SqlConnection(.....))
{
   connection.Open;

   using (SqlCommand command = new SqlCommand("SELECT ID, Value FROM Table"))
   {
      using (SqlDataReader reader = SqlCommand.ExecuteReader())
      {
         while (reader.Read())
         {
             if (THELIST.Contains(reader["Value"].ToString())
             {

                  THELIST.Remove(reader["Value"].ToString());
             }
             else
             {
                  //Execute a SqlCommand in here to do a DELETE where ID = reader["ID"]
             }

         }

      }

   }

   //Now that you've deleted all the ones that aren't in this list, go through what's left in the list and insert them (only the ones that aren't in the database will be left, since you deleted them as they were found

   foreach (string thing in THELIST)
   {
    //Execute a SqlCommand to do an insert into the database
   }

}

Upvotes: 0

Related Questions