SQL Server 2008 bulk update using stored procedure

I have 2 tables in the DB. each with "Name" column and "Count" column each.

I would like to update the Count c开发者_开发知识库olumn in the second table from the Count in the first table only where the "Name" columns are equal.

Example:

First Table:

Name Count

jack 25

mike 44

Name Count

jack 23

mike 9

david 88

Result (the second table would look like that...)

Name Count

jack 25

mike 44

david 88

NOTES:

1. Both tables are huge. (although the second table is bigger...)

2. The update must be as fast as possible...

(if there are more options other than stored procedures, i would love to hear.)

3. "Count" defined as bigint while "Name" as nvarchar(100)

4. the "Count" field in the first table is always bigger than the equivalent in the

second table.

I think that there are more options (other than stored procedure) maybe with MERGE or TRANSACTION as long as it will be the fastest way...

Thanks!

The best way would be to keep it simple

UPDATE Table2
SET Count = t1.Count
FROM Table1
WHERE Table2.Name = Table1.Name 
  AND Table2.Count <> Table1.Count

If the performance of this query is not satisfactory due to the size of your tables the best solution would be to partition the tables based on the name field. The query can then be run from different threads at the same time with and extra filter based on Name to satisfy the partition function.

For example: (assuming name is a varchar(20) column)

UPDATE Table2
SET Count = t1.Count
FROM Table1
WHERE Table2.Name = Table1.Name 
  AND Table2.Count <> Table1.Count
  AND Table2.Name between cast('Jack' as varchar(20)) 
                      and cast('Mike' as varchar(20))

(The cast on the strings is a big help for Sql Server to properly do partition elimination.)