Hello Friends,
On last week i am working on upgrade come (re-implementation project).
It is has size around 120GB. Upgrading from NAV 2009 R2 to NAV 2013 R2
We have decided that we will only carry forward the master data and open source document like (sales, purchase, service, production, etc) and rest of other transaction and ledger entry table data will delete.
G/L Entry table has more than 2 Crore entries and rest of other traction table have more than 50 lakes entries.
To delete the data from transaction table i have create a tool by using the SQL query to trunk the table data instead of delete query.
I have got the success. It is trunk the G/L entry table in just 2 second. Data deletion task is just completed in 2-3 minutes.
So, I would like to share the SQL Store Procedure and NAV objects which has call that store procedure for one by one table to trunk table data.
Following 3 object create for NAV 2009 R2:
|
Type
|
ID
|
Name
|
Remarks
|
|
Table
|
50090
|
Table
Delete List
|
Contain
the list of table to trunk data
|
|
Report
|
90505
|
Delete
Table Data
|
Call
the SP to trunk data for one by one table
|
|
Dataport
|
90501
|
Delete
Table Import
|
To
import the data in Table 50090
|
Note: Disable and Enable the table secondary keys
after data trunked from tables.
***************** Store Procedure *******************
/****** Object: StoredProcedure
[dbo].[usp_TruncateTable] Script Date:
12/31/2015 11:24:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_TruncateTable]
(
@flg VARCHAR(10) --BACKUP DELETE RESTORE
,@tableName NVARCHAR(250) -- Table name
,@excludedColumn
NVARCHAR(250) = 'timestamp' -- Works with
flg = RESTORE
,@flgBackup BIT = 1 -- Works with flg = DELETE
)
AS
BEGIN
/*
EXEC usp_TruncateTable
@flg = 'RESTORE'
--'DELETE','BACKUP'
,@tableName = 'CRONUS India Ltd_$Activity Step'
,@excludedColumn = 'timestamp' --default
,@flgBackup = 1 --default --0 = Shift + Delete
SELECT * FROM [CRONUS India Ltd_$Activity Step]
*/
SET NOCOUNT ON
DECLARE @sql NVARCHAR(MAX)
DECLARE @i INT
DECLARE @colnames VARCHAR(MAX)
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @tableName)
BEGIN
PRINT 'No tables found with name ' + QUOTENAME(@tableName)
SELECT name as [RelatedTables] FROM
sys.tables WHERE name LIKE '%' + @tableName + '%'
RETURN
END
IF @flg = 'DELETE'
BEGIN
IF OBJECT_ID('tempdb..#object_backup')
IS NOT NULL DROP TABLE #object_backup
CREATE TABLE #object_backup (id INT IDENTITY(1,1), objectname NVARCHAR(128), objtype nvarchar(100), objectsql VARCHAR(MAX), created_dt DATETIME DEFAULT (GETDATE()), hostid NVARCHAR(100) DEFAULT(HOST_NAME()))
DECLARE
@tDependnts TABLE (ID
INT IDENTITY(1,1), objetcname nvarchar(250), objtype nvarchar(100))
--Taking backup of the table to tempdb
IF @flgBackup
= 1
BEGIN
PRINT '=======Table backup started========'
SET @sql = '
IF
OBJECT_ID(''tempdb..'+QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', ''))+''') IS NOT NULL DROP TABLE tempdb..' +QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', '')) + '
SELECT
* INTO tempdb..'+QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', '')) +' FROM ' + QUOTENAME(@tableName)
PRINT @sql
EXEC sp_executesql @sql
PRINT 'Processed Record:' + CAST(@@rowcount AS VARCHAR(10))
PRINT '=======Table backup completed========'
END
--Finding dependencies
INSERT INTO @tDependnts
(
objetcname,
objtype
)
EXEC sp_depends @tableName
--Backing up dependencies
INSERT INTO #object_backup (objectname,
objectsql, objtype,
created_dt, hostid)
SELECT c.objetcname,
[definition], objtype,
GETDATE(), HOST_NAME() FROM @tDependnts c
INNER JOIN sys.sql_modules a ON 'dbo.' + OBJECT_NAME(a.object_id) = objetcname
INNER JOIN sys.objects b ON a.[object_id] = b.[object_id]
WHERE
is_ms_shipped = 0
PRINT 'Total dependent objects:' +
CAST(@@rowcount AS VARCHAR(10))
SELECT @i = COUNT(1) FROM #object_backup
--Deleting dependencies schema binding views
PRINT '=======Table dependent delete started========'
WHILE @i >= 1
BEGIN
SELECT @sql = 'DROP VIEW ' + QUOTENAME(REPLACE(ob.objectname,'dbo.','')) FROM #object_backup ob WHERE
ob.id = @i
AND objtype LIKE '%view%'
print @sql
EXEC
(@sql)
SET @sql = ''
SET @i -= 1
END
PRINT '=======Table dependent delete completed========'
--truncating the original table
PRINT '=======Table truncate started========'
SET @sql = N'TRUNCATE TABLE ' + QUOTENAME(@tableName)
PRINT @sql
EXEC
(@sql)
PRINT '=======Table truncate completed========'
PRINT '=======Table dependent creation started========'
SELECT @i = COUNT(1) FROM #object_backup
--Creating back the dependencies
WHILE @i >= 1
BEGIN
SELECT @sql = ob.objectsql FROM
#object_backup ob WHERE ob.id = @i
AND objtype LIKE '%view%'
EXEC
(@sql)
SET @sql = ''
SET @i -= 1
END
PRINT '=======Table dependent creation completed========'
END
ELSE IF @flg = 'RESTORE'
BEGIN
SET @colnames = STUFF((SELECT ',' + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME = @tableName AND COLUMN_NAME <>
@excludedColumn FOR XML
PATH('')), 1, 1, '')
PRINT '=======Restoring table data started========'
SET @sql = '
IF
OBJECT_ID(''tempdb..'+QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', ''))+''') IS NULL
BEGIN
PRINT
''NO BACKUP FOUND FOR SPECIFIC TABLE''
RETURN
END
INSERT
INTO ' + QUOTENAME(@tableName) + '(' + @colnames + ')
SELECT
'+@colnames+' FROM tempdb..' + QUOTENAME(@tableName
+ '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', ''))
--PRINT @sql
EXEC
(@sql)
PRINT 'Record Inserted:' + CAST(@@rowcount AS VARCHAR(10))
PRINT '=======Restoring table data completed========'
END
ELSE IF @flg = 'BACKUP'
BEGIN
PRINT '=======Table backup started========'
SET @sql = '
IF
OBJECT_ID(''tempdb..'+QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', ''))+''') IS NOT NULL DROP TABLE tempdb..' +QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', '')) + '
SELECT
* INTO tempdb..'+QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', '')) +' FROM ' + QUOTENAME(@tableName)
PRINT @sql
EXEC sp_executesql @sql
PRINT 'Processed Record:' + CAST(@@rowcount AS VARCHAR(10))
PRINT '=======Table backup completed========'
--Finding dependencies
END
ELSE
PRINT 'INVALID ARGUMENT PASSED. Valid arguments are BACKUP or
DELETE or RESTORE'
END
****************************************************************************
Download the objects and store procedure from following link:
