Delete Old Tables
-
dear Friends,
Kindly help me, I want to make a syntax that compares if the number of rows in table A is the same as Table B, then delete table A, but if not,update table A then delete the it
i have 10 table that need to be deleted, and before that I insert into History_table, with this syntax
DECLARE @name as varchar(500)
DECLARE @sql as varchar(500)DECLARE db_cursor CURSOR FOR
SELECT name
FROM A
WHERE status = 'over_6months' and [type]='u'
--where name ='ta_B_AFFINITY_BUILD_TEST_Tes_Prod_PDS_S_MQ'OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @nameWHILE @@FETCH_STATUS = 0
BEGIN
--print @name
set @sql = 'INSERT INTO HISTORY_TABLE select * , ''' + @name +''' ,''INSERT'' AS [ACTION] ,suser_name() as [USER_NAME] ,getdate() as deletion_time FROM ' + @name
print @sql
--exec(@sql)FETCH NEXT FROM db_cursor INTO @name
ENDCLOSE db_cursor
DEALLOCATE db_cursorAfter that code, I want to make another syntax when the record at table A = table B then Delete table A
how to create it ?thank you
-
Please post create table script and insert script with sample data, you've provided what you've tried already, and desired result.
- Joe Torre - Friday, September 7, 2018 11:33 AM
Hi Joe,
First thing first, I search the Table with condition over 6 months..here is the scripts
select
[STATUS] = CASE WHEN CONVERT(VARCHAR(8),MODIFY_dATE,112) < DATEADD(MONTH, -6, GETDATE()) THEN 'OVER_6MONTHS' ELSE 'STILL_USE' END, *
into Temp_Table
--SELECT *
from sys.tables
where type = 'u'
and [NAME] LIKE 'BCK_TA_%'
OR [NAME] LIKE 'TA_%'
--AND CASE CONVERT(VARCHAR(8),MODIFY_DATE,112) = WHEN MODIFY_dATE > DATEADD(MONTH, -6, GETDATE()) THEN 'OVER_6MONTHS' ELSE 'STILL_USE' END
ORDER BY NAME ASCafter that, I create History_table to insert table_name+ Rows:
CREATE TABLE [dbo].[Table_History](
[record_id] [int] NOT NULL,
[contact_info] [varchar](128) NOT NULL,
[contact_info_type] [int] NOT NULL,
[record_type] [int] NOT NULL,
[record_status] [int] NOT NULL,
[call_result] [int] NULL,
[attempt] [int] NOT NULL,
[dial_sched_time] [int] NULL,
[call_time] [int] NULL,
[daily_from] [int] NOT NULL,
[daily_till] [int] NOT NULL,
[tz_dbid] [int] NOT NULL,
[campaign_id] [int] NULL,
[agent_id] [varchar](32) NULL,
[chain_id] [int] NOT NULL,
[chain_n] [int] NOT NULL,
[group_id] [int] NULL,
[app_id] [int] NULL,
[treatments] [varchar](255) NULL,
[media_ref] [int] NULL,
[email_subject] [varchar](255) NULL,
[email_template_id] [int] NULL,
[switch_id] [int] NULL,
[anp] [float] NULL,
[citas_call_id] [varchar](5) NULL,
[citas_campaign_id] [varchar](6) NULL,
[contact_name] [varchar](50) NULL,
[custom_float_1] [float] NULL,
[custom_float_2] [float] NULL,
[custom_int_1] [int] NULL,
[custom_int_2] [int] NULL,
[custom_varchar_1] [varchar](50) NULL,
[custom_varchar_2] [varchar](50) NULL,
[custom_varchar_3] [varchar](50) NULL,
[policy_id] [varchar](100) NULL,
[premium] [float] NULL,
[product_id] [varchar](50) NULL,
[prospect_id] [varchar](20) NULL,
[sponsor_id] [varchar](10) NULL,
[sponsor_name] [varchar](60) NULL,
[table_name] [varchar](200) NULL,
[Action] varchar (20) Not Null,
[user_name] [varchar](100) NOT NULL,
[deletion] [datetime] NULL,
CONSTRAINT [TABLE_HISTORY_PK] PRIMARY KEY CLUSTERED
(
[chain_id] ASC,
[chain_n] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[record_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GOthis is the sample data:
Now I insert the table ta_G_UM_Cashback_WBK_IZ_JUL17 into History_table with syntax:
DECLARE @name as varchar(500)
DECLARE @sql as varchar(500)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM A
WHERE status = 'over_6months' and [type]='u'
--where name ='ta_B_AFFINITY_BUILD_TEST_Tes_Prod_PDS_S_MQ'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
--print @name
set @sql = 'INSERT INTO HISTORY_TABLE select * , ''' + @name +''' ,''INSERT'' AS [ACTION] ,suser_name() as [USER_NAME] ,getdate() as deletion_time FROM ' + @name
print @sql
--exec(@sql)
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursorthe Question: after I've done with that steps,,I want to validate the Rows data..between [ta_G_UM_Cashback_WBK_IZ_JUL17] at sys.tables = [ta_G_UM_Cashback_WBK_IZ_JUL17] at History_Table..
If RowCount at sys.tables.[ta_G_UM_Cashback_WBK_IZ_JUL17] = Rowcount at History_Table.[ta_G_UM_Cashback_WBK_IZ_JUL17]
then Delete the sys.tables.[ta_G_UM_Cashback_WBK_IZ_JUL17]how to validate it ?
All of table that I want to delete from sys.tables have the same columns name..
thank you
-
1- In your WHILE loop use EXECUTE sp_executeSQL instead of EXEC.
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017
2- Capture the error from your insert statement execution
3a- If no error happened, then create a dynamic SQL statement for the deletion
3b- If error happened, skip over deletionIf A is a temporary table for your first script where you capture the names, then you can replace your cursor with following approach:
1- Capture relevant rows into temporary table A
2- Delete irrelevant rows from temporary table where status <> 'over_6months'
3- WHILE EXISTS (SELECT 1 FROM A) BEGIN .... END
3A- Dynamic SQL to insert into history table
3Ba- if successful then dynamic SQL to drop originating table and pass execution time as output parameter
3Bb- update history table with execution time parameter on column deletion (I think that is what this column is for)
3C- dynamic SQL to delete name of originating table from temporary table A
The WHILE loop processes each table on its own and table A will have 0 rows at the end. - unas_sasing - Thursday, September 6, 2018 9:01 PM
That cannot be correct. Why would you update the table that you're going to delete? Did you mean that you wanted to update Table B from table A and then delete Table A? :blink:
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Monday, September 10, 2018 8:04 AM
I guess in theory you could have a trigger on the table that you want to fire with the appropriate update(s) before dropping the table(?).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher - Monday, September 10, 2018 8:24 AM
My mistake im sorry..when table A have been copied to B all of rows and columns..then delete it..
But before I delete it, I want to make sure if total rows in table A = total rows in table B - unas_sasing - Tuesday, September 11, 2018 2:13 AM
We need another piece of information. Is there a combination of columns that will make a unique key for Table A? Of is the whole row the key? I'm assuming at this point that Table B is your "history" table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Tuesday, September 11, 2018 5:42 AM
Why does table B has no name column for which table A's have been deleted?
If it would have then the condition to check (if successful deletion is not good enough) before deletion is to count / sum from table A and compare to records for table A in table B.
- Jeff Moden - Tuesday, September 11, 2018 5:42 AM
Table B is the history_table and it should as same as table A (columns name,total rows)
Knut Boehnert - Tuesday, September 11, 2018 9:34 AMAll Columns in table B have the same column name like table A + Add new_columns with [table_name] [varchar](200) NULL, [Action] varchar (20) Not Null, [user_name] [varchar](100) NOT NULL, [deletion] [datetime] NULL
Like Jeff said table B is the History_Table for table A.
After all data in table A have been copied/insert to table B then I would like to delete table A because not being used anymore..
One day, if some users need it table A back, I could create this table A with all information in table B (Table_History) -
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 TableAWrapped in a sensible Try/Catch handler just in case something goes wrong.
- 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 BPlease 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 deletedINSERT 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 ASCDECLARE @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_HISTBEGIN
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
ENDPRINT @SQL2
--exec @SQL2 -
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.
- Knut Boehnert - Friday, September 14, 2018 7:32 AM
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..
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply