如何快速插入上亿条记录

Question

insert into t2
(id,c2,c3)
select 
(id,c2,c3)
from tSQL语句简单， 但此表(t) 非常庞大， 记录条数是2亿多条， 查看这个表的大小有七十多个GB。试着一次性插入， 结果导致了 tempdb 的被爆满， 提示空间不够(tempdb目前大小10GB左右)。 
不过其它几千万数据的插入， 不出错也是并不快， 22534250条的数据插入也执行了20062秒。后来写了个存储过程， 分批插入， 每次插入1百万条左右。 速度还可以吧。看看网上的介绍， Orcle 有分批插入的功能， 有点好奇地问问：
SQL Server 有没有类似的功能， 或者说简单点的办法来实现呢？快速插入 sqlserver

Answer 1

哥， bcp 只是在导入文件时才能用吧

Answer 2

2亿的记录，不是小数据量了。分批次，每次几十万笔
若无特殊需求，将日志设为SIMPLE

Answer 3

表要做分区，然后才有“可能”并行插入，如果你要借助tempdb，那它的大小和IO要满足要求。尽量减少文件因大小不足而增长，如果T和T2都做了分区，那用switch分区切换，听说可以秒杀百万级别的数据，不过具体还是要测试。

Answer 4

--#1.手工处理：BCP导出，然后再BCP导入。
--#2.快照复制：如果是一次性的，建议用快照复制，做一下同步即可。

Answer 5

如果是同一个服务器上，那么如果用bcp导出数据，再导入数据，效率可能还不如分批次的导入效率好，本来bcp的效率是挺高的，但是先导出，再导入，就得花2倍时间。我觉得上面提到的分区表，应该是一个好办法，但是分区表在把某个表的数据转移到其他表的时候，那么这个分区的数据就没有了，而上面的意思只是插入，也就是原来的表的数据还在，只是把原表的数据插入到另一个表。所以，我在想，一个表的数据，而且是大量的数据，为什么要插入到另一个表中，是否一定要这么做呢？

Answer 6

原来的表(t)没有做分区， 目的就是改成分区表， 步骤如下:
1. 建新表(t2)；
2. 将旧表t数据插入到新表t2;
3. 将旧表删除;
4. 重建约束和索引;其实事情已经做完了， 2亿7千万条数据存储过程分批插入大概3小时完成， 只是探索一下：
有无更好更方便的方式来做这个事情

Answer 7

原来的表(t)没有做分区， 目的就是改成分区表， 步骤如下:
1. 建新表(t2)；
2. 将旧表t数据插入到新表t2;
3. 将旧表删除;
4. 重建约束和索引;其实事情已经做完了， 2亿7千万条数据存储过程分批插入大概3小时完成， 只是探索一下：
有无更好更方便的方式来做这个事情

 
哦，改成分区表不用把原来的表删掉，直接在原表上修改成分区表就可以了，这样可能会更快。

Answer 8

原来的表(t)没有做分区， 目的就是改成分区表， 步骤如下:
1. 建新表(t2)；
2. 将旧表t数据插入到新表t2;
3. 将旧表删除;
4. 重建约束和索引;其实事情已经做完了， 2亿7千万条数据存储过程分批插入大概3小时完成， 只是探索一下：
有无更好更方便的方式来做这个事情

 
哦，改成分区表不用把原来的表删掉，直接在原表上修改成分区表就可以了，这样可能会更快。来个具体的代码吧……

Answer 9

在表已经存在的情况下，构建分区表，通过删除主键约束，重建主键约束，同时按照分区架构来建立聚集索引。当然，如果表本来就没有主键，只有一个聚集索引，那么可以直接删除聚集索引，然后在重建聚集索引时指定分区架构，其实就是索引分区，只不过这个索引里存储的是真正的表的数据。链接： http://blog.csdn.net/yupeigu/article/details/7754115代码：
--1.创建数据库
create database wc
on primary
(
name = wc_data,
filename = 'D:\wc_data.mdf'
)
log on
(
name = wc_log1,
filename = 'd:\wc_log1.ldf'  
),
(
name = wc_log2,
filename = 'd:\wc_log2.ldf'
)
--2.增加文件组
alter database wc
add filegroup wc_fg1alter database wc
add filegroup wc_fg2alter database wc
add filegroup wc_fg3alter database wc
add filegroup wc_fg4--3.把文件添加到文件组中
alter database wc
add file
(
name = wc_fg1_1,
filename = 'd:\wc_fg1_1.ndf',
size = 1MB
)
to filegroup wc_fg1alter database wc
add file
(
name = wc_fg2_1,
filename = 'd:\wc_fg2_1.ndf',
size = 1MB
)
to filegroup wc_fg2alter database wc
add file
(
name = wc_fg3_1,
filename = 'd:\wc_fg3_1.ndf',
size = 1MB
)
to filegroup wc_fg3alter database wc
add file
(
name = wc_fg4_1,
filename = 'd:\wc_fg4_1.ndf',
size = 1MB
)
to filegroup wc_fg4
--4.创建分区函数
use wc
gocreate partition function wcLeftRange(datetime)
as range left for values('2006-01-01','2007-01-01','2008-01-01')create partition function wcRightRange(datetime)
as range right for values('2006-01-01','2007-01-01','2008-01-01')
--5.创建分区方案
create partition scheme wcLeftRangeScheme
as partition wcLeftRange
to (wc_fg1,wc_fg2,wc_fg3,wc_fg4)--下面的代码非常重要--6.1创建表,下面是把已经存在的表改为分区表,其实分区表说到底就是对聚集索引的分区,
--所以只要重建主键索引就可以了
create table dbo.wcT
(wcId bigint not null,
 wcV varchar(100) not null ,
 wcDate datetime not null,
 
 constraint pk_wcid_date   --主键约束
 primary key(wcId,wcDate)  
)--6.2添加数据
insert into dbo.wcT(wcId,wcV,wcDate)
values(1,'2','2006-01-01 00:00:00'),
      (2,'1','2005-12-31 23:59:59'),
      (3,'2','2006-12-31 23:59:59'),
      (4,'3','2007-01-01 00:00:00'),
      (5,'4','2008-01-01 00:00:00'),
      (6,'4','2008-12-31 23:59:59')
--6.3现在需要把原表按照一个分区架构来分区
--6.3.1如果这么删除,会报错,因为这个索引正用于 PRIMARY KEY 约束的强制执行
drop index pk_wcid_date on dbo.wcT
--6.5.2重建聚集索引,操作失败，因为表'wcT'上已存在名称为'pk_wcid_date'的索引或统计信息。
create clustered index pk_wcid_date on wcT(wcId,wcDate) 
on wcLeftRangeScheme(wcDate)
--6.3.3正确的方法：首先需要删除主键约束,应该这么写才是对的
alter table dbo.wcT
drop constraint pk_wcid_date
--6.3.4再次建立主键约束,指定创建聚集索引,同时指定分区架构
alter table dbo.wcT
add constraint pk_wcid_date primary key clustered (wcId,wcDate) 
on wcLeftRangeScheme(wcDate)
--7.显示每条数据所属分区号,从1开始计算
select *,       --$partition函数,后面是分区函数名称,列名称
       $partition.wcLeftRange(wcDate) as partition 
from wcT

Answer 10

好吧，其实我觉得用自带的ssis来做比较好。或者，导入导出功能