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

ufo20020427 2013-11-14 08:30:19
有一几百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多分钟,太久了,不满足业务要求。


请问该如何实现(关键时速度要快)。谢谢!
...全文
281 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复
引用 6 楼 ufo20020427 的回复:
再请教下,之前我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' 好像不行
试试这样,导出格式文件时,还是逗号,导出格式文件后,打开.fmt的问题,把逗号 替换成~
ufo20020427 2013-11-14
  • 打赏
  • 举报
回复
再请教下,之前我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' 好像不行
  • 打赏
  • 举报
回复
对了上面导出的格式文件,分隔符是逗号,你要导入的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
  • 打赏
  • 举报
回复
一个例子:

--修改高级参数
sp_configure 'show advanced options',1
go

--允许即席分布式查询
sp_configure 'Ad Hoc Distributed Queries',1
go

--如果配置的值不在合理范围(在最小值最大值范围内),那么可以强制覆盖
reconfigure with override
go

sp_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
  • 打赏
  • 举报
回复
exec xp_cmdshell 'bcp 库.dbo.表 format nul -f c:\wc.fmt -c -Usa -P密码 -S ip,端口' go
ufo20020427 2013-11-14
  • 打赏
  • 举报
回复
@yupeigu 请问c:\wc.fmt如何得到 ?
  • 打赏
  • 举报
回复
别用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   

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