1.存储过程动态表查询
USE [NETWORKING_AUDIT]
GO
/****** Object: StoredProcedure [dbo].[impConfigInfo] Script Date: 01/04/2019 08:39:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: wm
-- Create date: 20181215
-- Description: 导入dbo.T_UNIT_AUDIT、dbo.T_UNIT_DELIVERY_CONFIG、dbo.T_ETL_CONFIG、dbo.T_AUDIT_TASK 配置信息
-- pzb 表字段：[市（县、区）] ,[行政区划代码],[单位层次],[单位编码],[单位名称]
-- ,[统一社会信用代码证号/_组织机构代码证号],[行业类型],[数据名称],[系统名称] ,[数据类型]
-- ,[是否含多套数据] ,[数据内容],[数据备份类型],[采集表类型],[是否市级部门集中管理],[备注] -- =============================================
ALTER PROCEDURE [dbo].[impConfigInfo]
@paramTableName varchar(200),
@errorInfo VARCHAR(8000) OUTPUT
AS
BEGIN ---开启事务
BEGIN tran
begin try DECLARE @BakTableName VARCHAR(200)
DECLARE @str VARCHAR(800)
DECLARE @tableName VARCHAR(200)
SET NOCOUNT ON -----------------！！！注意设置，取消增删改返回值 -----------------------------删除无效行
DECLARE @delsql VARCHAR(8000)
SET @delsql = '
delete from '+@paramTableName+'
where ([行政区划代码] is null or [行政区划代码]=''null'' OR LEN([行政区划代码])=0)
and ([单位编码] is null or [单位编码]=''null'' OR LEN([单位编码])=0)
and ([单位名称] is null or [单位名称]=''null'' OR LEN([单位名称])=0);
select distinct * into '+@paramTableName+'_1 from '+@paramTableName+';
drop table ['+@paramTableName+'];
EXEC sp_rename '''+@paramTableName+'_1'','''+@paramTableName+''';
'
EXEC (@delsql); ------------------------------插入单位表信息前备份单位表 SET @tableName = 'T_UNIT_AUDIT'
--SELECT REPLACE(CONVERT(VARCHAR(200),GETDATE(),23),'-','')
SET @BakTableName = @tableName+'_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(200),GETDATE(),20),'-',''),' ',''),':','')+'_bak'
SET @str = '
IF not EXISTS(
SELECT 1 FROM [BackupsTables].sys.tables
where name = '''+@BakTableName+'''
)
begin
select * into [BackupsTables].[dbo].['+@BakTableName+'] from [NETWORKING_AUDIT].[dbo].['+@tableName+'];
end
'
EXEC (@str) ------------------------------删除单位表重复信息
DECLARE @sql1 VARCHAR(8000)
SET @sql1 = '
IF EXISTS(SELECT 1
FROM dbo.T_UNIT_AUDIT a,'+@paramTableName+' b
WHERE a.XZ_CODE = b.[行政区划代码]
AND a.UNIT_CODE= b.[单位编码]
)
BEGIN
DELETE FROM '+@paramTableName+'
WHERE [单位编码] IN
(
SELECT b.[单位编码]
FROM dbo.T_UNIT_AUDIT a,'+@paramTableName+' b
WHERE a.XZ_CODE = b.[行政区划代码]
AND a.UNIT_CODE= b.[单位编码]
)
AND [行政区划代码] IN
(
SELECT b.[行政区划代码]
FROM dbo.T_UNIT_AUDIT a, '+@paramTableName+' b
WHERE a.XZ_CODE = b.[行政区划代码]
AND a.UNIT_CODE= b.[单位编码]
)
END
'
EXEC (@sql1); ---------插入单位表信息
DECLARE @sql2 VARCHAR(8000)
SET @sql2 = '
INSERT INTO dbo.T_UNIT_AUDIT
( XZ_CODE ,
UNIT_NAME ,
UNIT_CODE ,
INDUSTRY_CODE,
INDUSTRY_NAME ,
SORT
)
SELECT
distinct
[行政区划代码],
[单位名称],
[单位编码],
(CASE WHEN [行业类型] LIKE ''%党政%'' THEN ''''
WHEN [行业类型] LIKE ''%医院%'' THEN ''''
WHEN [行业类型] LIKE ''%金融%'' THEN ''''
WHEN [行业类型] LIKE ''%国企%'' THEN ''''
WHEN [行业类型] LIKE ''%高校%'' THEN ''''
END),
[行业类型],
'''' sort
FROM '+@paramTableName+'
' EXEC (@sql2); ------------------------------插入配置表信息前备份配置表
SET @tableName = 'T_UNIT_DELIVERY_CONFIG'
SET @BakTableName = @tableName+'_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(200),GETDATE(),20),'-',''),' ',''),':','')+'_bak'
SET @str = '
IF not EXISTS(
SELECT 1 FROM [BackupsTables].sys.tables
where name = '''+@BakTableName+'''
)
begin
select * into [BackupsTables].[dbo].['+@BakTableName+'] from [NETWORKING_AUDIT].[dbo].['+@tableName+'];
end
'
EXEC (@str) ------------------------------删除配置表重复信息
DECLARE @sql3 VARCHAR(8000)
SET @sql3 = '
IF EXISTS(SELECT 1
FROM dbo.T_UNIT_DELIVERY_CONFIG a,'+@paramTableName+' b
WHERE a.XZ_CODE = b.[行政区划代码]
AND a.UNIT_CODE= b.[单位编码]
AND a.[FILE_NAME]= b.[系统名称]
)
BEGIN
DELETE FROM '+@paramTableName+'
WHERE [单位编码] IN
(
SELECT b.[单位编码]
FROM dbo.T_UNIT_DELIVERY_CONFIG a,'+@paramTableName+' b
WHERE a.XZ_CODE = b.[行政区划代码]
AND a.UNIT_CODE= b.[单位编码]
AND a.[FILE_NAME]= b.[系统名称]
)
AND [行政区划代码] IN
(
SELECT b.[行政区划代码]
FROM dbo.T_UNIT_DELIVERY_CONFIG a, '+@paramTableName+' b
WHERE a.XZ_CODE = b.[行政区划代码]
AND a.UNIT_CODE= b.[单位编码]
AND a.[FILE_NAME]= b.[系统名称]
)
END
'
EXEC (@sql3); ---------插入配置表信息
DECLARE @sql4 VARCHAR(8000)
SET @sql4 = '
INSERT INTO dbo.T_UNIT_DELIVERY_CONFIG
(
UNIT_CODE ,
UNIT_NAME ,
SYS_NAME ,
SYS_TYPE ,
COLLECT_METHOD ,
BACKUP_FILE_TYPE ,
ENCRYPT ,
COMPRESS ,
FILE_NAME ,
ACCOUNT_TYPE ,
XZ_CODE,
INDUSTRY_CODE,
INDUSTRY_NAME,
DB_BACKUP_MODE
)
SELECT
distinct
[单位编码],
[单位名称],
[系统名称],
(CASE WHEN [数据类型] LIKE ''%财务%'' THEN ''''
WHEN [数据类型] LIKE ''%业务%'' THEN ''''
END) sys_type,
[采集表类型],
[数据备份类型],
1,
1,
[系统名称],
[数据类型],
[行政区划代码],
(CASE WHEN [行业类型] LIKE ''%党政%'' THEN ''''
WHEN [行业类型] LIKE ''%医院%'' THEN ''''
WHEN [行业类型] LIKE ''%金融%'' THEN ''''
WHEN [行业类型] LIKE ''%国企%'' THEN ''''
WHEN [行业类型] LIKE ''%高校%'' THEN ''''
END),
[行业类型],
''全量''
FROM '+@paramTableName+'
'
EXEC (@sql4); ------------------------------插入ETL配置表信息前备份ETL配置表
SET @tableName = 'T_ETL_CONFIG'
SET @BakTableName = @tableName+'_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(200),GETDATE(),20),'-',''),' ',''),':','')+'_bak'
SET @str = '
IF not EXISTS(
SELECT 1 FROM [BackupsTables].sys.tables
where name = '''+@BakTableName+'''
)
begin
select * into [BackupsTables].[dbo].['+@BakTableName+'] from [NETWORKING_AUDIT].[dbo].['+@tableName+'];
end
'
EXEC (@str) ------------------------------删除ETL配置表重复信息
DECLARE @sql5 VARCHAR(8000)
SET @sql5 = '
IF EXISTS(SELECT 1
FROM dbo.T_ETL_CONFIG a,'+@paramTableName+' b
WHERE a.XZ_CODE = b.[行政区划代码]
AND a.UNIT_CODE= b.[单位编码]
AND a.sys_name = b.[系统名称]
)
BEGIN
DELETE FROM '+@paramTableName+'
WHERE [单位编码] IN
(
SELECT b.[单位编码]
FROM dbo.T_ETL_CONFIG a,'+@paramTableName+' b
WHERE a.XZ_CODE = b.[行政区划代码]
AND a.UNIT_CODE= b.[单位编码]
AND a.sys_name = b.[系统名称]
)
AND [行政区划代码] IN
(
SELECT b.[行政区划代码]
FROM dbo.T_ETL_CONFIG a, '+@paramTableName+' b
WHERE a.XZ_CODE = b.[行政区划代码]
AND a.UNIT_CODE= b.[单位编码]
AND a.sys_name = b.[系统名称]
)
END
'
EXEC (@sql5); ---------插入ETL配置表信息
DECLARE @sql6 VARCHAR(8000)
SET @sql6 = '
INSERT INTO dbo.T_ETL_CONFIG
( UNIT_CODE ,
UNIT_NAME ,
SYS_NAME ,
TEMPLATE_NAME ,
XZ_CODE
)
SELECT
distinct
a.[单位编码],
a.[单位名称],
a.[系统名称],
b.[对应转换模板路径],
a.[行政区划代码]
FROM '+@paramTableName+' a left join dbo.t_stcwpzb b
on a.[系统名称] = b.[财务软件及版本] ' EXEC (@sql6); ------------------------------插入审计任务表信息前备份审计任务表
SET @tableName = 'T_AUDIT_TASK'
SET @BakTableName = @tableName+'_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(200),GETDATE(),20),'-',''),' ',''),':','')+'_bak'
SET @str = '
IF not EXISTS(
SELECT 1 FROM [BackupsTables].sys.tables
where name = '''+@BakTableName+'''
)
begin
select * into [BackupsTables].[dbo].['+@BakTableName+'] from [NETWORKING_AUDIT].[dbo].['+@tableName+'];
end
'
EXEC (@str) ----------------------------删除审计任务表重复信息
DECLARE @sql7 VARCHAR(8000)
SET @sql7 = '
IF EXISTS(SELECT 1
FROM dbo.T_AUDIT_TASK a,'+@paramTableName+' b
WHERE a.XZ_CODE = b.[行政区划代码]
AND a.UNIT_CODE= b.[单位编码]
)
BEGIN
DELETE FROM '+@paramTableName+'
WHERE [单位编码] IN
(
SELECT b.[单位编码]
FROM dbo.T_AUDIT_TASK a,'+@paramTableName+' b
WHERE a.XZ_CODE = b.[行政区划代码]
AND a.UNIT_CODE= b.[单位编码]
)
AND [行政区划代码] IN
(
SELECT b.[行政区划代码]
FROM dbo.T_AUDIT_TASK a, '+@paramTableName+' b
WHERE a.XZ_CODE = b.[行政区划代码]
AND a.UNIT_CODE= b.[单位编码]
)
END
'
EXEC (@sql7); -----------插入审计任务表信息
DECLARE @sql8 VARCHAR(8000)
SET @sql8 = '
INSERT INTO dbo.T_AUDIT_TASK
( UNIT_CODE ,UNIT_NAME ,
INDUSTRY_CODE ,INDUSTRY_NAME ,
YEAR ,DATA_TYPE ,
STATE ,CREATE_TIME ,
AUDIT_METHOD_ID ,IS_ENABLE ,
STANDARD_DB_IP ,STANDARD_DB_NAME ,STANDARD_DB_TYPE ,
XZ_CODE ,
DOUBT_DB_IP ,DOUBT_DB_NAME ,DOUBT_DB_TYPE
)
SELECT distinct
a.[单位编码]
,a.[单位名称]
,(CASE WHEN [行业类型] LIKE ''%党政%'' THEN ''''
WHEN [行业类型] LIKE ''%医院%'' THEN ''''
WHEN [行业类型] LIKE ''%金融%'' THEN ''''
WHEN [行业类型] LIKE ''%国企%'' THEN ''''
WHEN [行业类型] LIKE ''%高校%'' THEN ''''
END),
a.[行业类型],2018,0,
''error'',GETDATE(),
b.methodModelID,1,''100.80.10.36'',CAST(a.[行政区划代码] AS VARCHAR(200))+''_cw'',''sqlserver'',
a.[行政区划代码],''100.80.10.30'',''410000_yd'',''sqlserver''
FROM '+@paramTableName+' a CROSS JOIN [wqb_upgrade].dbo.auditMethodModel b
WHERE b.DATA_TYPE = ''财务''
AND b.INDUSTRY_CODE = ''''
'
EXEC (@sql8); end try
begin catch
SELECT @errorInfo = Error_message()
if(@@trancount>0)
rollback tran
END catch
if(@@trancount>0)
commit tran END
2.事务处理
--常用语句就四个。 --Begin Transaction：标记事务开始。
--Commit Transaction：事务已经成功执行，数据已经处理妥当。
--Rollback Transaction：数据处理过程中出错，回滚到没有处理之前的数据状态，或回滚到事务内部的保存点。
--Save Transaction：事务内部设置的保存点，就是事务可以不全部回滚，只回滚到这里，保证事务内部不出错的前提下。 CREATE TABLE lives(Eat VARCHAR(200),Play VARCHAR(300),Numb int) ---开启事务
begin tran
--错误扑捉机制，看好啦，这里也有的。并且可以嵌套。
begin try
--语句正确
insert into lives (Eat,Play,Numb) values ('猪肉','足球',1)
--Numb为int类型，出错
insert into lives (Eat,Play,Numb) values ('猪肉','足球','abc')
--语句正确
insert into lives (Eat,Play,Numb) values ('狗肉','篮球',2)
end try
begin catch
select Error_number() as ErrorNumber, --错误代码
Error_severity() as ErrorSeverity, --错误严重级别，级别小于10 try catch 捕获不到
Error_state() as ErrorState , --错误状态码
Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
Error_line() as ErrorLine, --发生错误的行号
Error_message() as ErrorMessage --错误的具体信息
if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
rollback tran ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
end catch
if(@@trancount>0)
commit tran --如果成功Lives表中，将会有3条数据。 --表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型
select * from lives --------调用带返回值的存储过程
DECLARE @zz VARCHAR(200)
EXEC ceshi @zz output
SELECT @zz
3.批量还原sqlserver备份
SET NOCOUNT ON
DECLARE @disk NVARCHAR(max)
DECLARE @folederPath NVARCHAR(max)
DECLARE @cmdSql NVARCHAR(max)
DECLARE @filePath NVARCHAR(max)
DECLARE @targetDataPath NVARCHAR(max)
DECLARE @targetLogPath NVARCHAR(max)
DECLARE @restoreDate NVARCHAR(max)
DECLARE @fileName NVARCHAR(max); --***********************需要配置************************
----@disk 存放sqlserver备份文件的盘符,例如：'d'
SET @disk = 'e'
----@folederPath 存放sqlserver备份文件的文件夹路径,例如：'kk\test'
SET @folederPath = 'Idea\qq'
----@targetPath 还原出来的数据文件存放的路径,例如：'D:\kk\ta\d'
SET @targetDataPath = 'e:\Idea\qq'
----@targetPath 还原出来的日志文件存放的路径,例如：'D:\kk\ta\l'
SET @targetLogPath = 'e:\Idea\qq'
--******************************************************* SELECT @restoreDate = REPLACE(CONVERT(NVARCHAR(2000),GETDATE(),23),'-','')
SET @cmdSql ='exec master..xp_cmdshell '''+@disk+':&&cd '+@folederPath+'&&dir /s/b''' CREATE TABLE #tempTable ( filePath NVARCHAR(max) );
INSERT #tempTable
EXEC ( @cmdSql
); DECLARE My_Cursor CURSOR
FOR
( SELECT filePath,right(filePath,CHARINDEX('\',reverse(filePath))-1) fileN
FROM #tempTable
);
OPEN My_Cursor;
FETCH NEXT FROM My_Cursor INTO @filePath,@fileName;
WHILE @@FETCH_STATUS = 0
BEGIN IF @filePath != 'NUll'
BEGIN DECLARE @dataName NVARCHAR(max);
DECLARE @logName NVARCHAR(max);
DECLARE @restoreSql NVARCHAR(max); CREATE TABLE #tempTable2
(
LogicalName NVARCHAR(max) ,
a NVARCHAR(max) ,
b NVARCHAR(max) ,
c NVARCHAR(max) ,
d NVARCHAR(max) ,
e NVARCHAR(max) ,
f NVARCHAR(max) ,
g NVARCHAR(max) ,
h NVARCHAR(max) ,
i NVARCHAR(max) ,
j NVARCHAR(max) ,
k NVARCHAR(max) ,
l NVARCHAR(max) ,
m NVARCHAR(max) ,
n NVARCHAR(max) ,
o NVARCHAR(max) ,
p NVARCHAR(max) ,
q NVARCHAR(max) ,
r NVARCHAR(max) ,
s NVARCHAR(max) ,
t NVARCHAR(max)
);
INSERT #tempTable2
EXEC
( 'RESTORE FILELISTONLY FROM DISK = '''
+ @filePath + ''''
);
SELECT @dataName = LogicalName
FROM #tempTable2
WHERE LogicalName NOT LIKE '%log%';
SELECT @logName = LogicalName
FROM #tempTable2
WHERE LogicalName LIKE '%log%';
SET @restoreSql = 'RESTORE DATABASE [' + @fileName + '_'
+ @restoreDate + '] FROM DISK=''' + @filePath
+ '''WITH MOVE ''' + @dataName + '''To '''
+ @targetDataPath + '\' + @fileName + '.mdf'',
MOVE ''' + @logName + ''' To''' + @targetLogPath + '\'
+ @fileName + '.ldf'';
'; exec (@restoreSql);
PRINT '---------------------------------------分隔线-----------------------------------------'; DROP TABLE #tempTable2;
END;
FETCH NEXT FROM My_Cursor INTO @filePath,@fileName;
END;
CLOSE My_Cursor;
DEALLOCATE My_Cursor;
DROP TABLE #tempTable;
4.强制删除被占用的数据库
---------------查询数据库是否被占用
select * from master.sys.sysprocesses where dbid = db_id('kaifengshitiyuju078000000jindiekisxingzhengshiyezhuanbanaccess_20190212015105_5705723296216269111') ---------------方法1：强制断开连接
declare @d varchar(8000)
set @d= ' '
select @d=@d+ ' kill '+cast(spid as varchar)+char(13)
from master.sys.sysprocesses where dbid=db_id('lankaoxianchengshiguanliju036000000yongyouruanjiangrpu8sqlserver_20190130115932_8149424311300407836')
exec(@d) ---------------方法2：立即回滚事务设置脱机状态，然后还需要手动删除数据文件
ALTER DATABASE [410225_cw]
SET OFFLINE with rollback IMMEDIATE