西门子Wincc的归档数据批量导出2

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'