SQL Server批量插入数据sql与性能比较

有这样一个表【OperatorInfo】，字段有[OPERATOR_NO]，[OPERATOR_PWD]，[OPERATOR_NAME]，[GROUP_ID]，[GROUP_NO]，[SKILL_LEVEL]，[OPERATOR_TYPE]，[PAUSENUM], [OPERATOR_ACD_TYPE]

其中[OPERATOR_NO]需要指定一个起始数值，然后实现规定的累加，其他的一些都是默认值，可以不用理会。

以下是代码：

 代码如下 复制代码

DECLARE @recordNmber int 
DECLARE @OPERATOR_NO int 
 
SET @recordNmber   = 0  
SET @OPERATOR_NO = 6990 --座席起始工号，例如6990，将从6991开始插入 
 
WHILE (@recordNmber) < $30 --需要添加的座席数量 
BEGIN 
      SET @recordNmber = @recordNmber + 1 
      SET @OPERATOR_NO = @OPERATOR_NO + 1 
      INSERT INTO [OperatorInfo] ([OPERATOR_NO], [OPERATOR_PWD], [OPERATOR_NAME], [GROUP_ID],  
      [GROUP_NO], [SKILL_LEVEL], [OPERATOR_TYPE], [PAUSENUM], [OPERATOR_ACD_TYPE]) 
      VALUES (@OPERATOR_NO,'123456',@OPERATOR_NO,'4204','4204',1,'1',1,'1') 
END

这里我只加了30条，如果你需要，可以自行定义

性能对比

 

.创建表值参数类型

我们打开查询分析器，然后在查询分析器中执行下列代码：

 代码如下 复制代码
Create Type PassportTableType as Table
(
PassportKey nvarchar(50)
)

执行成功以后，我们打开企业管理器，按顺序依次展开下列节点--数据库、展开可编程性、类型、用户自定义表类型，就可以看到我们创建好的表值类型了如下图所示：

 

说明我们创建表值类型成功了。

3.编写存储过程

存储过程的代码为：

 代码如下 复制代码
