--==============================================
作者:王运亮(wwwwgou)
时间:2011-06-08
博客:http://blog.csdn.net/wwwwgou
--==============================================
--开启导入配置
sp_configure 'show advanced options',1
RECONFIGURE WITH override
GO
sp_configure 'Ad Hoc Distributed Queries',1 --1:启用(默认) 0:禁用
RECONFIGURE WITH override
GO
EXEC sp_configure; --显示所有配置信息
GO
--导入(例子中需要加上insert into/into语句)
--#1.直接引用表名即可
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/test.xls')...[test$]
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/test.xls',[test$])
--#2.数字或不规则表名需要加'限定
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/test.xls')...['Auto Add$']
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/test.xls',['3$'])
--#3.用这种方式可免除#2中的限定
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/test.xls','select * from [3$]')
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:/test.xls','select * from [Auto Add$]')
GO
--#4.BCP导入(文本文件)
EXEC master..xp_cmdshell 'bcp TEST.dbo.TEST in D:/test.txt -c -F 10 -L 13 -T'
EXEC master..xp_cmdshell 'bcp TEST.dbo.TEST in D:/test.txt -F 10 -L 13 -c -x -f c:/test.xml -T'
GO
--开启导出配置
sp_configure 'show advanced options',1
RECONFIGURE WITH override
GO
sp_configure 'xp_cmdshell',1 -- 1:启用(默认) 0:禁用
RECONFIGURE WITH override
GO
EXEC sp_configure;
GO
--导出
--#1.使用OPENROWSET
INSERT INTO OPENROWSET('MICROSOFT.JET.OLEDB.4.0','excel 8.0; HDR=YES;database=D:/test.xls',['3$'])
(
[level 1],
[level 2]
)
SELECT
'x',
'y'
--#2.BCP导出.注意: 用BCP导出的是类Excel文件,其实质为文本文件
--#2.1 BCP带连接信息导出(覆盖生成xls文件)
EXEC master..xp_cmdshell 'bcp Test.dbo.B out "D:/test.xls" -c -S"实例名" -U"sa" -P"1q2w3e4R"'
--#2.2 BCP使用默认安全连接导出(覆盖生成xls文件)
EXEC master..xp_cmdshell 'bcp Test.dbo.B out "D:/test1.xls" -c -T'
--#2.3 BCP带查询语句导出(覆盖生成xls文件)
EXEC master..xp_cmdshell 'bcp "SELECT TOP(1) * FROM Test.dbo.B order by code" queryout "D:/test2.xls" /c -S"实例名" -U"sa" -P"1q2w3e4R"'
--#2.4
EXEC master..xp_cmdshell 'bcp Test.dbo.B format nul -f D:/test.xml -x -c -T'
GO
/*
BCP共有四个动作可以选择。
(1) 导入 : 这个动作使用in命令完成,后面跟需要导入的文件名。
(2) 导出 : 这个动作使用out命令完成,后面跟需要导出的文件名。
(3) 使用SQL语句导出 : 这个动作使用queryout命令完成,它跟out类似,只是数据源不是表或视图名,而是SQL语句。
(4) 导出格式文件 : 这个动作使用format命令完成,后而跟格式文件名。
常用参数介绍:
-f : format_file表示格式文件名。这个选项依赖于上述的动作,如果使用的是in或out,format_file表示已经存在的格式文件,如果使用的是format则表示是要生成的格式文件。
-x : 这个选项要和-f format_file配合使用,以便生成xml格式的格式文件。
-F : first_row指定从被导出表的哪一行导出,或从被导入文件的哪一行导入。
-L : last_row指定被导出表要导到哪一行结束,或从被导入文件导数据时,导到哪一行结束。
-c : 使用char类型做为存储类型,没有前缀且以"/t"做为字段分割符,以"/n"做为行分割符。
-w : 和-c类似,只是当使用Unicode字符集拷贝数据时使用,且以nchar做为存储类型。
-t : field_term指定字符分割符,默认是"/t"。
-r : row_term指定行分割符,默认是"/n"。
-S : server_name指定要连接的SQL Server服务器的实例,如果未指定此选项,BCP连接本机的SQL Server默认实例。如果要连接某台机器上的默认实例,只需要指定机器名即可。
-U : login_id指定连接SQL Sever的用户名。
-P : password指定连接SQL Server的用户名密码。
-T : 指定BCP使用信任连接登录SQL Server。如果未指定-T,必须指定-U和-P。
-k : 指定空列使用null值插入,而不是这列的默认值。
*/