TagLoggingExport.sql脚本如下:
USE [CC_oval_21_10_27_09_20_33R] GO --存储过程如果存在,则先删除存储过程 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_TagLoggingExportDay]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[sp_TagLoggingExportDay] GO CREATE PROCEDURE [dbo].[sp_TagLoggingExportDay] @TableName varchar(50), --解密表名称 @DatabaseName varchar(50), --解密数据库名称 @DatabasePath varchar(50), --解密数据库文件存储路径 @BackupName varchar(50), --备份文件的名称 @BackupPath varchar(50), --备份文件的路径 @SelectDate varchar(50), --查询的日期,如为空,则查询前一天,格式为2021-10-26 @ArchiveTagName varchar(max) --查询的变量名不能包含中文,可以为ValueID或者TagName,以;分隔,变量不能超出20,如果是TagName则字符长度不能超出128,如1;2;3或者Archvie1/tag1;Archive1/tag2 AS BEGIN declare @sql nvarchar(max) declare @CurDate varchar(50) --备份数据库日期后缀 declare @StartTime varchar(50) --查询开始时间 declare @EndTime varchar(50) --查询结束时间 declare @Count int --查询valudid数量,20个为一批 declare @TagName nvarchar(50) --存储ValueID对应的TagName,用于去掉Energy declare @temp nvarchar(max) --数据库不存在,则先创建数据库 IF (NOT EXISTS (SELECT * FROM sys.databases WHERE name = @DatabaseName)) BEGIN SET @sql = N'CREATE DATABASE <NAME> ON (NAME= ''<NAME>'',FILENAME= ''<PATH>\<NAME>.mdf'',SIZE=5mb,MAXSIZE=unlimited,FILEGROWTH=10%)' SET @sql = REPLACE(@sql, '<NAME>', @DatabaseName) SET @sql = REPLACE(@sql, '<PATH>', @DatabasePath) exec(@sql) END --Archive表存在,先删除 SET @sql = N'[<DataBaseName>].[dbo].[Archive]' SET @sql = REPLACE(@sql, '<DataBaseName>', @DatabaseName) IF OBJECT_ID(@sql,N'u') iS NOT NULL BEGIN SET @sql = 'DROP TABLE [<DataBaseName>].[dbo].[Archive]' SET @sql = REPLACE(@sql, '<DataBaseName>', @DatabaseName) exec(@sql) END --插入Archive到数据库,方便ValueID与ValueName的关联 SET @sql = 'Select * into [<DataBaseName>].[dbo].[Archive] FROM [Archive]' SET @sql = REPLACE(@sql, '<DataBaseName>', @DatabaseName) exec(@sql) --表不存在,则先创建表 SET @sql = N'[<DataBaseName>].[dbo].[<TableName>]' SET @sql = REPLACE(@sql, '<DataBaseName>', @DatabaseName) SET @sql = REPLACE(@sql, '<TableName>', @TableName) IF OBJECT_ID(@sql,N'u') iS NULL --表不存在,先创建表 BEGIN SET @sql = 'CREATE TABLE [<DATABASENAME>].[dbo].[<TABLENAME>]( [ValueID] [int] NOT NULL, [Timestamp] [datetime] NOT NULL, [RealValue] [float] NOT NULL, [Quality] [int] NOT NULL, [Flags] [int] NOT NULL )' SET @sql = REPLACE(@sql, '<DATABASENAME>', @DatabaseName) SET @sql = REPLACE(@sql, '<TABLENAME>', @TableName) exec(@sql) END ELSE --表存在,删除所有数据 BEGIN SET @sql = 'DELETE FROM [<DATABASENAME>].[dbo].[<TABLENAME>]' SET @sql = REPLACE(@sql, '<DataBaseName>', @DatabaseName) SET @sql = REPLACE(@sql, '<TableName>', @TableName) exec(@sql) END --时间处理 IF @SelectDate is NULL --参数为NULL,则查询前一天归档数据 BEGIN --获取前一天日期时间 SET @StartTime = CONVERT(varchar(100), DATEADD(DAY, -1, GETDATE()),23) + ' 00:00:00.000' SET @EndTime = CONVERT(varchar(100), DATEADD(DAY, -1, GETDATE()),23) + ' 23:59:59.999' --变量记录时间为UTC,需减8小时 SET @StartTime = CONVERT(varchar(100),DATEADD(HOUR, -8, @StartTime), 25) SET @EndTime = CONVERT(varchar(100),DATEADD(HOUR, -8, @EndTime), 25) SET @CurDate = CONVERT(varchar(100), DATEADD(DAY,-1, GETDATE()),112) END ELSE IF @SelectDate = '0000-00-00' --参数为0000-00-00,则查询前10分钟数据,用于测试 BEGIN SET @StartTime = '0000-00-00 00:10:00.000' SET @EndTime = '0000-00-00 00:00:00.000' SET @CurDate = CONVERT(varchar(100), DATEADD(MINUTE,-10, GETDATE()),112) + REPLACE(CONVERT(varchar(100), DATEADD(MINUTE,-10, GETDATE()),108),':','') END ELSE --参数为实际日期时间,则查询指定日期归档数据 BEGIN SET @StartTime = @SelectDate + ' 00:00:00.000' SET @EndTime = @SelectDate + ' 23:59:59.999' --变量记录时间为UTC,需减8小时 SET @StartTime = CONVERT(varchar(100),DATEADD(HOUR, -8, @StartTime), 25) SET @EndTime = CONVERT(varchar(100),DATEADD(HOUR, -8, @EndTime), 25) SET @CurDate = REPLACE(@SelectDate, '-' ,'') END IF UPPER(@ArchiveTagName) = 'ALL' --查询Archive表中的所有归档数据 BEGIN SET @Count = 0 SET @ArchiveTagName = '' DECLARE RS CURSOR FOR SELECT ValueID FROM [Archive] OPEN RS FETCH NEXT FROM RS INTO @temp WHILE @@Fetch_Status = 0 BEGIN SELECT @TagName = ValueName FROM [Archive] Where ValueID = @temp --如ValueName包含Energy,则略过 IF CHARINDEX('Energy', @TagName) = 0 BEGIN SET @Count = @Count + 1 IF @Count = 20 BEGIN SET @ArchiveTagName = @ArchiveTagName + ';' + @temp Set @sql = 'Insert Into <database>.[dbo].<tableName> Select * from OpenQuery(WINCCOLEDB,N''Tag:R,(<tagName>),''''<startTime>'''',''''<endTime>'''''')' SET @sql = REPLACE(@sql, '<database>', @DatabaseName) SET @sql = REPLACE(@sql, '<tableName>', @TableName) SET @sql = REPLACE(@sql, '<tagName>', @ArchiveTagName) SET @sql = REPLACE(@sql, '<startTime>', @StartTime) SET @sql = REPLACE(@sql, '<endTime>', @EndTime) print('SQL:' + @sql) exec( @sql) --PRINT @ArchiveTagName SET @Count = 0 SET @ArchiveTagName = '' END ELSE BEGIN IF @ArchiveTagName = '' BEGIN SET @ArchiveTagName = @temp END ELSE BEGIN SET @ArchiveTagName = @ArchiveTagName + ';' + @temp END END END FETCH NEXT FROM RS INTO @temp END CLOSE RS DEALLOCATE RS --处理剩余Tag IF @Count <> 0 BEGIN Set @sql = 'Insert Into <database>.[dbo].<tableName> Select * from OpenQuery(WINCCOLEDB,''Tag:R,(<tagName>),''''<startTime>'''',''''<endTime>'''''')' SET @sql = REPLACE(@sql, '<database>', @DatabaseName) SET @sql = REPLACE(@sql, '<tableName>', @TableName) SET @sql = REPLACE(@sql, '<tagName>', @ArchiveTagName) SET @sql = REPLACE(@sql, '<startTime>', @StartTime) SET @sql = REPLACE(@sql, '<endTime>', @EndTime) print('SQL:' + @sql) END END ELSE --查询指定ValueID或者ValueName BEGIN Set @sql = 'Insert Into <database>.[dbo].<tableName> Select * from OpenQuery(WINCCOLEDB,''Tag:R,(<tagName>),''''<startTime>'''',''''<endTime>'''''')' SET @sql = REPLACE(@sql, '<database>', @DatabaseName) SET @sql = REPLACE(@sql, '<tableName>', @TableName) SET @sql = REPLACE(@sql, '<tagName>', @ArchiveTagName) SET @sql = REPLACE(@sql, '<startTime>', @StartTime) SET @sql = REPLACE(@sql, '<endTime>', @EndTime) print('SQL:' + @sql) exec( @sql) END --备份数据库 SET @sql = 'BACKUP DATABASE <DATABASE> TO DISK = ''<PATH>\<NAME><DATE>.bak'' WITH INIT' SET @sql = REPLACE(@sql,'<DATABASE>', @DatabaseName) SET @sql = REPLACE(@sql,'<PATH>', @BackupPath) SET @sql = REPLACE(@sql,'<NAME>', @BackupName) SET @sql = REPLACE(@sql,'<DATE>', @CurDate) exec(@sql) print @sql END GO --示例1:导出前一天编号为893/894/895;896;897的归档数据,存储在c:\temp\database数据库TagLoggingDecode中的data表中,并备份到C:\temp\database\backup文件夹 --EXEC sp_TagLoggingExportDay 'data','TagLoggingDecode','c:\temp\database','893;894;895;896;897',null,'TagLogging','c:\temp\database\backup' --示例2:导出前10分钟编号为893/894/895;896;897的归档数据,存储在c:\temp\database数据库TagLoggingDecode中的data表中,并备份到C:\temp\database\backup文件夹 --EXEC sp_TagLoggingExportDay 'data','TagLoggingDecode','c:\temp\database','893;894;895;896;897','0000-00-00','TagLogging','c:\temp\database\backup' --示例3:导出前一天所有归档的归档数据,存储在c:\temp\database数据库TagLoggingDecode中的data表中,并备份到C:\temp\database\backup文件夹 --EXEC sp_TagLoggingExportDay 'data','TagLoggingDecode','c:\temp\database','ALL',null,'TagLogging','c:\temp\database\backup' --示例4:导出2021年10月11日的所有归档数据,存储在c:\temp\database数据库TagLoggingDecode中的data表中,并备份到C:\temp\database\backup文件夹 --EXEC sp_TagLoggingExportDay 'data','TagLoggingDecode','c:\temp\database','ALL','2021-10-11','TagLogging','c:\temp\database\backup'
标签:SET,varchar,temp,--,归档,西门子,REPLACE,Wincc,sql From: https://www.cnblogs.com/caesar-the-great/p/16949654.html