SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete Old Tables


Delete Old Tables

Author
Message
andycadley
andycadley
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1739 Visits: 1394
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.

Deny Christian
Deny Christian
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 Visits: 194
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

Knut Boehnert
Knut Boehnert
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2531 Visits: 436
This is procedural code instead of set-based code. This will work.

What I mean with this statement is you think in terms of single rows (cursor) instead of a data set of tables (temporary table of statements to execute).
If you do more database "stuff" feel free to learn the difference. Jeff's articles related to RBAR (also on this site) are the best start. There are others (just want to mention out of this pool the team around Brent Ozar).

Good for you to have worked it out yourself.
Deny Christian
Deny Christian
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 Visits: 194
Knut Boehnert - Friday, September 14, 2018 7:32 AM
This is procedural code instead of set-based code. This will work.

What I mean with this statement is you think in terms of single rows (cursor) instead of a data set of tables (temporary table of statements to execute).
If you do more database "stuff" feel free to learn the difference. Jeff's articles related to RBAR (also on this site) are the best start. There are others (just want to mention out of this pool the team around Brent Ozar).

Good for you to have worked it out yourself.

Hai Knut,
I did want to make a prrocedural code and this SP will work at my production servers in future..
One minus thing at my insert syntax,,the empty tables cant include in there..i still figure it out..
Perhaps any other suggestions i welcome..

Thank you..

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search