CODEWITHSUNDEEP
c#sql
Chani Poz
Chani Poz

Reputation: 1463

Update thousands of rows

I have this query:

s="update myTable set date='09/09/12' where crc32 in("+list+")";

But because of my list is with thousands of values I think that there is a better way to do it. I don't know which way, or how.

Update: The column crc32 is int. The list is string like: 

list = "2345,-6467,7865,7654,...."

I exeute the query by c#. the list contains values that I cann't get them by sql query, just through my code.

Thanks

Upvotes: 1

Views: 538

Answers (2)

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

Check Table valued parameters. http://msdn.microsoft.com/en-us/library/bb510489.aspx Put it in a stored procedure, like:

create procedure mysp
@tvp tvp_list readonly
as
 update mt
 set date='09/09/12'
 from mytable mt
 join @tvp t on tvp.id = crc32

And call it from C# with a datatable parameter containing your crc32

var cmd = new SqlCommand(CommandText = "prc_update",conn);

...

var dt = new DataTable();
dt.Columns.Add("crc32",typeof(string));
..... you can add more if you like
foreach(var item in myList)
{
  var row = dt.NewRow();
  row["crc32"] = item;
  dt.Rows.Add(row);
}

cmd.Parameters.Add("@tvp", System.Data.SqlDbType.Structured);
cmd.Parameters["@tvp"].Value = dt;

.....

conn.Open();
cmd.ExecuteNonQuery();

Upvotes: 2

cjb110
cjb110

Reputation: 1491

Is List in the database somewhere?

As an UPDATE with a JOIN is probably quicker, it might even be quicker to create a table, put List in it, and then do the UPDATE with a JOIN.

Upvotes: 1

Related Questions