一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务

    一般情况下,SQL数据库中带有导入与导出数据的直接按键操作,点击数据表所在的数据库--任务--导出/导入数据,根据导入/导出向导直接将数据导出即可。

一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务

 

 

但导出的数据格式多为Excel格式,如果需要导出的数据为csv/word/txt格式时,我们如何用SQL语言操作?如果需定时导出更新的数据表,又该如何操作呢?

第一步:使用 sp_configure 开启系统存储过程sys.xp_cmdshell的服务器安全配置

一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务
USE master
GO
RECONFIGURE --先执行一次刷新,处理上次的配置
GO

EXEC sp_configure 'show advanced options',1 --启用xp_cmdshell的高级配置
GO
RECONFIGURE --刷新配置
GO
EXEC sp_configure 'xp_cmdshell',1  --打开xp_cmdshell,可以调用SQL系统之外的命令
GO
RECONFIGURE
GO

--备注:若不使用xp_cmdshell时一定要将该服务器安全配置关闭,即将上述命令的1均改为0即可
View Code

第二步:使用系统存储过程sys.xp_cmdshell及大容量复制程序实用工具bcp导出数据

(1)使用bcp直接导出整个数据表

一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务
exec master.sys.xp_cmdshell 'bcp 数据库名称.dbo.数据表名称 out 输出的路径及格式 -c -q -S"服务器名称" -U"登录名" -P"登录名密码"'


--例如:

exec master.sys.xp_cmdshell'bcp 练习用的.dbo.student out E:学习\SQL\temp1.txt -c -q -S"ssksksksfr6kbb" -U"sa" -P"1234"'

exec master.sys.xp_cmdshell
 'bcp 练习用的.dbo.student out E:\学习\SQL\temp2.xls  -F 1 -L 5 -c -T -S"ssksksksfr6kbb" -U"sa" -P"1234"'

--(1)这里的格式txt可改成xls、csv、xlsx、doc
--(2)输出整个表格,bcp后面跟out
--(3)bcp用法中的字符 :-F输出第几行开始,-L输出第几行结束,-c使用char类型做为存储类型,-T可信连接(若未指定T必须指定-u-p),-S服务器,-U用户,-P密码,-t 指定字符分割符,默认是"\t"(空格,输出的结果在同一个单元格中),-q带引号的标识符
--(4)bcp中涉及的字符段要用双引号""引起来
View Code

一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务

 

bcp的具体使用方法见:https://docs.microsoft.com/zh-cn/sql/tools/bcp-utility?view=sql-server-2017

一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务

(2)bcp与queryout 连用,导出含有select语句的数据表

一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务
1 EXEC master..xp_cmdshell    
2   'bcp  "select sname,sbirthday from 练习用的.dbo.student " queryout  "E:\学习\SQL\temp2.xlsx"-c -q -S"ssksksksfr6kbb" -U"sa" -P"1234"'
View Code

这里要说明一下的是,使用bcp导出来的表格是没有列名的,若要输出含有where筛选条件的查询语句是会提示错误的,这时候可以直接利用数据库-任务--导出/导入数据向导中的编写查询语句

一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务

(3)创建存储过程导出数据表

    含有select语句的bcp后需跟queryout,但是这种bcp语句只能一行到底,不能进行语句换行处理,不易定位错误点,因此可进行创建存储过程-声明变量-调用,也为后面的定时导出数据任务做准备。

一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务
 1 --第一种:部分语句直接在bcp中写出
 2 use 练习用的--在哪个数据库下建立的存储过程
 3 go
 4 if exists (select * from dbo.sysobjects where id = object_id('pro_1') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
 5 drop procedure pro_1--查询是否存在该存储过程
 6 
 7 --先执行上述两条语句,再执行下面的,因为'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。
 8 create procedure  pro_1--创建存储过程
 9 as 
10 begin 
11 declare @exec_sql varchar(1025)
12 set @exec_sql='select * from 练习用的.dbo.student '---数据表使用的完整路径
13 set @exec_sql='bcp "'+@exec_sql+'" queryout "E:\学习\SQL\student.xls" -c -T -S"ssksksksfr6kbb" -U"sa" -P"1234"'
14 exec master.sys.xp_cmdshell @exec_sql
15 end
16 
17 exec pro_1--执行存储过程,导出数据
View Code 一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务
 1 --第二种:数据均进行参数声明,bcp中直接引入参数
 2 create procedure pro_2
 3 as
 4 begin 
 5     exec sp_configure 'show advanced options',1    --开启配置
 6     reconfigure
 7     exec sp_configure 'xp_cmdshell',1    --开启配置
 8     reconfigure--这四条语句可在存储过程外执行(像第一步直接大环境下执行),也可在存储过程里面写,建议里面,后续建立定时导出数据任务比较方便
 9 declare @filename varchar(1024)--Excel文件名
10 declare @path varchar(1024)--Excel存放路径名
11 declare @exec_sql varchar(1024)--查询语句
12 declare @server varchar(1024)--服务器名,可不写默认,若不写这项-S去掉
13 declare @zhanghu varchar(1024)--登录账户名
14 declare @mima varchar(1024)--账户密码
15 set @filename=' '+CONVERT(varchar(100), GETDATE(), 112)+' .xls'--Excel文件名
16 --set @FileName ='Log' + CONVERT(varchar(100), GETDATE(), 112)+replace(CONVERT(varchar(100), GETDATE(), 108),':','') + '.xls' 这种文件名是日期与时间结合的
17 set @path='E:\学习\SQL\'--Excel路径名,多加\是为了直接在该路径下放文件,否则命名则为SQL加上Excel名,文件将放在学习文件夹下
18 set @server='ssksksksfr6kbb'--服务器名,相应的bcp要有-S,若不写这项-S去掉
19 set @zhanghu='sa'--登录账户名
20 set @mima='1234'--账户密码名
21 set @exec_sql='select * from 练习用的.dbo.student'--数据表要用完整的数据库.dbo.表名
22 set @exec_sql='bcp "'+@exec_sql+'" queryout "'+@path+@filename+'" -c -T -S"'+@server+'" -U"'+@zhanghu+'" -P "'+@mima+'"'
23 exec master.sys.xp_cmdshell @exec_sql--bcp中涉及的每个字段要用双引号""引起来,又因为有+号连接符,因此双引号里需再放完整单引号进行+连接
24 end
25 go
26 
27 exec pro_2-执行存储过程导出数据
View Code

 

第三步:建立定时导出数据表任务

 (1)启动SQL Server代理

一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务

(2)创建定时作业

 一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务

一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务

(3)更新数据库中的数据表格,然后检查更新的数据是否定时输出

一、SQL系列之~使用SQL语言导出数据及实现定时导出数据任务

 

上一篇:kubernets使用helm安装Tiller 失败


下一篇:kubadm方式安装k8s时的images安装和init操作