017_bcp_bulk_openrowset
017_bcp_bulk_openrowset
--bcp*******************************************************************************
--1.可以用master..xp_cmdshell存储过程将CMD的命令传给系统，这样就可以直接在SQL Server的查询处理器中直接输入bcp的命令，而sqlserver默认禁用master..xp_cmdshell存储过程
--2.开启xp_cmdshell存储过程的方法（开启后有安全隐患）
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
--3.关闭xp_cmdshell存储过程的方法
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
--4.验证 master..xp_cmdshell存储过程是否可用
EXEC master..xp_cmdshell 'ipconfig'
--5.【queryout 用SQL语句导出 】
--将本地数据库中的表数据导出到本地 aaa.xls 【用sa/123 登陆到 localhost\liwz】
EXEC master..xp_cmdshell 'BCP "select * from test.dbo.aaa" queryout f:\aaa1.xls -c -S"localhost\liwz" -U"sa" -P"123"'
--导出数据到test_aaa.xls,使用-T信任连接
--通过-F 10 -L 13 指定导出的起始行和结束行数
--使用-T时，报错是因为没有对目录添加读写权限造成的，将输目录添加NETWORK SERVICE的读写权限即可
EXEC master..xp_cmdshell 'BCP "select * from test.dbo.aaa" queryout d:\test_aaa.xls -c -F 10 -L 13 -T"'
--6.【in 导入】使用in语句来将数据导入到数据表
EXEC master..xp_cmdshell 'BCP test.dbo.aaa in f:\aaa1.xls -c -S"localhost\liwz" -U"sa" -P"123"'
--7.【out 导出】使用out语句来将数据导出到数据表
EXEC master..xp_cmdshell 'BCP test.dbo.aaa out f:\aaa2.xls -c -S"localhost\liwz" -U"sa" -P"123"'
--8.【format 导出格式文件】--这里的nul必须存在，用于不是导出和导入数据的情况下
--①创建非XML格式化文件【bcp table_or_view format nul -f format_file_name 】
EXEC master..xp_cmdshell 'bcp test.dbo.aaa format nul -n -f f:\aaa_fmt2.fmt -S"localhost\liwz" -U"sa" -P"123"'
--②创建XML格式化文件【bcp table_or_view format nul -f format_file_name -x 】
EXEC master..xp_cmdshell 'BCP test.dbo.aaa format nul -c -f f:\aaa_fmt.xml -x -S"localhost\liwz" -U"sa" -P"123"'
--9.利用bcp格式化文件导入导出数据
--【依据aaa_fmt.xml格式文件，将test_aaa.txt中的数据导入到aaa表】
exec master..xp_cmdshell 'bcp test.dbo.aaa in f:\test_aaa.txt -f f:\aaa_fmt.Xml -T'
--【依据aaa_fmt.xml格式文件，将aaa表中的数据导出到test_aaa.txt】
--使用bcp导出数据文件 student.data
exec master..xp_cmdshell 'bcp test.dbo.aaa out f:\test_aaa.txt -f f:\aaa_fmt.Xml -T'
--BULK INSERT*******************************************************************************
--1.依据aaa_fmt.xml格式文件，将test_aaa.txt中的数据导入到aaa表
BULK INSERT test..aaa
FROM 'f:\aaa.txt'
WITH (FORMATFILE='f:\aaa_fmt.Xml');
--2.使用 bcp 交互指定终止符
--以下示例使用 bcp 命令大容量导入 test.txt 数据文件。 该命令与大容量导出命令使用相同的命令开关。 有关详细信息，请参阅本主题前面的“为大容量导出指定终止符”。
--在 Windows 命令提示符下输入：
bcp AdventureWorks..myDepartment in f:\test.txt -c -t , -r \n -T
--3.使用 BULK INSERT 交互指定终止符
--以下示例使用 BULK INSERT 语句大容量导入 Department-c-t.txt 数据文件，该语句使用了下表中所示的限定符。
BULK INSERT myDepartment FROM 'C:\myDepartment-c-t.txt'
WITH (
DATAFILETYPE = 'char', --指定将数据字段作为字符数据加载。
FIELDTERMINATOR = ',',--将逗号 (,) 指定为字段终止符。
ROWTERMINATOR = '\n' --指定行终止符作为换行符。
);
--OPENROWSET*******************************************************************************
--依据aaa_fmt.xml格式文件，将test_aaa.txt中的数据导入到aaa表
INSERT INTO test..aaa
SELECT *
FROM OPENROWSET(BULK 'f:\aaa.txt',
FORMATFILE='f:\aaa_fmt.Xml'
) as t1;
--1.查看bcp参数
--cmd -> bcp -h
--2.bcp 可执行以下四个动作
/*
(1) 导入
这个动作使用in命令完成，后面跟需要导入的文件名。
(2) 导出
这个动作使用out命令完成，后面跟需要导出的文件名。
(3) 使用SQL语句导出
这个动作使用queryout命令完成，它跟out类似，只是数据源不是表或视图名，而是SQL语句。
(4) 导出格式文件
这个动作使用format命令完成，后而跟格式文件名。
*/
--3.bcp常用参数
/*
-n 指定本机数据类型。
-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值插入，而不是这列的默认值。
*/
