金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > SqlServer之xp_cmdshell_使用以及配置,使用脚本输出

SqlServer之xp_cmdshell_使用以及配置,使用脚本输出

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-09-14 06:55

转自:

SQL Server的导出导入格局有:

 SQL Server数据导入导出工具BCP详解

BCP是SQL Server中负责导入导出数据的二个命令行工具,它是依照DB-Library的,而且能以相互的办法一点也不慢地导入导出大量的多少。BCP能够将数据库的表或视图直接导出,也能经过SELECT FROM语句对表或视图实行过滤后导出。在导入导出数据时,可以采纳默许值或是使用三个格式文件将文件中的数据导入到数据库或将数据库中的数据导出到文件中。


  1. 在SQL Server中提供了导入导出的界面操作。
  2. 在分界面操作中又分【复制贰个或多少个表或视图的多少】和【编写查询以钦命要传输的数据】三种形式,第一种是间接对表、视图举行总体字段、记录实行导出,而第三种正是能够透过SQL语句来支配导出导入的字段和行。
  3. 使用 简单但管用的SQL脚本 中的【表复制】这里面包车型大巴章程。
  4. 再一种就是在命令行中使用bcp命令来导入导出多少,要求专门表明的是,那是对命局据量导入导出就好的法子。

    --整个表导出(out)
    bcp 数据库名.dbo.表名 out c:currency.txt -S"数据库实例" -U"客户" -P"密码" -c 

    --使用SQL语句导出(queryout)
    bcp "select * from 数据库名.dbo.表名" queryout c:currency.txt -S 数据库实例 -U"顾客" -P"密码" -c

    --设置字段分隔符和行分隔符(-c -t"," -r"n"),不想输入字段类型等请合作-c一齐使用
    bcp "select * from 数据库名.dbo.表名" queryout c:currency.txt -S 数据库实例 -U"客户" -P"密码" -c -t"," -r"n"

    --钦定每批导入数据的行数、内定服务器发出或收受的各种互连网数据包的字节数(-k -b陆仟 -a65535)
    bcp "select * from 数据库名.dbo.表名" queryout c:currency.txt -S 数据库实例 -U"客户" -P"密码" -c -t"," -r"n" -k -b5000-a65535

    --在查询深入分析器上实践(EXEC master..xp_cmdshell)
    金沙棋牌app手机下载,EXEC master..xp_cmdshell 'bcp "select * from 数据库名.dbo.表名" queryout c:currency.txt -S 数据库实例 -U"顾客" -P"密码" -c'

    --把SQL语句生成几个.sql文件,然后调用
    --注:路线的文书夹名称中间不能够有空格
    exec master..xp_cmdshell 'osql -S 数据库实例 -U 客商 -P 密码 -i    C:cmdshellTest.sql'  

    --将数据导入到currency表中
    EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 in c:currency.txt -c -T'
    --导入数据也一直以来能够应用-F和-L选项来选取导入数据的记录行。
    EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 in c:currency.txt -c -F 10 -L 13 -T'

bcp是SQL Server中担负导入导出数据的三个命令行工具,它是基于DB-Library的,况兼能以相互的措施相当慢地导入导出多量的数额。bcp能够将数据库的表或视图直接导出,也能经过SELECT FROM语句对表或视图实行过滤后导出。在导入导出数据时,能够选择暗中认可值或是使用贰个格式文件将文件中的数据导入到数据库或将数据库中的数据导出到文件中。上面将详细座谈哪边利用bcp导入导出多少。

上面将详细钻探哪些利用BCP导入导出多少。

金沙棋牌app手机下载 1

  1. bcp的首要参数介绍

1. BCP的重要参数介绍

摘要:

在行职分令xp_cmdshell的时候要求设置权限:

bcp共有两个动作能够选择。

BCP共有三个动作能够选用。
(1) 导入。
本条动作使用in命令落成,前边跟须求导入的文件名。
(2) 导出。
以此动作使用out命令实现,前面跟需求导出的文件名。
(3) 使用SQL语句导出。
其一动作使用queryout命令完毕,它跟out类似,只是数据源不是表或视图名,而是SQL语句。
(4) 导出格式文件。
本条动作使用format命令完毕,后而跟格式文件名。

