请教BULK INSERT时，如何插入额外的列

Question

有一几百M文本文件 test.txt (数据量约1千万条)，里面有N列（这时假设Name,Score两列），
现要求快速保存到数据表 User中（Id, BusinessDate, Name, Score)
其中Id 是 newId()得到的， BusinessDate通过参数传进去。但是目前Bulk Insert好像无法满足需求，该怎么办。我试过建一临时表TempUser(Name,Socre)，Bulk Insert时很快（1分多钟）
但是从TempUser插入到User中,则约10多分钟，太久了，不满足业务要求。
请问该如何实现（关键时速度要快）。谢谢！

Answer 1

别用bulk insert，用下面的方法：不过需要先建立一个格式文件.fmt文件：insert into 你的表(列)
select newid(),*  
from   
openrowset(bulk 'c:\wc.txt',             --要读取的文件路径和名称   
                formatfile='c:\wc.fmt',  --格式化文件的路径和名称  
                  
                firstrow = 2,            --要载入的第一行,由于第一行是标题,所以从2开始  
                --lastrow  = 1000,       --要载入的最后一行,此值必须大于firstrow  
                  
                maxerrors = 10,          --在加载失败之前加载操作中最大的错误数  
                --errorfile ='c:\wc_error1.txt', --存放错误的文件  
                  
                rows_per_batch = 10000                    --每个批处理导入的行数  
          ) as t   

Answer 2

@yupeigu 
请问c:\wc.fmt如何得到 ？

Answer 3

exec xp_cmdshell 'bcp 库.dbo.表 format nul  -f c:\wc.fmt -c -Usa -P密码 -S ip,端口'  
go 

Answer 4

一个例子：--修改高级参数
sp_configure 'show advanced options',1
go--允许即席分布式查询
sp_configure 'Ad Hoc Distributed Queries',1
go--如果配置的值不在合理范围（在最小值最大值范围内），那么可以强制覆盖
reconfigure with override  
gosp_configure 'xp_cmdshell',1
go
reconfigure
go
--创建数据库
create database wc 
go
use wc
go--建表
create table xxdd
(
aa nvarchar(1000),
bb nvarchar(1000),
cc nvarchar(1000),
dd nvarchar(1000),
ee nvarchar(1000),
ff nvarchar(1000)
)
go
/* 这里建立一个c:\wc.csv  文件,内容如下：
aa,bb,cc,dd,ee,ff
42222222223432432432,32432432432432432432,2332432432,32432432432,32432432,23432432
42222222223432432432,32432432432432432432,2332432432,32432432432,32432432,23432432
42222222223432432432,32432432432432432432,2332432432,32432432432,32432432,23432432
42222222223432432432,32432432432432432432,2332432432,32432432432,32432432,23432432*/--导出格式文件，这个是关键，数据库名称，表名称，用户名和密码，服务器ip和端口
--都改成你自己的
exec xp_cmdshell 'bcp wc.dbo.xxdd format nul -t "," -f c:\wc.fmt -c -Usa -Pyupeigu -S 192.168.1.106,1433'
go
--先查看要导入的数据
select *
from 
openrowset(bulk 'c:\wc.csv',             --要读取的文件路径和名称 
                formatfile='c:\wc.fmt',  --格式化文件的路径和名称
                
                firstrow = 2,            --要载入的第一行,由于第一行是标题,所以从2开始
                --lastrow  = 1000,       --要载入的最后一行,此值必须大于firstrow
                
                maxerrors = 10,          --在加载失败之前加载操作中最大的错误数
                --errorfile ='c:\wc_error1.txt', --存放错误的文件
                
                rows_per_batch = 10000                    --每个批处理导入的行数
          ) as t /*
aa bb cc dd ee ff
42222222223432432432 32432432432432432432 2332432432 32432432432 32432432 23432432
42222222223432432432 32432432432432432432 2332432432 32432432432 32432432 23432432
42222222223432432432 32432432432432432432 2332432432 32432432432 32432432 23432432
42222222223432432432 32432432432432432432 2332432432 32432432432 32432432 23432432
*/--最后可以 insert into 表 (列)  select * from openrowset...插入数据即可
insert into xxdd (aa,bb,cc,dd,ee,ff)
select *
from 
openrowset(bulk 'c:\wc.csv',             --要读取的文件路径和名称 
                formatfile='c:\wc.fmt',  --格式化文件的路径和名称
                
                firstrow = 2,            --要载入的第一行,由于第一行是标题,所以从2开始
                --lastrow  = 1000,       --要载入的最后一行,此值必须大于firstrow
                
                maxerrors = 10,          --在加载失败之前加载操作中最大的错误数
                --errorfile ='c:\wc_error1.txt', --存放错误的文件
                
                rows_per_batch = 10000                    --每个批处理导入的行数
          ) as t 
select *
from xxdd   

Answer 5

对了上面导出的格式文件，分隔符是逗号，你要导入的txt文件，把这个去掉 -t ","exec xp_cmdshell 'bcp wc.dbo.xxdd format nul  -f c:\wc.fmt -c -Usa -Pyupeigu -S 192.168.1.106,1433'
go

Answer 6

再请教下，之前我bulk insert时BULK INSERT BILL_UserBill_Temp
   FROM 'f:\test.txt'
   WITH
      (
         FIELDTERMINATOR = '~',
         ROWTERMINATOR = '0x0a'
      )那么我这里
exec xp_cmdshell 'bcp NoMasterAndBill.dbo.BILL_UserBill_Temp format nul -t "~" -f f:\BILL_UserBill.fmt -c -Usa -Pabc123 -S 127.0.0.1,1433'  
好像不行

Answer 7

试试这样，导出格式文件时，还是逗号，导出格式文件后，打开.fmt的问题，把逗号 替换成~