1

I use Sql server 2008 to store my data,and the table structure like that

index float  not null,
type int   not null,
value int  not null,

and the (index,type) is unique.there are not two datas has the same index and the same type.

So when I insert the data to the table, I have to check the (index,type) pair whether in the table already, if it exists I use update statement, otherwise, I insert it directly.but I think this is not a efficient way,because:

  1. Most of the data' index-type pair is not existed int the table.so the select operation is waste, especially the table is huge.

  2. When I use C# or other CLR language to insert the data, I can't use batch copy or batch insert.

is there any way to overwrite the data directly without check whether it is existed in the table?

Oded
  • 489,969
  • 99
  • 883
  • 1,009
storm
  • 395
  • 1
  • 4
  • 11

3 Answers3

4

If you want to update OR insert the data, you need to use merge:

merge MyTable t using (select @index index, @type type, @value value) s on
    t.index = s.index
    and t.type = s.type
when not matched insert (index, type value) values (s.index, s.type, s.value)
when matched update set value = s.value;

This will look at your values and take the appropriate action.

To do this in C#, you have to use the traditional SqlClient:

SqlConnection conn = new SqlConnection("Data Source=dbserver;Initial Catalog=dbname;Integrated Security=SSPI;");
SqlCommand comm = new SqlCommand();
conn.Open();
comm.Connection = conn;

//Add in your values here
comm.Parameters.AddWithValue("@index", index);
comm.Parameters.AddWithValue("@type", type);
comm.Parameters.AddWithValue("@value", value);

comm.CommandText = 
    "merge MyTable t using (select @index index, @type type, @value value) s on " +
        "t.index = s.index and t.type = s.type " +
    "when not matched insert (index, type value) values (s.index, s.type, s.value) " +
    "when matched update set value = s.value;"
comm.ExecuteNonQuery();

comm.Dispose();
conn.Close();
conn.Dispose();
Eric
  • 92,005
  • 12
  • 114
  • 115
  • if you create a unique constraint for the two cols, you are not allowed to insert the data with the same index,type. and you will get a exception when you use CLR language to do the batch insertions. – storm Dec 16 '11 at 18:07
  • Updated to include the `merge` statement – Eric Dec 16 '11 at 18:14
  • ok， I will test these different ways and figure out which one is better. – storm Dec 16 '11 at 18:14
  • `Merge` is what you want if you want to update if the row exists, but insert if it doesn't. It's new in SQL Server 2008, but it is very powerful. You can also delete `when not matched by source`, but you don't need that here. – Eric Dec 16 '11 at 18:17
  • thanks eric, I want give you one up, but I don't have the reputation. – storm Dec 16 '11 at 18:19
  • one more thing, if I use C# to insert the data, how to use the merge? – storm Dec 16 '11 at 18:31
0

You should make (index, type) into a composite primary key (aka compound key).

This would ensure that the table can only even have unique pairs of these (I am assuming the table does not have a primary key already).

If the table does have a primary key, you can add a UNIQUE constraint onto those columns with similar effect.

Once defined, this means that any attempt to insert a duplicate pair would fail.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
0

Other answers recommend constraints. Creating constraints just means you will be executing insert statements that trigger errors. The next step (after having created the constraints) is something like INSERT ON DUPLICATE KEY UPDATE, which apparently does have an Sql Server equivalent.

Community
  • 1
  • 1
Julien
  • 212
  • 1
  • 18
  • 53