下文将分享应用sql脚本输出excel的方法
 
此脚本能够行使于 表或视图生成excel的点子,
若需使用sql脚本输出excel数据,我们可将sql脚本生成视图或有的时候表,

金沙棋牌app手机下载 2

(1) 导入。

上面介绍部分常用的选项:

然后再输出excel

输出excel语法简要介绍:
exec
master..xp_cmdshell 'bcp [数据库名称].[架构名].[表名] out [excel存放地点全路径] -c -q -S"服务器Ip" -U "sql用户名" -P "sql密码"'

---例1:
exec 
master..xp_cmdshell 'bcp test.dbo.tableName out d:test.xls -c -q -S"." -U "sa" -P "erp"'
---将数据库test中tablName输出值d盘test.xls文件
---服务器地址.
---sql账户sa
---sql密码erp
--------------------------------------------
例2: 根据动态文件名输出excel的方法
declare @name varchar(30) ---动态文件名
set @name ='d:test123.xlsx'

exec 
('master..xp_cmdshell ''bcp test.dbo.tableName out '+@name+' -c -q -S"." -U "sa" -P "erp"''') ---拼接操作语句,并采用exec执行

注意事项:

*1 使用脚本输出的excel ,无表头
2 可动态文件名和动态sa账户密码生成相关音信
3 假如出口sql脚本的数量至excel,大家需先将sql脚本生成的数目缓存至表中,然后输出表数据至excel

xp_cmdshell相关权力需张开
*

/*MSsql二〇〇七 如何启用xp_cmdshell
暗许情形下,sql server二〇〇六安装完后,xp_cmdshell是禁止使用的(或者是安全着想),假若要使用它,可按以下步骤
*/
-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--重新配置
RECONFIGURE
GO

以此动作使用in命令完毕,前面跟需求导入的文件名。

-f format_file
format_file表示格式文件名。那几个选项依赖于上述的动作,若是利用的是in或out,format_file表示早就存在的格式文件,借使使用的是format则意味着是要扭转的格式文件。

--实践想要的xp_cmdshell语句
Exec xp_cmdshell 'query user'
GO

(2) 导出。

-x
那个选项要和-f format_file协作使用,以便生成xml格式的格式文件。

--用完后,要记得将xp_cmdshell禁用(出于安全着想)
-- 允许配置高端选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 禁用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO
--重新配置
RECONFIGURE
GO

本条动作使用out命令实现,后边跟须求导出的文书名。

-F first_row
钦点从被导出表的哪一行导出,或从被导入文本的哪一行导入。

金沙棋牌app手机下载 3

(3) 使用SQL语句导出。

-L last_row
点名被导出表要导到哪一行截止,或从被导入文本导数据时,导到哪一行甘休。

金沙棋牌app手机下载 4

本条动作使用queryout命令实现,它跟out类似,只是数据源不是表或视图名,而是SQL语句。

-c
接纳char类型做为存款和储蓄类型,未有前缀且以"t"做为字段分割符,以"n"做为行分割符。

 

(4) 导出格式文件。

-w
和-c类似,只是当使用Unicode字符集拷贝数据时行使,且以nchar做为存款和储蓄类型。

 

那么些动作使用format命令完毕,后而跟格式文件名。

-t field_term
内定字符分割符,暗许是"t"。

 

上边介绍一些常用的选项:

-r row_term
点名行分割符,暗中认可是"n"。

 

-f format_file

-S server_name[ instance_name]
钦点要延续的SQL Server服务器的实例,若是未钦点此选项,BCP连接本机的SQL Server暗中同意实例。假如要延续某台机器上的暗中认可实例,只须求钦点机器名就可以。

蒙受的失实:

format_file表示格式文件名。那么些选项重视于上述的动作,借使应用的是in或out,format_file表示曾经存在的格式文件,假如接纳的是format则表示是要转换的格式文件。

-U login_id
点名连接SQL Sever的顾客名。

1、产生以下错误:
[Error][Microsoft][Native]Error = [Microsoft][SQL Native Client]不或许开采 BCP 主数据文件

-x

-P password
点名连接SQL Server的客商名密码。

运用如下命令:
EXEC xp_cmdshell 'ECHO %USERDOMAIN%%USERNAME%'
返回 :NT AUTHORITYNETWORK SERVICE

本条选项要和-f format_file合作使用,以便生成xml格式的格式文件。

-T
点名BCP使用信任连接登入SQL Server。借使未钦点-T,必得钦命-U和-P。

接下来在配备管理器(configuration manager)里面包车型地铁SQL server二〇〇六服务里展开,看到登录内置账号为Network service,
改成local system难题化解。

-F first_row

-k
钦命空驶列车使用null值插入,实际不是这列的暗中同意值。

2、SQLState = 22018, NativeError = 0
Error = [Microsoft][SQL Native Client]对此造型表达无效的字符值

点名从被导出表的哪一行导出,或从被导入文本的哪一行导入。

2. 什么样使用BCP导出多少

比如是表与表之间的数码导入,可用 -N, 恐怕 -w, 而不用用 -c
用-c的话, 如果导出的某些列中的数据中满含分隔符, 则会招致您 bcp 导入的时候退步
-N 只怕 -w 不会有那么些问题

-L last_row

(1) 使用BCP导出全方位表或视图。

 

点名被导出表要导到哪一行甘休,或从被导入文本导数据时,导到哪一行甘休。

BCP AdventureWorks.sales.currency out c:currency1.txt -c -U"sa" -P"password" --使用密码连接

bcp用法: bcp {dbtable | query} {in | out | queryout | format} 数据文件
  [-m 最大错误数]             [-f 格式化文件]         [-e 错误文件]
  [-F 首行]                       [-L 末行]                  [-b 批大小]
  [-n 本机类型]                 [-c 字符类型]            [-w 宽字符类型]
  [-N 将非文本保持为本机类型] [-V 文件格式版本]     [-q 带引号的标记符]
  [-C 代码页表达符]           [-t 字段终止符]       [-r 行终止符]
  [-i 输入文件]                   [-o 输出文件]         [-a 数据包大小]
  [-S 服务器名称]              [-U 用户名]            [-P 密码]
  [-T 可信赖连接]                  [-v 版本]                [-冠道允许行使区域安装]
  [-k 保留空值]                  [-E 保留标记值]
  [-h"加载提示"]                 [-x 生成 xml 格式化文件]

-c

 

运用char类型做为存储类型,未有前缀且以"/t"做为字段分割符,以"/n"做为行分割符。

BCP AdventureWorks.sales.currency out c:currency1.txt -c -T --利用信任连接

 

-w

上面是上述命令实践后的出口结果

其它:

和-c类似,只是当使用Unicode字符集拷贝数据时行使,且以nchar做为存款和储蓄类型。

Starting copy...
105 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 10 Average : (10500.00 rows per sec.)

新建查询->输入SQL查询语句,实践得到要求的结果,在询问结果栏点击鼠标右键->将结果另存为 
导出文件(*csv)就能够导出为 逗号分割的excel文件。 
那儿,由于尚未分列,所以应先新建八个白手的Excel文书档案,然后选拔张开刚才保存的的文本; 
下一场它会师世文本导入步骤,选中分割符号,下一步,选中 逗号,预览区域健康,然后在下一步和达成,保存为excel文件

-t field_term

上面是currency1.txt的一些剧情

点名字符分割符,私下认可是"/t"。

AED Emirati Dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
... ... ...
... ... ...
ZWD Zimbabwe Dollar 1998-06-01 00:00:00.000

-r row_term

在行使密码登入时索要将-U后的客户名和-P后的密码加上双引号。

点名行分割符,暗中认可是"/n"。

:BCP除了可以在调整台试行外,还足以经过调用SQL Server的二个系统存款和储蓄进度xp_cmdshell以SQL语句的艺术运维BCP。如上述第一条命令可改写为

-S server_name[ /instance_name]

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency out c:currency1.txt -c -U"sa" -P"password"'

钦点要连接的SQL Server服务器的实例,借使未钦命此选项,bcp连接本机的SQL Server私下认可实例。若是要连接某台机器上的暗中同意实例,只要求钦命机器名就可以。

执行xp_cmdshell后,重临信息以表的款式出口。为了能够方便地在SQL中进行BCP,下边包车型地铁下令都选择xp_cmdshell执行BCP命令。

