SqlServer数据导入导出 之BCP
|
BCP是SQL Server中负责导入导出数据的一个命令行工具，它是基于DB-Library的，并且能以并行的方式高效地导入导出大批量的数据 开启cmdshell的SQL语句 EXEC sp_addextendedproc xp_cmdshell,@dllname ='xplog70.dll' 判断存储扩展是否存在 恢复xp_cmdshell 否则上传xplog7.0.dll ------------------------------------------------------------------------------- 1）cmd命令行执行 bcp "库名.dbo.表明" out?目标路径 -c -T 回车 就开始复制，目标路径生成文件 OK 2） 通过调用SQL Server的一个系统存储过程xp_cmdshell以SQL语句的方式运行BCP。 如：EXEC master..xp_cmdshell 'BCP NTS.dbo.T_User out c:User.txt -c -U"sa" -P"password"' 上面的sql语句用在c#代码中， string sql = "EXEC master..xp_cmdshell 'BCP CITDatabase.dbo.PersonSurname out e:abc.xls -c -T'"; SqlCommand cmdFrom = new SqlCommand(sql,conn); 弹出错误提示： SQL Server 阻止了对组件 'xp_cmdshell' 的 过程'sys.xp_cmdshell' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'xp_cmdshell'。有关启用 'xp_cmdshell' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。 可以通过使用外围应用配置器工具或运行??? sp_configure??? 系统存储过程来启用它 “Microsoft SQL Server 2005”、“配置工具”，再单击“SQL Server 外围应用配置器”。单击“配置外围应用”旁边的链接。默认值为 localhost。如果您以前选择的是一个命名服务器，则将会看到该服务器名称。选择“功能的外围应用配置器”，启用'xp_cmdshell'选项打勾即可。 1.2运行??? sp_configure??? 系统存储过程来启用它，如下面的代码示例所示： ALTER?? PROCEDURE?? dbo.dcxls?? AS
?????? ? SQLState = HY000 NativeError =0? Error = [Microsoft][SQL Native Client]无法打开 BCP 主数据文件??<NULL>?? ? 网上提示说 1）这是因为exec ? master..xp_cmdshell是一个SQL语句,这个SQL语句是在SQL服务器上执行的,所以对应的文件也应该是放在SQL服务器,如果你指定的是本机的文件,则出现此问题. 解决：e:abc.xls?改为?C:Program FilesMicrosoft SQL Server 错误：??????命令行中的参数 'FilesMicrosoft' 未知? 解决：用“” 2）只有sysadmin用户才有xp_cmdshell权限，可能是这个原因。 ?????????? 服务中sqlserver登录用户为NT AUTHORITYNetworkService，sqlserver安全性中赋给该用户sysadmin服务器角色 怎么还不行啊？？？ ??????????????????????????? 1. 四个动作 01. 导入：这个动作使用IN命令完成，后面跟需要导入的文件名 02. 导出：这个动作使用OUT命令完成，后面跟需要导出的文件名，数据源是表或者视图 03. 使用SQL语句导出：这个动作使用QueryOut命令完成，跟OUT类似，数据源是SQL语句 04. 导出格式文件：这个动作使用Format命令完成，后而跟格式文件名 2. 常用的选项 01. -f Format_File Format_File表示格式文件名。这个选项依赖于上述的动作，如果使用的是IN或OUT，Format_File表示已经存在的格式文件，如果使用的是Format则表示是要生成的格式文件 02. -x 这个选项要和-f Format_File配合使用，以便生成XML格式的格式文件 03. -F First_Row 指定从被导出表的哪一行导出，或从被导入文件的哪一行导入 04. -L last_Row 指定被导出表要导到哪一行结束，或从被导入文件导数据时，导到哪一行结束 05. -c 使用Char类型做为存储类型，没有前缀且以"t"做为字段分割符，以"n"做为行分割符 06. -w 和-c类似，只是当使用Unicode字符集拷贝数据时使用，且以nChar做为存储类型 07. -t Field_Term 指定字符分割符，默认是"t" 08. -r Row_Term 指定行分割符，默认是"n" 09. -S Server_Name[ Instance_Name] 指定要连接的SQL Server服务器的实例，如果未指定此选项，BCP连接本机的SQL Server默认实例。如果要连接某台机器上的默认实例，只需要指定机器名即可 10. -U Login_ID 指定连接SQL Sever的用户名 11. -P Password 指定连接SQL Server的用户名密码 12. -T 指定BCP使用信任连接登录SQL Server。如果未指定-T，必须指定-U和-P 13. -k 指定空列使用NULL值插入，而不是这列的默认值 3. 数据导出 01. 导出整个表或视图 BCP NTS.dbo.T_User out c:User.txt -c -U"sa" -P"password" --使用密码连接（用户名和密码需要加上双引号） BCP NTS.dbo.T_User out c:User.txt -c -T --使用信任连接 02. 导出SQL语句的目标数据 BCP "SELECT TOP 20 * FROM NTS.dbo.T_User" queryout c:User.txt -c -U"sa" -P"password" BCP "SELECT TOP 20 * FROM NTS.dbo.T_User" queryout c:User.txt -F 10 -L 13 -c -U"sa" -P"password" --导出第10条到13条记录 03. 导出格式文件 BCP NTS.dbo.T_User format nul -f c:User_format1.fmt -c -T --把表结构信息导出到User_format1.fmt中 BCP NTS.dbo.T_User format nul -f c:User_format2.fmt -x -c -T --把表结构信息导出XML中 04. 导入数据 在导入数据时可以根据已经存在的格式文件将满足条件的记录导入到数据库中，不满足则不导入。如上述的格式文件中的第三个字段的字符长度是24，如果某个文本文件中的相应字段的长度超过24，则这条记录将不被导入到数据库中，其它满足条件的记录正常导入 BCP NTS.dbo.T_User in c:User1.txt -c -T BCP NTS.dbo.T_User in c:User1.txt -c -F 10 -L 13 -T --导入时也可使用-F和-L选项来选择目标数据 BCP NTS.dbo.T_User in c:User1.txt -F 10 -L 13 -c -f c:User_format1.fmt -T --使用普通的格式文件 BCP NTS.dbo.T_User in c:User1.txt -F 10 -L 13 -c -x -f c:User_format2.fmt -T --使用XML格式的格式文件 05. 实际导出的例子 EXEC master..xp_cmdshell 'BCP SZC.dbo.T_User out c:test1.csv -c -U"sa" -P"qwert1"' --使用密码连接 EXEC master..xp_cmdshell 'BCP SZC.dbo.T_User out c:test1.csv -c -T' --使用信任连接 4. 其他方法导入导出数据 01. 导入数据 SELECT * INTO authors1 FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.1.1.2; UserID=sa; Password=qwert1').pubs.dbo.authors 02. 导出数据 INSERT INTO OPENDATASOURCE('SQLOLEDB','Data Source=192.1.1.2; UserID=sa; Password=qwert1').test.dbo.authors SELECT * FROM pubs.dbo.authors INSERT INTO OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','Text;DATABASE=c:&;apos;)[data#txt] SELECT * FROM text1 INSERT INTO OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;DATABASE=c:&;apos;,[data#txt]) SELECT * FROM text1 03. 导入Excel数据 SELECT * INTO excel FROM OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;DATABASE=c:book1.xls' )[Sheet1$] 04. 导入到Excel INSERT INTO OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;DATABASE=c:book1.xls' )[Sheet1$] SELECT * FROM excel ? 很多大型的系统不仅仅提供了友好的图形用户接口，同时也提供了命令行方式对系统进行控制。在SQL Server中除了可以使用SQL语句对数据进行操作外，还可以使用一个命令行工具BCP对数据进行同样的操作。BCP是基于DB-Library 客户端库的工具。它的功能十分强大，BCP能够以并行方式将数据从多个客户端大容量复制到单个表中，从而大大提高了装载效率。但在执行并行操作时要注意的是只有使用基于 ODBC 或 SQL OLE DB 的 API 的应用程序才可以执行将数据并行装载到单个表中的操作。 BCP可以将SQL Server中的数据导出到任何OLE DB所支持的数据库的，如下面的语句是将authors表导出到excel文件中。 bcp pubs.dbo.authors out c:temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P"password" BCP不仅能够通过命令行执行，同时也可以通过SQL执行，这需要一个系统存储过程xp_cmdshell来实现，如上面的命令可改写为如下形式。 EXEC master..xp_cmdshell 'bcp pubs.dbo.authors out c:temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P"password"' ? ???? BCP命令是Sybase和MS ? SQL ? Server用来备份和恢复数据用的工具，它使用方便，备份/恢复速度快。当Table过多时，编写批处理是一件繁琐的事情。这时候就该用BCP了。 http://dazuiyuloveapple.i.sohu.com/blog/view/137902907.htm （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |