andycadley - Wednesday, September 12, 2018 12:55 PM
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