-U login_id

(2) 对要导出的表实行过滤。

点名连接SQL Sever的客户名。

BCP不只能够承受表名或视图名做为参数,也足以接受SQL做为参数。通过SQL语句能够对要导出的表进行过滤,然后导出过滤后的笔录。

-P password

EXEC master..xp_cmdshell 'BCP "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:currency2.txt -c -U"sa" -P"password"'

钦命连接SQL Server的客户名密码。

BCP仍可以够通过轻便地设置选项对导出的行开展界定。

-T

EXEC master..xp_cmdshell 'BCP "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:currency2.txt -F 10 -L 13 -c -U"sa" -P"password"'

点名bcp使用信任连接登入SQL Server。假如未钦定-T,必得钦定-U和-P。

那条命令使用了多个参数-F 10和-L 13,表示从SELECT TOP 20 * FROM AdventureWorks.sales.currency所查出来的结果中取第10条到13条记下举办导出。

-k

3. 哪些运用BCP导出格式文件

点名空驶列车使用null值插入,实际不是那列的默许值。

BCP不仅可以够依靠表、视图导入导出多少,还是能够协作格式文件对导入导出数据进行界定。格式文件以纯文本文件情势存在,分为一般格式和xml格式。用户能够手工业编写制定格式文件,也得以因此BCP命令依据表、视图自动生成格式文件。

  1. 什么样行使bcp导出多少

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:currency_format1.fmt -c -T'

(1) 使用bcp导出全部表或视图。

上述命令将currency表的构造变迁了八个格式文件currency_format1.fmt,上边是以此格式文件的开始和结果。

bcp AdventureWorks.sales.currency out c:/currency1.txt -c -U"sa" -P"password" --使用密码连接或bcp AdventureWorks.sales.currency out c:/currency1.txt -c -T --应用信任连接

9.0
3
1 SQLCHAR 0 6 "t" 1 CurrencyCode SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "t" 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 24 "rn" 3 ModifiedDate

上边是上述命令推行后的输出结果

那几个格式文件记录了这么些表的字段(共3个字段)类型、长度、字符和行分割符和字段名等音讯。

Starting copy...

BCP还是能够透过-x选项生成xml格式的格式文件。

105 rows copied.

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:currency_format2.fmt -x -c -T'

Network packet size (bytes): 4096

xml格式文件所描述的从头到尾的经过和平凡格式文件所陈述的内容完全平等,只是格式不一致。

Clock Time (ms.) Total : 10 Average : (10500.00 rows per sec.)

4. 什么样运用BCP导入数据

上边是currency1.txt的片段内容

BCP能够由此in命令将上面所导出的currency1.txt和currency2.txt再另行导入到数据库中,由于currency有主键,由此大家将复制四个和currency的组织完全一致的表。

AED Emirati Dirham 1998-06-01 00:00:00.000

SELECT TOP 0 * INTO AdventureWorks.sales.currency1 FROM AdventureWorks.sales.currency

AFA Afghani 1998-06-01 00:00:00.000

将数据导入到currency1表中

... ... ...

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -c -T'

... ... ...

导入数据也同样能够使用-F和-L选项来接纳导入数据的记录行。

ZWD Zimbabwe Dollar 1998-06-01 00:00:00.000

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -c -F 10 -L 13 -T'

在运用密码登陆时索要将-U后的客商名和-P后的密码加上双引号。

在导入数据时得以依附现已存在的格式文件将满意条件的笔录导入到数据库中,不满意则不导入。如上述的格式文件中的第多少个字段的字符长度是24,固然有些文本文件中的相应字段的长短超越24,则那条记下将不被导入到数据库中,另外知足条件的记录健康导入。

注:bcp除了能够在调控台实践外,还足以经过调用SQL Server的贰个连串存款和储蓄进度xp_cmdshell以SQL语句的法子运转bcp。如上述第一条命令可改写为

运用普通的格式文件

EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency out c:/currency1.txt -c -U"sa" -P"password"'

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -F 10 -L 13 -c -f c:currency_format1.fmt -T'

执行xp_cmdshell后,再次回到音信以表的款式出口。为了能够一本万利地在SQL中实行bcp,下边包车型大巴吩咐都选取xp_cmdshell执行bcp命令。

