批量插入

转自：http://www.cnblogs.com/zhangql/archive/2008/09/27/1300964.html

 

 

我们这里讨论的只是普通sql语句如何写更高效，不考虑特殊的用文件中转等导入方式，毕竟在代码中调用sql语句或存储过程才更方便。

    批量删除很简单，大家可能都用过：

    DELETE FROM TestTable WHERE ID IN (1, 3, 54, 68)  --sql2005下运行通过

    当用户在界面上不连续的选择多项进行删除时，该语句比循环调用多次删除或多条delete语句中间加分号一次调用等方法都高效的多。

   

    本文重点讲述的是批量插入的写法：

    sql写法：

    INSERT INTO TestTable SELECT 1, 'abc' UNION SELECT  2, 'bcd'  UNION SELECT 3, 'cde'   --TestTable表没有主键，ID不是主键

    oracle写法：

    INSERT INTO TestTable SELECT 1, 'abc' From daul UNION SELECT  2, 'bcd' From daul    --TestTable表没有主键，ID不是主键

    曾经测试过，这种写法插入1000条数据比循环调用1000次insert或1000条insert语句简单叠加一次调用要高效得多，大概快20多倍（调试状态不是太准）。其实很简单，就用了个union（union all 也可以），但当时得出测试结果时还是很惊喜的。

    要得出这个结果需要两个条件：

    1、表不能有主键或者主键是数据库默认的（sql用自动递增列，oracle用序列）

    2、组合sql语句时只能直接用字符串连接，不能用参数化sql语句的写法（就是在组合的sql中用@parm做占位符，再给Command对象添加Parameter）

    以上两条任意一条不满足，效率的提高都不明显。

    另外，sql语句的最大长度有限制，组合几千条数据写成一条insert语句，可能会超过上限，所以如果有5000条数据，可以一次insert 1000条，分5次写完（不一定一次1000条最合适，有兴趣的朋友可以研究）。

 

声明：看了几位朋友的评论后发现自己没有叙述清楚

     上面的两个条件并不是说这样做就好，而是说当应用场景满足这两个条件时才适合用union的写法

     当时用这种写法时，是为了解决一个WinForm程序批量导入手机号码的问题，程序给自己人用，excel文件也是可靠的，不需要考虑sql攻击。还有一些场景是大量数据来源于通过Gprs网络连接的专用终端，总之数据来源是可靠的，还有就是数据量很大但不可能巨大。

 

 

ps：INSERT INTO TestTable SELECT 1, 'abc' UNION SELECT 2, 'bcd' UNION SELECT 3, 'cde'

union 这种写法来组合表效率是很低的，实质上这种写发已经违背了SQL SERVER 基于集合的原则

参考一下这种方式：
DECLARE @x xml
SET @x='<ids>
<im id="1" c="100"/>
<im id="2" c="300"/>
<im id="3" c="400"/>
</ids>'
SELECT T1.ids.value('@id','int') as id ,T1.ids.value('@c','int') as click FROM @x.nodes('/ids/im') T1(ids)

效率最高的的是使用CLR 来写个表值函数来       这种方法有待考证。

 

ps：存储过程