分享贴：sql2000，sql2005如何批量update

[yzy8788]

sql2008中，可以自定义type，比如自定义一个table，然后在程序中构建一个DataTable并装入海量需要更新的数据，然后一把将这个DataTable更新到数据库中即可。
具体就不多说了，网上可以搜到很多示例。

本次重点讲讲，sql2008以下的版本，没用自定义type的机制，那么该如何快速高效地批量更新呢？
假设我们需要更新的目标表是a，
1、借助上面sql2008思想，我们在数据库中创建一个表b，表b中的字段就是要更新的字段
2、在c#程序中，构建一个DataTable,字段跟表b一样，注意列名、类型、顺序都要一样
3、在c#中，把海量数据装入DataTable
4、利用c#自带的System.Data.SqlClient.SqlBulkCopy一把将DataTable拷贝入数据表b中，速度很快
5、在数据库中，写sql语句，把表b中的值更新到表a
6、大功可成

//SqlBulkCopy实例
using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connString, SqlBulkCopyOptions.KeepNulls))
            {
                sqlbulkcopy.DestinationTableName = "表b名称";
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                }
                sqlbulkcopy.WriteToServer(dt);
            }

--用sql语句把表b中的数值更新到表a中
update [表a] set username=b.username,qq=b.qq,sex=b.sex
		from [表b] b 
		where b.userid=[表a].userid

[yzy8788]

上面的代码格式化有点问题，重发一下

//SqlBulkCopy实例
using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connString, SqlBulkCopyOptions.KeepNulls))
            {
                sqlbulkcopy.DestinationTableName = "表b名称";
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                }
                sqlbulkcopy.WriteToServer(dt);
            }

--用sql语句把表b中的数值更新到表a中
update [表a] set username=b.username,qq=b.qq,sex=b.sex
		from [表b] b 
		where b.userid=[表a].userid

[二月十六]

感谢分享

[OwenZeng_DBA]

谢谢分享。

[吉普赛的歌]

这个当然是好， 但没考虑并发。 如果考虑并发， 还得加字段。 如果考虑历史数据太多， 还得及时清理。

[yzy8788]

引用 4 楼 yenange 的回复:

这个当然是好， 但没考虑并发。 如果考虑并发， 还得加字段。 如果考虑历史数据太多， 还得及时清理。

1、上面只是抛一个思想出来，具体使用中，并发并不难解决，例如在表b中设置联合主键的办法或者在程序中lock等等，我目前是上千万条数据体量，同时6*8=48个线程update数据，可以轻松应对。 2、历史数据太多，这个是一个值得注意的问题，因为不能让表b一直增长，这样会一直给表a update一些过期数据 实际使用过程中，我是写了一个存储过程 在update [表a] set……之后，马上把刚才更新的数据从表b中删除，具体如何来分辨哪些数据可删，我是在表b中新增了一个addtime时间字段 存储过程主体大致可以写为

update [表a] set……where  b.addtime<=@now and 其他条件

delete from [表b] where addtime<=@now

当然如果能安装sql2008或者以上版本更好，这样就可以直接创建一个自定义类型 as table了，免得像上面这样绕弯 我是一些特殊原因，不好更改数据库版本

[吉普赛的歌]

并发， 应该类似这样：

--用sql语句把表b中的数值更新到表a中
update [表a] set username=b.username,qq=b.qq,sex=b.sex
        from [表b] b 
        where b.batch=@batch and  b.userid=[表a].userid
delete from b where batch=@batch
--batch 字段可以做成 guid ， 用以区别每个会话插入进来的批次数据。

[yzy8788]

引用 6 楼 yenange 的回复:

并发， 应该类似这样：

--用sql语句把表b中的数值更新到表a中
update [表a] set username=b.username,qq=b.qq,sex=b.sex
        from [表b] b 
        where b.batch=@batch and  b.userid=[表a].userid
delete from b where batch=@batch
--batch 字段可以做成 guid ， 用以区别每个会话插入进来的批次数据。

guid貌似更高大上一点，我对sqlserver技术水平有限，不是特别透，所以就用了一些笨办法，目前用时间戳来区分，也能解决我的问题，就先这么用着了。 看兄上面这种写法，会不会每次就update一条数据啊？b.batch=@batch，batch做成guid的话，那batch应该唯一吧？ 本贴时分享，批量海量update，表b中有成千上万条数据，要一次性update到表a中，且表b还在一直增长，所以我用时间戳区分界线

[RICHEER COCA]

引用 6 楼 yenange 的回复:

并发， 应该类似这样：

--用sql语句把表b中的数值更新到表a中
update [表a] set username=b.username,qq=b.qq,sex=b.sex
        from [表b] b 
        where b.batch=@batch and  b.userid=[表a].userid
delete from b where batch=@batch
--batch 字段可以做成 guid ， 用以区别每个会话插入进来的批次数据。

[吉普赛的歌]

引用 7 楼 yzy8788 的回复:

[quote=引用 6 楼 yenange 的回复:] 并发， 应该类似这样：

--用sql语句把表b中的数值更新到表a中
update [表a] set username=b.username,qq=b.qq,sex=b.sex
        from [表b] b 
        where b.batch=@batch and  b.userid=[表a].userid
delete from b where batch=@batch
--batch 字段可以做成 guid ， 用以区别每个会话插入进来的批次数据。

guid貌似更高大上一点，我对sqlserver技术水平有限，不是特别透，所以就用了一些笨办法，目前用时间戳来区分，也能解决我的问题，就先这么用着了。 看兄上面这种写法，会不会每次就update一条数据啊？b.batch=@batch，batch做成guid的话，那batch应该唯一吧？ 本贴时分享，批量海量update，表b中有成千上万条数据，要一次性update到表a中，且表b还在一直增长，所以我用时间戳区分界线[/quote] 1. batch 并不是唯一， 你构建的 datatable 中也有一个batch字段， 同一个 datatable 所有记录的 batch 是一致的 ， 不同会话构建的 datatable ; 2. batch 只是为了区分不同会话的 datatable 而加上的，你要理解会话 —— 同一时间可能有大量不同的用户在使用你这个功能， 时间并不能完全保证唯一; 3. b 表中的数据不会大量累积， 每次操作完就已将这一批数据都全部删除了。

[吉普赛的歌]

不同会话构建的 datatable =》 不同会话构建的 datatable 的才是不一致的。 比如张三在做这个操作， 李四也在做这个操作。 张三有 5 条数据，5条数据的 batch 全都是 6527D383-49D5-478E-BEC4-51DB8D33866D 李四有 6 条数据，6条数据的 batch 全都是 10BA9F5B-CBEE-4EB5-B7B3-44942F1778C5 在操作时因为 batch 不同， 张三李四不会互相干扰。

[吉普赛的歌]

guid 也很简单：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication8
{
    class Program
    {
        static void Main(string[] args)
        {
            Guid g = Guid.NewGuid();
            Console.WriteLine(g.ToString());

            Console.Read();
        }
    }
}

在数据库可以用 varchar(40) 或 UNIQUEIDENTIFIER 类型来保存。

[yzy8788]

明白了，谢谢补充

[深度格式]

我批量更新用的是表类型，速度还可以