Bulk批量插入时格式文件的生成方法,及execl表导入及相关

最新推荐文章于 2023-03-09 16:46:50 发布

原创最新推荐文章于 2023-03-09 16:46:50 发布 · 1.2k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sqlserver #properties #sql #go #xp

sql 专栏收录该内容

31 篇文章

订阅专栏

本文详细介绍了在SQL Server中如何使用BCP命令生成格式文件以进行批量插入，以及解决在执行过程中遇到的安全问题和格式配置。同时，文章还探讨了Excel数据导入数据库时的空值处理和数据类型转换问题，以及64位系统下的兼容性挑战。

写这文章推荐给编辑是因为,网上很多资料是错误的,不全面的,光看资料是没有办法解决问题的.

SELECT hotelid into #tempHotelC FROM OPENROWSET(Bulk 'd:/a/hotelidc.txt',FORMATFILE='d:/a/format.txt') AS b

这个FORMATFILE是怎么出来的,怎么写的呢?

可以自己写,但也不知道格式是什么,网上说有一个BCP的,BCP是什么呢,他就是个命令,没有界面的,可以在cmd里面执行,我们也可以通过

exec master..xp_cmdshell 在查询分析器里面执行这个命令

现在问题来了,sql报安全问题说无法执行 exec master..xp_cmdshell ,说可以通过sp_configure来设置,使支持master..xp_cmdshell

命令如下

EXEC sp_configure 'show advanced options', 1

　　-- 重新配置

RECONFIGURE

　　-- 启用xp_cmdshell

EXEC sp_configure 'xp_cmdshell', 1

　　--重新配置

RECONFIGURE

这是我改过的,网上大部分都写错了,真是害死人,EXEC sp_configure 'xp_cmdshell', 1 为启用,0为禁止,而网上写的刚好是反的

现在我们来生成格式文件,这是我试过最后可以成功的

exec master..xp_cmdshell 'BCP test.dbo.Table_1 format nul -U sa -P 123456 -S EBJ1557/SQLEXPRESS -f D:/aaa/aaa.fmt -c -t'

这破语句,网上有很多,包括msdn,不过全是少参数的,你根本就无法执行

就这样,你认为可以执行成功了吗?错

首先你要新建一个D:/aaa/aaa.fmt文件,这条命令不会帮你新建的,网上也不说,如果你文件夹权限不够也没有用,最好给个everyone权限

最后,你才成生成格式文件

这种方法生成的格式文件是

1       SQLCHAR       0       100     ""       1     hotel_id                     Chinese_PRC_CI_AS
2       SQLCHAR       0       12      ""       2     audit                        ""
3       SQLCHAR       0       12      "/r/n"   3     auditingType                 ""

如果你想把列分格符改为tab(制表符)就改一下文本就行了

1       SQLCHAR       0       100     "/t"       1     hotel_id                     Chinese_PRC_CI_AS
2       SQLCHAR       0       12      "/t"       2     audit                        ""
3       SQLCHAR       0       12      "/r/n"   3     auditingType                 ""

另外我们发现前面的 SQLCHAR 我把他改成SQLINT也没用,不知道怎么改成别的格式,这样的话,导入的数据全是字符型的,有时候你能需要类型转换一下

现在我们来看看execl导入数据库,首先有一个null,对空字符串问题,就是execl里面的格子没写的话,导入sqlserver为成为null,如果不允许空就麻烦了

如果大公司,dba是不会理你这种需求的,你一般要写sql

SELECT identity(int,1,1) id,*
into #temp
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source="d:/aaa/日审酒店数据-分时传真维护列表201008.xls";Extended Properties=Excel 8.0')...[abc$]

abc是execl的左下角表名,这里也还有问题,就是比如0:00这种字符串,execl会把他变成时间格式,要注意

但是64位机器是不支持'Microsoft.Jet.OLEDB.4.0'的,所以你要先找台32位机子,再进行sql对sql的导入,相当麻烦