sql server 2005 大数据量插入性能对比

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

sql server 2005大数据量的插入操作

第一，写个存储过程，传入参数，存储过程里面是insert操作，

第二，用System.Data.SqlClient.SqlBulkCopy实例方法，

数据库表Passport

CREATE TABLE [dbo].[Passport](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PassportKey] [nchar](50) NOT NULL,
[addtime] [datetime] NOT NULL,
CONSTRAINT [PK_Passport] PRIMARY KEY CLUSTERED
( [Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

存储过程 CreatePassport

Create proc [dbo].[CreatePassport]
@passport varchar(50),
@addtime datetime
as
insert into Passport (PassportKey,addtime) values (@passport,@addtime)

对比测试，方法一调用存储过程，循环插入数据，方法二，构造表的对象，然后调用SqlBulkCopy

少量数据

大量数据

代码

DataTable dataTable = GetTableSchema();
string passportKey;
for (int i = ; i < count; i++)
{
passportKey = Guid.NewGuid().ToString();
DataRow dataRow = dataTable.NewRow();
dataRow[] = passportKey;
dataRow[] = DateTime.Now;
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!=)
{
sqlBulkCopy.WriteToServer(dataTable);
}
sqlBulkCopy.Close();
sqlConnection.Close();