我想做一个测试，如何向mssql快速插入10亿条数据。

字段有ID（int，自动编号） title（char）
title的内容可以相同。。。或者是title1，title2，title3和id一样自动增长。。

求快速插入的办法。

或者是分段插入。
比如一次性插入一百万，或者一千万条数据，因为要考虑pc机器的性能，求各位大侠支招。。

14 个解决方案

#1

建议:
1.分批插入,如一次插入5000笔记录.
2.加tablock hint: insert into [表名] with(tablock) ...

另问: 敢问LZ这是想干啥?10亿数据?要测试SQL Server的极限?

#2

引用 1 楼 ap0405140 的回复:

建议:
1.分批插入,如一次插入5000笔记录.
2.加tablock hint: insert into [表名] with(tablock) ...

另问: 敢问LZ这是想干啥?10亿数据?要测试SQL Server的极限?

对啊。。。

求具体代码。。。

#3

--SQL:
;WITH 
cte1 AS(SELECT num = 1 UNION ALL SELECT 1),
cte2 AS(SELECT num = 1 FROM cte1 a, cte1 b),
cte3 AS(SELECT num = 1 FROM cte2 a, cte2 b),
cte4 AS(SELECT num = 1 FROM cte3 a, cte3 b),
cte5 AS(SELECT num = 1 FROM cte4 a, cte4 b),
cteNum as(SELECT num = ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM cte5)
INSERT INTO tb(title)
SELECT TOP(10000000) --1千万
	'title'+LTRIM(num)
FROM cteNum

#4

create table ph
(ID int identity(1,1), title char(50))

set nocount on
declare @x int
select @x=1
while(@x<=1000000000)
begin
 insert into ph(title) values('title'+rtrim(@x))

 select @x=@x+1
end
set nocount off

#5

我用另外一种方式，差不多一秒多钟能增加一万条数据。。。
看看你这个效率如何？

#6

还可以考虑启用Trace flag 610,减少LOg的写入。
Trace Flag 610

SQL Server 2008 introduces trace flag 610, which controls minimally logged inserts into indexed tables. The trace flag can be turned on by using one of the following methods;
•Adding to the SQL Server startup parameters. ◦For more information, see (http://msdn.microsoft.com/en-us/library/ms345416.aspx) in SQL Server Books Online.

•Running ◦This enables the trace flag for a specific session. This is useful if you want to enable 610 for only a subset of load scenarios on the instance, and it applies only to the Transact-SQL connection that issues it.
◦Use turns on the trace flag for all connections to the server until it is turned off or until the next server restart.
◦For more information about using DBCC to enable trace flags, see (http://msdn.microsoft.com/en-us/library/ms187329.aspx) in SQL Server Books Online.

#7

我刚刚都试验了一下，感觉效率都差不多。。。。

#8

try this,

create table ph
(ID int identity(1,1), title char(50))

create table #t
(ID int, title varchar(50))

set nocount on

declare @x int
select @x=1
while(@x<=5000)
begin
 insert into #t(ID,title) values(@x,'title')
 select @x=@x+1
end

select @x=0
while(@x<=1000000000)
begin
 insert into ph(title) with(tablock)
  select title+rtrim(ID+@x) from #t
 
 select @x=@x+5000
end

set nocount off

#9

sorry! 8楼代码有bug,修正一下,

create table ph
(ID int identity(1,1), title char(50))

create table #t
(ID int, title varchar(50))

set nocount on

declare @x int
select @x=1
while(@x<=5000)
begin
 insert into #t(ID,title) values(@x,'title')
 select @x=@x+1
end

select @x=0
while(@x<1000000000)
begin
 insert into ph with (tablock)
  select title+rtrim(ID+@x) from #t
 
 select @x=@x+5000
end

set nocount off

#10

CREATE TABLE tb(ID INT IDENTITY(1,1),title VARCHAR(20));
GO

DECLARE @i INT = 1
WHILE @i <= 1000
BEGIN 
    INSERT INTO tb
    SELECT 'title1' 
    FROM   (
SELECT X.NUMBER * 2048 + Y.NUMBER AS N
FROM   (
 SELECT DISTINCT     NUMBER
 FROM   MASTER.dbo.spt_values
 WHERE  NAME IS      NULL
  ) X
  JOIN (
 SELECT DISTINCT NUMBER
 FROM   MASTER.dbo.spt_values
 WHERE  NAME IS NULL
  ) Y
  ON  1 = 1
 ) a
    WHERE  n <= 1000000 AND n > 0
    set @i = @i + 1
END
循环还是应该多开几个线程来跑

 

#11

感谢各位了。。

#12

看楼主的意思，不是要一条条去插，然后测试插入到10亿条的性能，而是要将10亿条数据分为多个1百万条数据去插入，个人觉得用BCP的方式插入，每次插入1百万条比较好。两种方案
方案1：先建立一个表，然后写1百万条数据进去，再导出为.txt文件，再将这个文件多次导入
方案2：楼主会c#或者其它语言，c#是有这个BCP导入的类，可以用其进行导入

#13

搞个有100000的表，cross join一下就可以了，

#14

为什么不用SQLSERVER自带的作业来做这件事？