• andycadley - Wednesday, September 12, 2018 12:55 PM

    Something like:

    Insert Into TableB
    (
       TableName,
       cola,
       colb,
       colc,
       user_name,
       action,
       deletion
    )
    Select
       TableName,
        cola,
        colb,
        colc,
        suser_name(),
        'INSERT',
        GetDate()
    (
    Select 'TableA' As TableName, cola, colb, colc From TableA
    Except
    Select TableName, cola, colb, colc From TableB
    ) X
    Drop TableA

    Wrapped in a sensible Try/Catch handler just in case something goes wrong.

    Dear all,
    I have already found the syntax for insert the source table (A) into history_table (B).
    And also delete table A if table A = table B

    Please suggest me if there is something missing or any other suggestion

    thank you

    Here is the syntax:


    --------------------------/*SYNTAX TO INSERT*/--------------------------------------------------------

    if exists (select * From sys.objects where name like '%tampung%' and type ='u')
    truncate table dbo.tampung --temp table that want to be deleted

    INSERT INTO dbo.tampung
    select
       [STATUS] = CASE WHEN CONVERT(VARCHAR(8),MODIFY_dATE,112) < DATEADD(MONTH, -6, GETDATE()) THEN 'OVER_6MONTHS' ELSE 'STILL_USE' END,
       *
         --SELECT *
    from sys.tables
    where type = 'u'
    and [NAME] LIKE 'BCK_TA_%'
    OR [NAME] LIKE 'TA_%'
    ORDER BY NAME ASC

    DECLARE @name as nvarchar(max)
    DECLARE @SQL as nvarchar(max)
    declare @rowcount as bigint;

    DECLARE db_cursor CURSOR FOR
    SELECT NAME FROM DBO.TAMPUNG --TEMPTABLE
    WHERE status = 'over_6months' and [type]='u'

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
            set @sql = N'SELECT @rowcount = count(1) FROM ' + quotename(@name)
            PRINT @ROWCOUNT
            --exec sp_executesql @sql, N'@rowcount int out', @rowcount out
       
    IF @rowcount <> 0
        BEGIN
             set @sql = 'INSERT INTO table_history select * ,''' + @name +''' ,''INSERT'' AS [ACTION] ,suser_name() as [USER_NAME] ,getdate() as deletion FROM ' + @name
         print @sql
         --exec SP_EXECUTESQL @sql
        end
          FETCH NEXT FROM db_cursor INTO @name
    END
    CLOSE db_cursor
    DEALLOCATE db_cursor

    ------------------/*SYNTAX TO DELETE*/------------------------------------------------

    --declare @rowcount as int;
    declare @rowcount1 as int;
    declare @name_SOURCE as varchar(500);
    declare @name_HIST as Nvarchar(500);
    --declare @sql as nvarchar(max);
    declare @sql1 as nvarchar(max);
    declare @sql2 as nvarchar(max);

    SELECT @name_SOURCE = NAME FROM TAMPUNG WHERE [status] = 'over_6months' and [type]='u'
    SELECT @NAME_HIST = TABLE_NAME FROM table_history WHERE TABLE_NAME = @name_SOURCE
    PRINT @NAME_HIST

    BEGIN
    set @sql = N'SELECT @rowcount = count(1) FROM ' + quotename(@NAME_SOURCE)
    set @sql1 = N'SELECT @rowcount1 = count(1) FROM ' + quotename(@NAME_HIST)
    exec sp_executesql @sql, N'@rowcount int out', @rowcount out;
    exec sp_executesql @sql1, N'@rowcount1 int out', @rowcount1 out;

    PRINT CAST(@ROWCOUNT AS VARCHAR)+' TABLE_SOURCE';
    PRINT CAST(@ROWCOUNT1 AS VARCHAR)+' TABLE_HIST';

    IF @ROWCOUNT = @ROWCOUNT1
                set @sql2 = 'DROP TABLE dbo.' +@name_SOURCE
    ELSE
               PRINT 'ERROR DELETING SOURCE TABLE '+@name_SOURCE
    END

    PRINT @SQL2
    --exec @SQL2