USE [TestInsert]
GO
/****** Object: StoredProcedure [dbo].[CreatePassportWithTVP] Script Date: 03/02/2010 00:14:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Kevin>
-- Create date: <2010-3-1>
-- Description: <创建通行证>
-- =============================================
Create PROCEDURE [dbo].[CreatePassportWithTVP]
@TVP PassportTableType readonly
AS
BEGIN
SET NOCOUNT ON;
Insert into Passport(PassportKey) select PassportKey from @TVP
END

可能在查询分析器中，智能提示会提示表值类型有问题，会出现红色下划线（见下图），不用理会，继续运行我们的代码，完成存储过程的创建 4.编写代码调用存储过程。

三种数据库的插入方式代码如下，由于时间比较紧，代码可能不那么易读，特别代码我加了些注释。

主要部分的代码

 代码如下 复制代码

using System;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using com.DataAccess;

namespace ConsoleAppInsertTest
{
    class Program
    {
        static string connectionString = SqlHelper.ConnectionStringLocalTransaction;    //数据库连接字符串
        static int count = 1000000;           //插入的条数
        static void Main(string[] args)
        {
            //long commonInsertRunTime = CommonInsert();
            //Console.WriteLine(string.Format("普通方式插入{1}条数据所用的时间是{0}毫秒", commonInsertRunTime, count));

            long sqlBulkCopyInsertRunTime = SqlBulkCopyInsert();
            Console.WriteLine(string.Format("使用SqlBulkCopy插入{1}条数据所用的时间是{0}毫秒", sqlBulkCopyInsertRunTime, count));

            long TVPInsertRunTime = TVPInsert();
            Console.WriteLine(string.Format("使用表值方式（TVP）插入{1}条数据所用的时间是{0}毫秒", TVPInsertRunTime, count));
        }

        /// <summary>
        /// 普通调用存储过程插入数据
        /// </summary>
        /// <returns></returns>
        private static long CommonInsert()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
           
            string passportKey;
            for (int i = 0; i < count; i++)
            {
                passportKey = Guid.NewGuid().ToString();
                SqlParameter[] sqlParameter = { new SqlParameter("@passport", passportKey) };
                SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassport", sqlParameter);
            }
            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }

        /// <summary>
        /// 使用SqlBulkCopy方式插入数据
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        private static long SqlBulkCopyInsert()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            DataTable dataTable = GetTableSchema();
            string passportKey;
            for (int i = 0; i < count; i++)
            {
                passportKey = Guid.NewGuid().ToString();
                DataRow dataRow = dataTable.NewRow();
                dataRow[0] = passportKey;
                dataTable.Rows.Add(dataRow);
            }

            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
            sqlBulkCopy.DestinationTableName = "Passport";
            sqlBulkCopy.BatchSize = dataTable.Rows.Count;
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            sqlConnection.Open();
            if (dataTable!=null && dataTable.Rows.Count!=0)
            {
                sqlBulkCopy.WriteToServer(dataTable);
            }
            sqlBulkCopy.Close();
            sqlConnection.Close();

            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }

        private static long TVPInsert()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            DataTable dataTable = GetTableSchema();
            string passportKey;
            for (int i = 0; i < count; i++)
            {
                passportKey = Guid.NewGuid().ToString();
                DataRow dataRow = dataTable.NewRow();
                dataRow[0] = passportKey;
                dataTable.Rows.Add(dataRow);
            }

            SqlParameter[] sqlParameter = { new SqlParameter("@TVP", dataTable) };
            SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassportWithTVP", sqlParameter);

            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }

        private static DataTable GetTableSchema()
        {
            DataTable dataTable = new DataTable();
            dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("PassportKey") });
           
            return dataTable;
        }

    }
}

比较神秘的代码其实就下面这两行，该代码是将一个dataTable做为参数传给了我们的存储过程。简单吧。

 代码如下 复制代码

SqlParameter[] sqlParameter = { new SqlParameter("@TVP", dataTable) };
SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassportWithTVP", sqlParameter);

5.测试并记录测试结果

1.创建表。

为了简单，表中只有一个字段，如下图所示：

第一组测试，插入记录数1000
第二组测试，插入记录数10000
第三组测试，插入记录数1000000

通过以上测试方案，不难发现，技术方案二的优势还是蛮高的。无论是从通用性还是从性能上考虑，都应该是
优先被选择的，还有一点，它的技术复杂度要比技术方案三要简单一些

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索string
， 存储过程
， static
， new
， 代码
类型
sql server 批量更新、sqlserver 批量insert、sqlserver批量update、sqlserver 批量更新、sql server批量insert，以便于您获取更多的相关知识。

时间： 2024-08-30 01:46:12

SQL Server批量插入数据sql与性能比较的相关文章

MySQL和SQL Server在插入数据上的差别

环境:MySQL Sever 5.1 + SQLServer 2008 R2 问题:MySQL和SQLServer在插入数据上的差别 先看两张图: MySQL: SQLServer: 在MySQL里创建表,主键自增 ,在插入数据时,不指定列名(表示对每个字段都赋值),会报列数不匹配,指定列名后插入正常:而在 SQLServer里,表的主键同样是自增,不指定列名却可以正常插入. 附 SQL: MySQL create table tb_user(id int primary key auto_in

用SQL数据库批量插入数据简介

最近忙于公司BI软件性能测试,这几天主要测试CUBE采用ROLAP下,PA的并发和稳定性.涉及表和维度,立方:事实表sales_fact_1997,维度表time_by_day:立方:sales.修改内容:删除原来的TIME维度,新建TIME维度,修改SALES立方. 数据插入: 1.事实数据插入:通过DTS加调度实现将sales_fact_1997的数据进行复制.频率为每分钟10000条.主要用到的SQL语句:select top 10000 * from sales_fact_1997 2.

关于sql server批量插入和更新的两种解决方案_MsSql

复制代码 代码如下: .游标方式 1 DECLARE @Data NVARCHAR(max)  SET @Data='1,tanw;2,keenboy'   --Id,Name  DECLARE @dataItem NVARCHAR(100)  DECLARE data_cursor CURSOR FOR (SELECT * FROM split(@Data,';')) OPEN data_cursor FETCH NEXT FROM data_cursor INTO @dataItem   

VB.NET操作 SQL SERVER的 二进制数据

server|二进制|数据 在VB时期, 向SQL SERVER 中插入二进制数据, 是通过 ADODB.STREAM 实现, 在.NET中, 对 "流"格式的操作更加强大而简单,本篇演示向SQL SERVER 中插入数据并读出的功能. 在窗体上添加一个 OPENFILEDIALOG 控件, 两个PICTUREBOX, 代码如下:-------------------------------------------------------------------------------

C#实现SQL批量插入数据到表的方法_C#教程

本文实例讲述了C#实现SQL批量插入数据到表的方法.分享给大家供大家参考,具体如下: #region 帮助实例:SQL 批量插入数据 多种方法 /// <summary> /// SqlBulkCopy往数据库中批量插入数据 /// </summary> /// <param name="sourceDataTable">数据源表</param> /// <param name="targetTableName"

sql下三种批量插入数据的方法_MsSql

本文将介绍三种批量插入数据的方法.第一种方法是使用循环语句逐个将数据项插入到数据库中:第二种方法使用的是SqlBulkCopy,使您可以用其他源的数据有效批量加载 SQL Server 表:第三种使用的方法是sql server中的表值参数方法,表值参数是 SQL Server 2008 中的新参数类型.表值参数是使用用户定义的表类型来声明的.使用表值参数,可以不必创建临时表或许多参数,即可向 Transact-SQL 语句或例程(如存储过程或函数)发送多行数据.       代码示例:    

sql下三种批量插入数据的方法

本文将介绍三种批量插入数据的方法.第一种方法是使用循环语句逐个将数据项插入到数据库中:第二种方法使用的是SqlBulkCopy,使您可以用其他源的数据有效批量加载 SQL Server 表:第三种使用的方法是sql server中的表值参数方法,表值参数是 SQL Server 2008 中的新参数类型.表值参数是使用用户定义的表类型来声明的.使用表值参数,可以不必创建临时表或许多参数,即可向 Transact-SQL 语句或例程(如存储过程或函数)发送多行数据. 代码示例: 此例子为控制台输出

c#实现批量插入数据到sql数据库表中，怎么做到跳过插入失败的数据，继续插入

问题描述 c#实现批量插入数据到sql数据库表中,怎么做到跳过插入失败的数据,继续插入 c#实现批量插入数据到sql数据库表中,怎么做到跳过插入失败的数据,继续插入. 并且报错,哪几天数据失败了.和失败的原因. 我现在是可以生成一个把一个数据库表的数据导入到另一个数据库表中.但是呢,有些数据由于某个字段过长无法导入成功,而导致所有的都无法导入.直接造成导入失败.我 想过滤掉那条不成功的继续导入.不至于都导入不成功.急! 想和好久也没解决.网上也没找到说法 解决方案 数据库表插入数据失败数据库批量

SQL Server中一个隐性的IO性能杀手-Forwarded record

原文:SQL Server中一个隐性的IO性能杀手-Forwarded record 简介     最近在一个客户那里注意到一个计数器很高(Forwarded Records/Sec),伴随着间歇性的磁盘等待队列的波动.本篇文章分享什么是forwarded record,并从原理上谈一谈为什么Forwarded record会造成额外的IO.   存放原理     在SQL Server中,当数据是以堆的形式存放时,数据是无序的,所有非聚集索引的指针存放指向物理地址的RID.当数据行中的变长列增