选拔xml格式的格式文件

(2) 对要导出的表举办过滤。

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -F 10 -L 13 -c -x -f c:currency_format2.fmt -T'

bcp不仅可以够承受表名或视图名做为参数,也尚可SQL做为参数。通过SQL语句能够对要导出的表张开过滤,然后导出过滤后的记录。

总结

EXEC master..xp_cmdshell 'bcp "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:/currency2.txt -c -U"sa" -P"password"'

BCP命令是SQL Server提供的二个急迅的数量导入导出工具。使用它无需运转任何图形管理工科具就能够以便捷的诀窍导入导出数据。当然,它也能够通过xp_cmdshell在SQL语句中实施,通过这种艺术能够将其内置顾客端程序中(如delphi、c#等)运维,这也是使顾客端程序具备数据导入导出作用的点子之一。

bcp还足以因而轻易地安装选项对导出的行进行限定。

那条命令使用了四个参数-F 10和-L 13,表示从SE

EXEC master..xp_cmdshell 'bcp "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:/currency2.txt -F 10 -L 13 -c -U"sa" -P"password"'

LECT TOP 20 * FROM AdventureWorks.sales.currency所查出来的结果中取第10条到13条记下进行导出。

  1. 怎样利用bcp导出格式文件

bcp既可以够依靠表、视图导入导出多少,还足以兼容格式文件对导入导出数据进行界定。格式文件以纯文本文件格局存在,分为一般格式和xml格式。客商能够手工业编制格式文件,也足以因而bcp命令依据表、视图自动生成格式文件。

EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency format nul -f c:/currency_format1.fmt -c -T'

上述命令将currency表的布局调换了贰个格式文件currency_format1.fmt,下边是那一个格式文件的故事情节。

9.0

3

1 SQLCHAR 0 6 "/t" 1 CurrencyCode SQL_Latin1_General_CP1_CI_AS

2 SQLCHAR 0 100 "/t" 2 Name SQL_Latin1_General_CP1_CI_AS

3 SQLCHAR 0 24 "/r/n" 3 ModifiedDate

其一格式文件记录了那个表的字段(共3个字段)类型、长度、字符和行分割符和字段名等音信。

bcp仍是能够透过-x选项生成xml格式的格式文件。

EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency format nul -f c:/currency_format2.fmt -x -c -T'

xml格式文件所描述的内容和一般本性式文件所叙述的剧情完全同样,只是格式差异。

  1. 怎么行使bcp导入数据

bcp能够经过in命令将地点所导出的currency1.txt和currency2.txt再另行导入到数据库中,由于currency有主键,因而我们将复制一个和currency的结构完全平等的表。

SELECT TOP 0 * INTO AdventureWorks.sales.currency1 FROM AdventureWorks.sales.currency

将数据导入到currency1表中

EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency1 in c:/currency1.txt -c -T'

导入数据也完全一样能够动用-F和-L选项来摘取导入数据的记录行。

EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency1 in c:/currency1.txt -c -F 10 -L 13 -T'

在导入数据时得以遵照已经存在的格式文件将满意条件的笔录导入到数据库中,不知足则不导入。如上述的格式文件中的第三个字段的字符长度是24,要是有些文本文件中的相应字段的长短超越24,则那条记下将不被导入到数据库中,别的满意条件的记录健康导入。

行使普通的格式文件

EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency1 in c:/currency1.txt -F 10 -L 13 -c -f c:/currency_format1.fmt -T'

采用xml格式的格式文件

EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency1 in c:/currency1.txt -F 10 -L 13 -c -x -f c:/currency_format2.fmt -T'

总结

bcp命令是SQL Server提供的二个便捷的数据导入导出工具。使用它不须要运营任何图形处理工科具就能够以神速的措施导入导出数据。当然,它也得以透过xp_cmdshell在SQL语句中执行,通过这种艺术能够将其内置顾客端程序中(如delphi、c#等)运转,那也是使客商端程序具备数据导入导出效率的秘籍之一。

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:SqlServer之xp_cmdshell_使用以及配置,使用脚本输出

关键词:

上一篇:没有了

下一篇:没有了