--TODO create a function around. replace database as paramter
USE P1602505
DECLARE @database nvarchar(100),
        @inverter_table nvarchar(100),
        @alias nvarchar(100),
        @table_name nvarchar(100),
        @virtual_device_alias nvarchar(100),
        @column_name nvarchar(100),
        @sql nvarchar(2000), 
        @insertsql nvarchar(2000), 
        @trigger_sql nvarchar(2000),
        @trigger_name nvarchar(100)

        SET @database = 'P1602505'
        SET @inverter_table = 'P1602505_798108_0116_0025_13321'
        SET @alias = (SELECT DEVICE_VALUE FROM P1602505.dbo.xxx_RAW_Device_Properties WHERE DEVICE_NAME='alias' AND TABLE_NAME=@inverter_table)

        SET @table_name = (SELECT DISTINCT A.TABLE_NAME FROM P1602505.dbo.xxx_RAW_Device_Properties AS A 
        WHERE A.TABLE_NAME <> @inverter_table AND A.DEVICE_NAME='Serial_Number' AND A.DEVICE_VALUE= 
        (SELECT DEVICE_VALUE FROM P1602505.dbo.xxx_RAW_Device_Properties WHERE DEVICE_NAME='Serial_Number' AND TABLE_NAME=@inverter_table))

        --manual destination table setting
        SET @table_name = 'Data_Table_000093'

        SET @virtual_device_alias  = (SELECT DISTINCT Virtual_Device_Name FROM P1602505.dbo.xxx_Virtual_Devices WHERE Table_Name = @table_name)

        print 'alias: ' + @alias
        print 'table name: ' + @table_name
        print 'raw table name: ' + @inverter_table
        print 'virtual device name: ' + @virtual_device_alias

        IF @table_name IS NOT NULL AND @table_name <> '' BEGIN
            print 'update device mappings for inverter'
            UPDATE P1602505.dbo.xxx_Device_Mappings 
            SET RAW_Table_Name=@inverter_table, RAW_Name=@alias
            WHERE Table_Name=@table_name
       

        print 'inverter_table: ' + @inverter_table
        print 'table name: ' + @table_name

        print 'disable trigger'
        SET @trigger_name = (SELECT name FROM P1602505.sys.triggers WHERE parent_id = (OBJECT_ID(@database +'.dbo.'+ @table_name) ) )
        
        SET @trigger_sql = 'DISABLE TRIGGER dbo.' + @trigger_name + ' ON '+ @database + '.dbo.' + @table_name 
        print @trigger_sql
        EXECUTE sp_executesql
                @trigger_sql

        DECLARE data_cursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
               
            SELECT DISTINCT A.COLUMN_NAME
            FROM P1602505.INFORMATION_SCHEMA.COLUMNS AS A
            INNER JOIN P1602505.INFORMATION_SCHEMA.COLUMNS AS B 
            ON A.TABLE_NAME = @table_name
            AND B.TABLE_NAME = @inverter_table
            AND A.Column_Name=B.column_Name
        OPEN data_cursor

        SET @sql = 'INSERT INTO P1602505.DBO.' + @table_name + ' ('  

        FETCH NEXT FROM data_cursor INTO @column_name
        SET @sql = @sql + @column_name
        SET @insertsql = @column_name

        WHILE @@FETCH_STATUS = 0 BEGIN

            FETCH NEXT FROM data_cursor INTO @column_name
            IF @@FETCH_STATUS = 0 BEGIN
                SET @sql = @sql + ', ' + @column_name
                SET @insertsql = @insertsql + ', ' + @column_name   
            END
        END

        SET @sql = @sql + ') SELECT ' + @insertsql + ' FROM ' + @database + '.dbo.' + @inverter_table

        CLOSE data_cursor
        DEALLOCATE data_cursor

        print 'copy data from: ' + @inverter_table + ' to: ' + @table_name
        print @sql 
        EXECUTE sp_executesql
                @sql

        print 'enable trigger'
        SET @trigger_sql = 'ENABLE TRIGGER [dbo].' + @trigger_name + ' ON '+ @database + '.dbo.' + @table_name
        print @trigger_sql
        -- Enable Trigger 
        EXECUTE sp_executesql
                @trigger_sql
        print 'enable trigger'
        
    END
    ELSE BEGIN
        print 'no device match found'
    END