Sql Server 存储过程

标签：存储 LIKE sp WHEN Server Sql 800 nvarchar configure

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Access文件导入数据

USE [Usp]
GO
/****** Object: StoredProcedure [dbo].[usp_DaoRuAccess] Script Date: 2023/8/5 11:02:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_DaoRuAccess]
@Url_A nvarchar(800),
@Path_A nvarchar(800)=0,
@Tb_A nvarchar(800),
@Sheet_A nvarchar(800),
@InsertSQL_A nvarchar(1000)=0
AS
BEGIN
SET NOCOUNT ON;
---#开启服务#---
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

---#拼接Access路径与传入参数#---
set @Path_A = '''Microsoft.ACE.OLEDB.12.0'',''Data Source=' + cast(@URL_A as varchar(800)) + ';User ID=Admin;Password='''
set @InsertSQL_A =N'SELECT * into ##'+@Tb_A+' FROM OpenDataSource('+@Path_A+')...['+@Sheet_A+']'
exec (@InsertSQL_A)

---#关闭服务#---
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

END

Access文件导入数据批量导入

USE [Usp]
GO
/****** Object: StoredProcedure [dbo].[usp_DaoRuAccess_YuanBan] Script Date: 2023/8/5 11:06:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_DaoRuAccess_YuanBan]
@Url_A nvarchar(800),
@Path_A nvarchar(800)=0,
@Tb_A nvarchar(800),
@Sheet_A nvarchar(800),
@InsertSQL_A nvarchar(1000)=0,
@ZhuanYun_1 nvarchar(800)=0,
@ZhuanYun_2 nvarchar(800)=0,
@ZhuanYun_3 nvarchar(800)=0
AS
BEGIN
SET NOCOUNT ON;
---#开启服务#---
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

---#关闭服务#---
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

END

Access文件导入数据批量导入

USE [Usp]
GO
/****** Object: StoredProcedure [dbo].[usp_DaoRuAccess_DongBeiDaQu] Script Date: 2023/8/5 11:05:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

ALTER PROCEDURE [dbo].[usp_DaoRuAccess_DongBeiDaQu]
@Url_A nvarchar(800),
@DLQ_A_LN nvarchar(800),
@DLQ_A_JL nvarchar(800),
@DLQ_A_HLJ nvarchar(800),
@Day_A nvarchar(800),
@Path_A_LN nvarchar(800)=0,
@Path_A_JL nvarchar(800)=0,
@Path_A_HLJ nvarchar(800)=0,
@Sheet_A nvarchar(800),
@InsertSQL_A nvarchar(2000)=0

AS
BEGIN
SET NOCOUNT ON;
---#开启服务#---
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

---#拼接Access路径与传入参数#---
set @Path_A_LN = '''Microsoft.ACE.OLEDB.12.0'',''Data Source=' + cast(@URL_A as varchar(800)) + @DLQ_A_LN + @Day_A + '.accdb;User ID=Admin;Password='''
set @Path_A_JL = '''Microsoft.ACE.OLEDB.12.0'',''Data Source=' + cast(@URL_A as varchar(800)) + @DLQ_A_JL + @Day_A + '.accdb;User ID=Admin;Password='''
set @Path_A_HLJ = '''Microsoft.ACE.OLEDB.12.0'',''Data Source=' + cast(@URL_A as varchar(800)) + @DLQ_A_HLJ + @Day_A + '.accdb;User ID=Admin;Password='''

set @InsertSQL_A =N'

SELECT DISTINCT
CONVERT(CHAR(10),签收时间,120) AS 日期,
(CASE
WHEN 运单号 LIKE ''55%'' THEN ''七星潭''
WHEN 运单号 LIKE ''JT3%'' THEN ''七星潭''
WHEN 运单号 LIKE ''JT5%'' THEN ''桃花岛''
WHEN 运单号 LIKE ''JT290%'' THEN ''紫金山''
WHEN 运单号 LIKE ''JT291%'' THEN ''紫金山''
WHEN 运单号 LIKE ''JT292%'' THEN ''紫金山''
WHEN 运单号 LIKE ''JT293%'' THEN ''紫金山''
WHEN 运单号 LIKE ''JT294%'' THEN ''紫金山''
WHEN 运单号 LIKE ''JT295%'' THEN ''逍遥峰''
WHEN 运单号 LIKE ''JT296%'' THEN ''逍遥峰''
WHEN 运单号 LIKE ''JT297%'' THEN ''逍遥峰''
WHEN 运单号 LIKE ''JT298%'' THEN ''逍遥峰''
WHEN 运单号 LIKE ''JT299%'' THEN ''逍遥峰''
ELSE ''其他'' END) AS 订单来源,
(寄件代理区+''-''+签收代理区) AS 代理区对,
(始发转运中心+''-''+目的转运中心) AS 中心对,
(寄件市+''-''+签收市) AS 城市对,
(寄件市+寄件区县+''-''+签收市+签收区县) AS 区县对,
DATEDIFF(DD,ISNULL(揽收时间,入仓时间) ,签收时间) AS 揽签日期差,
* ,
ISNULL((始发转运中心+干线任务单号),'''') AS 始发干线辅助列,
ISNULL(
( CASE
WHEN (目的转运中心+到车任务编号) LIKE ''%ZX%'' THEN ''''
ELSE (目的转运中心+到车任务编号) END
),'''') AS 目的干线辅助列,
ISNULL(
(CASE
WHEN 到车任务编号 LIKE ''%ZX%'' THEN ''支线''
WHEN 到车任务编号 LIKE ''%GX%'' THEN ''干线''
WHEN 到车任务编号 LIKE ''DBGX%'' THEN ''干线''
WHEN 到车任务编号 LIKE ''DKGX%'' THEN ''干线''
WHEN 到车任务编号 LIKE ''WFGX%'' THEN ''干线''
WHEN 到车任务编号 LIKE ''DKDD%'' THEN ''干线''
WHEN 到车任务编号 LIKE ''JBGX%'' THEN ''干线''
WHEN 到车任务编号 LIKE ''DBDD%'' THEN ''干线''
WHEN 到车任务编号 LIKE ''WFDD%'' THEN ''干线''
WHEN 到车任务编号 LIKE ''JBDD%'' THEN ''干线''
ELSE ''其他'' END ), '''') AS 到车任务判断

INTO ##ShiXiaoMingXi

FROM (

SELECT * FROM OpenDataSource('+@Path_A_LN+')...['+@Sheet_A+']
union all
SELECT * FROM OpenDataSource('+@Path_A_JL+')...['+@Sheet_A+']
union all
SELECT * FROM OpenDataSource('+@Path_A_HLJ+')...['+@Sheet_A+'] ) a
'
exec (@InsertSQL_A)

---#关闭服务#---
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

END

Excel文件导入数据

USE [Usp]
GO
/****** Object: StoredProcedure [dbo].[usp_DaoRuExcel] Script Date: 2023/8/5 11:11:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_DaoRuExcel]
@Url nvarchar(800),
@Path nvarchar(800)=0,
@Tb nvarchar(800),
@Sheet nvarchar(800),
@InsertSQL nvarchar(800)=0
AS
BEGIN
SET NOCOUNT ON;
---#开启服务#---
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

---#拼接Excel路径与传入参数#---
set @Path = '''Microsoft.ACE.OLEDB.12.0'',''Data Source=' + cast(@URL as varchar(800)) + ';Extended Properties=Excel 12.0'''
set @InsertSQL =N'SELECT * into ##'+@Tb+' FROM OpenDataSource('+@Path+')...['+@Sheet+'$]'
exec (@InsertSQL)

---#关闭服务#---
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

END

Excel文件导入数据批量导入

USE [Usp]
GO
/****** Object: StoredProcedure [dbo].[usp_PiLiangDaoRuExcel] Script Date: 2023/8/5 11:11:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_PiLiangDaoRuExcel]

@SQL nvarchar(4000)=0,
@Zzz_ZhongZhuan nvarchar(1000)=0,
@Url nvarchar(1000), ----------文件路径
@Name nvarchar(1000), ----------文件名(不包含尾部数字)
@Start int, ----------起始表
@Num int , ----------循环次数
@Tb nvarchar(1000)=0, ----------全局临时表名称
@Sheet nvarchar(1000) ----------Excel的Sheet页名称

AS
BEGIN
SET NOCOUNT ON;

---#开启服务#---
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

set @Zzz_ZhongZhuan = '''Microsoft.ACE.OLEDB.12.0'',''Data Source="'+ cast(@Url as varchar(1000))+ cast(@Name as varchar(1000)) + cast(@Start as varchar(1000)) + ').xlsx";
Extended properties=Excel 12.0'''

set @SQL = N'SELECT * into ##'+@Tb+' FROM OpenDataSource('+@Zzz_ZhongZhuan+')...['+@Sheet+'$]'
exec sp_executesql @SQL

Set @Start = @Start + 1

While @Start <= @Num
begin
---导入表格数据
set @Zzz_ZhongZhuan = '''Microsoft.ACE.OLEDB.12.0'',''Data Source="'+ cast(@Url as varchar(1000))+ cast(@Name as varchar(1000)) + cast(@Start as varchar(1000)) + ').xlsx";
Extended properties=Excel 12.0'''

set @SQL = 'insert into ##'+@Tb+' SELECT * FROM OpenDataSource('+@Zzz_ZhongZhuan+')...['+@Sheet+'$]'

exec sp_executesql @SQL

Set @Start = @Start + 1
End

---#关闭服务#---
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

END

标签：存储,LIKE,sp,WHEN,Server,Sql,800,nvarchar,configure
From： https://www.cnblogs.com/-Tiko/p/17607675.html

相关文章

赞助商

阅读排行