SQL server 数据导入导出BCP工具使用详解

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:     数据的导入导出是数据库管理员常见的工作任务之一,尤其是平面文件的导入导出。BCP 工具则为这些任务提供了强有力的支持,它是基于DB-Library,尤其是在生产环境中,从本地传送数据到服务器或从服务器传送数据到本地,因它无需提供图形界面,减少网络带宽,提高了传输速率。

    数据的导入导出是数据库管理员常见的工作任务之一,尤其是平面文件的导入导出。BCP 工具则为这些任务提供了强有力的支持,它是基于DB-Library,尤其是在生产环境中,从本地传送数据到服务器或从服务器传送数据到本地,因它无需提供图形界面,减少网络带宽,提高了传输速率。BCP的全称是BULK COPY PROGRAM,它是一个命令行程序,可以完全脱离SQL server进程来实现。


    常用的导入方式:bcp, BULK INSERT,OPENROWSET,or SSIS。

 

    本文主要介绍bcp工具的使用,其他的如BULK INSERT,OPENROWSET,or SSIS请参见后续博文。


    bcp的使用:可以在SQL Server 2005 实例和用户指定格式的数据文件间实现大容量复制数据,可以将平面文件导入到SQL server表,也可以将SQL server表导出为文件。该命令为一个DOS命令,通常位于x:/Program Files/Microsoft SQL Server/90/Tools/Bin目录下,可以在命令提示符下使用。

 

    以下简要列出其语法:
    语法:bcp {[[database_name.][owner].]{table_name | view_name} | "query"}     --指定相应的数据库名,表名,视图名或SQL查询语句,查询语句使用双引号括起来。


     {in | out | queryout | format} data_file         --数据流动的方向,in导入,out导出,queryout结果集 及指定data_file文件。
     [-mmax_errors] [-fformat_file] [-x] [-eerr_file]                         --bcp的错误处理选项
      [-Ffirst_row] [-Llast_row] [-bbatch_size]                                --可以指定特定行数
      [-n] [-c] [-N] [-w] [-V (60 | 65 | 70 | 80)] [-6]                        --有关字符编码选项,通常很少使用
      [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term] 
      [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
      [-Sserver_name[/instance_name]] [-Ulogin_id] [-Ppassword]               --指定登陆的服务器名、实例名及登陆账户密码。
      [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]                             --hint提示使用TABLOCK或CHECK_CONSTRAINTS,FIRE_TRIGGERS 等通常用于支持最小日志记录

    几个常用的参数:-f format_file

    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[ /instance_name]    指定要连接的SQL Server服务器的实例,如果未指定此选项,bcp连接本机的SQL Server默认实例。如果要连接某台机器上的默认实例,只需要指定机器名即可。

    -U login_id       指定连接SQL Sever的用户名。

    -P password    指定连接SQL Server的用户名密码。

    -T                     指定bcp使用信任连接登录SQL Server。如果未指定-T,必须指定-U和-P。

    -k                     指定空列使用null值插入,而不是这列的默认值。

    权限:
    bcp out 操作要求对源表有 SELECT 权限。
    bcp in 操作要求至少对目标表有 SELECT/INSERT 权限。
    当被导入的表中存在CHECK约束和TRIGGER时,缺省的行为为关闭,不要指定-h 选项和 CHECK_CONSTRAINTS  以及FIRE_TRIGGERS 提示。故需要对表有Alter table 权限。


    一、bcp导出到平面文件
    1.将表复制到平面文件(使用信任连接,使用参数 -T),在命令提示符下输入以下语句

bcp AdventureWorks.Sales.SalesOrderHeader OUT d:/SalesOrders.txt -c -T     -- -T表示使用可信任的连接

Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
--.........省略
--.........省略
31465 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 454    Average : (69306.17 rows per sec.)

 

使用xp_cmdshell存储过程来执行bcp,鉴于演示的方便,后续的处理将全部采用xp_cmdshell来实现。
EXEC xp_cmdshell 'bcp AdventureWorks.Sales.SalesOrderHeader OUT d:/SalesOrders1.txt -c -T'

 

2.将表复制到平面文件(使用混合身份验证,使用参数 -U —P,例如:-U"Test" -P"Test" , -U后的用户名和-P后的密码加上双引号)
EXEC xp_cmdshell 'bcp AdventureWorks.Sales.SalesOrderHeader OUT d:/SalesOrders2.txt -c -U"Test" -P"Test"'

 

3.将指定的列或行复制到平面文件
EXEC xp_cmdshell    --导出指定的列 使用到了queryout
'bcp "SELECT SalesOrderID,OrderDate,SalesOrderNumber FROM AdventureWorks.Sales.SalesOrderHeader"  queryout d:/SalesOrders3.txt -c -U"Test" -P"Test" '

 

EXEC xp_cmdshell    --导出指定的行,查询结果中的第20到第40条记录, 使用到了queryout
'bcp "SELECT TOP(50) SalesOrderID,OrderDate,SalesOrderNumber FROM AdventureWorks.Sales.SalesOrderHeader WHERE OrderDate >= ''20040101''"  queryout d:/SalesOrders4.txt -c -F 20 -L 40 -T '

 

二、bcp导出格式文件
    格式文件分为一般格式文件和XML格式文件,以下示例将SalesOrderHeader表的格式形成一个一般格式文件,也称为非XML 格式化文件。
EXEC xp_cmdshell 'bcp AdventureWorks.sales.SalesOrderHeader format nul -f d:/SalesOrders_format.fmt -c -T  '  
----------------
9.0                
27           --字段总数,多出的字段被省略,以下分别给出了字段的序号,类型,长度,分隔符,字段名等信息。
1       SQLCHAR       0       12      "/t"     1     SalesOrderID                                     ""
2       SQLCHAR       0       5       "/t"     2     RevisionNumber                                   ""
3       SQLCHAR       0       24      "/t"     3     OrderDate                                        ""
4       SQLCHAR       0       24      "/t"     4     DueDate     

                                     ""
XML 格式化文件
EXEC xp_cmdshell 'bcp AdventureWorks.sales.SalesOrderHeader format nul  -x -f d:/SaOrders_format_x.xml -c -T  ' 
非XML格式化文件与XML格式化文件两者用不同的方式来描述原始表的结构,其实质是一样的。

 

bcp导入平面文件到数据库表
创建新表NewOrderHeader,然后将前面导出的数据导入到新表


SELECT * INTO NewOrderHeader FROM sales.SalesOrderHeader WHERE 1=2
EXEC [master]..xp_cmdshell 'bcp AdventureWorks..NewOrderHeader in d:/SalesOrders.txt -c -T'
SELECT * FROM NewOrderHeader

 

使用格式化文件实现bcp的大容量导入

TRUNCATE TABLE NewOrderHeader
EXEC [master]..xp_cmdshell 'bcp AdventureWorks..NewOrderHeader in d:/SalesOrders.txt -f d:/Currency.xml -F 2000 -L 4000 -c -T'
SELECT * FROM NewOrderHeader

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
2月前
|
SQL 存储 缓存
SQL Server 数据太多如何优化
11种优化方案供你参考,优化 SQL Server 数据库性能得从多个方面着手,包括硬件配置、数据库结构、查询优化、索引管理、分区分表、并行处理等。通过合理的索引、查询优化、数据分区等技术,可以在数据量增大时保持较好的性能。同时,定期进行数据库维护和清理,保证数据库高效运行。
|
3月前
|
SQL 移动开发 Oracle
SQL语句实现查询连续六天数据的方法与技巧
在数据库查询中,有时需要筛选出符合特定时间连续性条件的数据记录
|
3月前
|
SQL 数据可视化 安全
微软SQL Server可视化工具与技巧
微软SQL Server不仅提供了强大的数据库管理功能,还集成了多种可视化工具,帮助用户更直观地理解和管理数据
|
3月前
|
SQL 存储 关系型数据库
添加数据到数据库的SQL语句详解与实践技巧
在数据库管理中,添加数据是一个基本操作,它涉及到向表中插入新的记录
|
3月前
|
SQL 数据挖掘 数据库
SQL查询每秒的数据:技巧、方法与性能优化
id="">SQL查询功能详解 SQL(Structured Query Language,结构化查询语言)是一种专门用于与数据库进行沟通和操作的语言
|
3月前
|
SQL 关系型数据库 Shell
SQL整库导出语录及其实用技巧与方法
在数据库管理和备份恢复过程中,整库导出是一项至关重要的任务
|
3月前
|
SQL 监控 数据处理
SQL数据库数据修改操作详解
数据库是现代信息系统的重要组成部分,其中SQL(StructuredQueryLanguage)是管理和处理数据库的重要工具之一。在日常的业务运营过程中,数据的准确性和及时性对企业来说至关重要,这就需要掌握如何在数据库中正确地进行数据修改操作。本文将详细介绍在SQL数据库中如何修改数据,帮助读者更好
583 4
|
3月前
|
SQL Oracle 关系型数据库
SQL整库导出语录:全面解析与高效执行策略
在数据库管理和维护过程中,整库导出是一项常见的需求,无论是为了备份、迁移还是数据分析,掌握如何高效、准确地导出整个数据库至关重要
|
3月前
|
SQL 关系型数据库 MySQL
SQL批量插入测试数据的几种方法?
SQL批量插入测试数据的几种方法?
209 1
|
3月前
|
SQL 分布式计算 关系型数据库
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
124 0