ALTER PROCEDURE [dbo].[pro_ImportExcelByTime]
AS
BEGIN
--第一步,开启高级功能
EXEC sp_configure ‘show advanced options‘, 1;
RECONFIGURE;
EXEC sp_configure ‘xp_cmdshell‘,1
RECONFIGURE;
--第二步,bcp导出excel
declare @file_path nvarchar(50),@file_name nvarchar(500),@exec_sql nvarchar(4000)
set @file_path = ‘D:‘ --定义导出excel的绝对路径
declare @index int
select @index=MAX(autoId) from TestB.dbo.seed where CONVERT(varchar(10),createtime,120)=CONVERT(varchar(10),GETDATE(),120)
if @index is null or @index=0
begin
set @index=1
end
else
begin
set @index=@index 1
end
--导出文件名字
set @file_name = ‘导出Excel(‘ CONVERT(nvarchar(10),@index) ‘)‘ CONVERT(nvarchar(50), GETDATE(), 112) ‘.xls‘ --定义excel的文件名
declare @colName nvarchar(10),@proPrice nvarchar(10),@proNumber nvarchar(10),@proDescript nvarchar(200),@proCreateTime nvarchar(10)
set @colName=‘名称‘
set @proPrice=‘价格‘
set @proNumber=‘数量‘
set @proDescript=‘描述‘
set @proCreateTime=‘创建时间‘
--数据源sql语句
set @exec_sql = ‘select ‘‘‘ @colName ‘‘‘,‘‘‘ @proPrice ‘‘‘,‘‘‘ @proNumber ‘‘‘,‘‘‘ @proDescript ‘‘‘,‘‘‘ @proCreateTime ‘‘‘ union all select proName,convert(nvarchar(10),proPrice),convert(nvarchar(10),proNumber),proDescript,convert(nvarchar(10),proCreateTime,120) from testA.dbo.project1‘ --定义sql查询语句;数据表需使用的完整路径;sql 语句必须一行完成
set @exec_sql = ‘ bcp "‘ @exec_sql ‘" queryout "‘ @file_path ‘‘ @file_name ‘" -c -T -S "LAPTOP-AUVHR4RD" -U "sa" -P "123456"‘;
exec xp_cmdshell @exec_sql
--修改标识
insert into TestB.dbo.seed(A) values(‘第‘ convert(varchar(10),@index) ‘次导出‘)
--第三步,关闭高级功能
EXEC sp_configure ‘xp_cmdshell‘,0
RECONFIGURE;
EXEC sp_configure ‘show advanced options‘, 0;
RECONFIGURE;
END