June 6, 2018 at 3:53 am
Hi Experts,
ALTER PROCEDURE PROC_JOB_BACKUP_OTHER_TABLES_ARCHIVED_00
@PARAMETER_DB_NAME VARCHAR(255)
,@PARAMETER_TABLE_NAME VARCHAR(255)
,@PARAMETER_DATE_FIELD_NAME VARCHAR(255)
,@ARCHIVE_DB_NAME VARCHAR(255)
,@IS_DATE_UTC BIT = 0
,@IS_DATE_FLOAT BIT = 0
,@SELECT BIT = 01
,@EXECUTE BIT = 0
,@END_DATE_TIME DATETIME = NULL
AS/*
DECLARE
@PARAMETER_DB_NAME VARCHAR(255)
,@PARAMETER_TABLE_NAME VARCHAR(255)
,@PARAMETER_DATE_FIELD_NAME VARCHAR(255)
,@ARCHIVE_DB_NAME VARCHAR(255)
,@IS_DATE_UTC BIT = 0
,@IS_DATE_FLOAT BIT = 0
,@SELECT BIT = 01
,@EXECUTE BIT = 0
,@END_DATE_TIME DATETIME = NULL
SELECT
@PARAMETER_DB_NAME = 'dbSilverStar'
,@PARAMETER_TABLE_NAME = 'Daily_Vehicle_Moving_Summary'
,@PARAMETER_DATE_FIELD_NAME = 'REPORT_DATE'
,@ARCHIVE_DB_NAME = 'dbArchivedData'
,@IS_DATE_UTC = 0
,@IS_DATE_FLOAT = 01
,@SELECT = 01
,@EXECUTE = 0
,@END_DATE_TIME = NULL
--
--*/
BEGIN
PRINT '1.1 START ' + OBJECT_NAME(@@PROCID) + ' ' + CONVERT(VARCHAR(13) ,GETDATE() ,114) + CHAR(13)
SET NOCOUNT ON;
DECLARE
@UTC_START_DATE_TIME DATETIME
,@UTC_END_DATE_TIME DATETIME
,@FLOAT_START_DATE_TIME FLOAT
,@FLOAT_END_DATE_TIME FLOAT
,@START_DATE_TIME DATETIME
,@OLD_LOGIC BIT = 0
,@ARCHIVE_TABLE_NAME VARCHAR(255)
,@TABLE_COUNT BIGINT
,@DATE_CONDITION NVARCHAR(MAX)
IF @END_DATE_TIME IS NULL
BEGIN
SELECT
@END_DATE_TIME = CONVERT(VARCHAR(10) ,GETDATE() - 1 ,121)
END
SELECT
@END_DATE_TIME = CONVERT(VARCHAR(10) ,@END_DATE_TIME ,121) + ' 23:59:59'
BEGIN --DEFAULTS
DECLARE
@ROW_INDEX BIGINT
,@ROWS_COUNT BIGINT
,@TABLE_NAME VARCHAR(250)
,@QUERY NVARCHAR(MAX)
,@FIELD_NAMES_01 NVARCHAR(MAX)
,@FIELD_NAMES_02 NVARCHAR(MAX)
,@DEFAULT_QUERY_01 NVARCHAR(MAX)
,@DEFAULT_QUERY_02 NVARCHAR(MAX)
,@DEFAULT_QUERY_03 NVARCHAR(MAX)
,@PARAMETER_DEFINATION NVARCHAR(500)
,@ROWS_PROCESSED BIGINT
SELECT
@ROW_INDEX = 1
,@ROWS_COUNT = 1
,@ROWS_PROCESSED = 0
BEGIN
SELECT @PARAMETER_DEFINATION = N'@QUERY_FIELD_NAMES_01 NVARCHAR(MAX) OUTPUT ,@QUERY_FIELD_NAMES_02 NVARCHAR(MAX) OUTPUT'
SELECT @QUERY = N'USE ' + @PARAMETER_DB_NAME
+ CHAR(13) + 'SELECT '
+ CHAR(13) + ' @QUERY_FIELD_NAMES_01 = CASE WHEN ISNULL( @QUERY_FIELD_NAMES_01 ,'''') = '''' THEN SC.name ELSE @QUERY_FIELD_NAMES_01 + CHAR(13) + '','' + SC.name END '
+ CHAR(13) + ' ,@QUERY_FIELD_NAMES_02 = CASE WHEN ISNULL( @QUERY_FIELD_NAMES_02 ,'''') = '''' THEN ''deleted.'' + SC.name ELSE @QUERY_FIELD_NAMES_02 + CHAR(13) + '',deleted.'' + SC.name END'
+ CHAR(13) + 'FROM'
+ CHAR(13) + ' SYS.columns AS SC'
+ CHAR(13) + ' LEFT OUTER JOIN sys.identity_columns AS SIC'
+ CHAR(13) + ' ON SIC.object_id = SC.object_id'
+ CHAR(13) + ' AND SIC.name = SC.name'
+ CHAR(13) + 'WHERE'
+ CHAR(13) + ' SC.object_id = OBJECT_ID(' + CHAR(39) + @PARAMETER_TABLE_NAME + CHAR(39) + ')'
+ CHAR(13) + ' AND ColumnProperty(OBJECT_ID(' + CHAR(39) + @PARAMETER_TABLE_NAME + CHAR(39) + ') , SC.name, ''IsComputed'') = 0'
+ CHAR(13) + ' AND SIC.name IS NULL'
+ CHAR(13) + 'ORDER BY'
+ CHAR(13) + ' SC.column_id'
EXECUTE SP_EXECUTESQL @QUERY ,@PARAMETER_DEFINATION
,@QUERY_FIELD_NAMES_01 = @FIELD_NAMES_01 OUTPUT
,@QUERY_FIELD_NAMES_02 = @FIELD_NAMES_02 OUTPUT;
END
BEGIN -- DEFAULT QUERY BUILD
SELECT @PARAMETER_DEFINATION = N'@QUERY_ROWS_COUNT BIGINT OUTPUT'
SELECT @DEFAULT_QUERY_01 = N';WITH CTE_PROCESS_TABLE_01 AS (SELECT'
+ CHAR(13) + @FIELD_NAMES_01
+ CHAR(13) + 'FROM ' + @PARAMETER_DB_NAME + '.DBO.' + @PARAMETER_TABLE_NAME + ' AS DVS WITH (NOLOCK)'
+ CHAR(13) + 'WHERE 1 = 1 '
-- HERE IS THE DATE PART NEED TO ADD
SELECT @DEFAULT_QUERY_02 = N''
+ CHAR(13) + ')DELETE FROM CTE_PROCESS_TABLE_01 OUTPUT'
+ CHAR(13) + @FIELD_NAMES_02
+ CHAR(13) + ' INTO ' -- + @TABLE_NAME
SELECT @DEFAULT_QUERY_03 = N''
+ CHAR(13) + 'SELECT @QUERY_ROWS_COUNT = @@ROWCOUNT'
END -- DEFAULT QUERY BUILD
END --DEFAULTS
IF @SELECT = 1
BEGIN
BEGIN --(@TABLE_COUNT)RECORD COUNT AVIALABLE OR NOT
SELECT @PARAMETER_DEFINATION = N'@QUERY_ROWS_COUNT BIGINT OUTPUT'
IF @IS_DATE_UTC = 1
BEGIN
SELECT @DATE_CONDITION = ' AND DVS.' + @PARAMETER_DATE_FIELD_NAME + ' <= ' + CHAR(39) + CONVERT(VARCHAR(23) , DATEADD(MINUTE ,-330 ,@END_DATE_TIME) ,121) + CHAR(39)
SELECT @QUERY = N'SELECT '
+ CHAR(13) + ' COUNT(1) AS RCOUNT'
+ CHAR(13) + ' ,DATEADD(MINUTE ,330 ,MIN(' + @PARAMETER_DATE_FIELD_NAME + ')) AS MIN_DT'
+ CHAR(13) + ' ,DATEADD(MINUTE ,330 ,MAX(' + @PARAMETER_DATE_FIELD_NAME + ')) AS MAX_DT'
+ CHAR(13) + ' ,CONVERT( VARCHAR(10) ,DATEADD(MINUTE ,330 ,' + @PARAMETER_DATE_FIELD_NAME + ') ,121) AS DAY_VALUE'
+ CHAR(13) + 'FROM '
+ CHAR(13) + ' ' + @PARAMETER_DB_NAME + '.DBO.' + @PARAMETER_TABLE_NAME + ' AS DVS WITH (NOLOCK)'
+ CHAR(13) + 'WHERE '
+ CHAR(13) + ' 1 = 1 '
--+ CHAR(13) + ' ' + @DATE_CONDITION
+ CHAR(13) + 'GROUP BY'
+ CHAR(13) + ' CONVERT( VARCHAR(10) ,DATEADD(MINUTE ,330 ,' + @PARAMETER_DATE_FIELD_NAME + ') ,121) WITH CUBE'
+ CHAR(13) + 'ORDER BY DAY_VALUE'
END
ELSE IF @IS_DATE_FLOAT = 1
BEGIN
SELECT @FLOAT_END_DATE_TIME = CAST( CAST( CONVERT(VARCHAR(10) ,@END_DATE_TIME ,121) AS DATETIME) AS FLOAT)
SELECT @DATE_CONDITION = ' AND DVS.' + @PARAMETER_DATE_FIELD_NAME + ' <= ' + CAST( @FLOAT_END_DATE_TIME AS VARCHAR)
SELECT @QUERY = N'SELECT '
+ CHAR(13) + ' COUNT(1) AS RCOUNT'
+ CHAR(13) + ' ,MIN( CAST( ' + @PARAMETER_DATE_FIELD_NAME + ' AS DATETIME)) AS MIN_DT'
+ CHAR(13) + ' ,MAX( CAST( ' + @PARAMETER_DATE_FIELD_NAME + ' AS DATETIME)) AS MAX_DT'
+ CHAR(13) + ' ,CONVERT( VARCHAR(10) ,CAST( ' + @PARAMETER_DATE_FIELD_NAME + ' AS DATETIME) ,121) AS DAY_VALUE'
+ CHAR(13) + 'FROM '
+ CHAR(13) + ' ' + @PARAMETER_DB_NAME + '.DBO.' + @PARAMETER_TABLE_NAME + ' AS DVS WITH (NOLOCK)'
+ CHAR(13) + 'WHERE '
+ CHAR(13) + ' 1 = 1 '
--+ CHAR(13) + ' ' + @DATE_CONDITION
+ CHAR(13) + 'GROUP BY'
+ CHAR(13) + ' CONVERT( VARCHAR(10) ,CAST( ' + @PARAMETER_DATE_FIELD_NAME + ' AS DATETIME) ,121) WITH CUBE'
+ CHAR(13) + 'ORDER BY DAY_VALUE'
END
ELSE
BEGIN
SELECT @DATE_CONDITION = ' AND DVS.' + @PARAMETER_DATE_FIELD_NAME + ' <= ' + CHAR(39) + CONVERT(VARCHAR(23) ,@END_DATE_TIME ,121) + CHAR(39)
SELECT @QUERY = N'SELECT '
+ CHAR(13) + ' COUNT(1) AS RCOUNT'
+ CHAR(13) + ' ,DATEADD(MINUTE ,0 ,MIN(' + @PARAMETER_DATE_FIELD_NAME + ')) AS MIN_DT'
+ CHAR(13) + ' ,DATEADD(MINUTE ,0 ,MAX(' + @PARAMETER_DATE_FIELD_NAME + ')) AS MAX_DT'
+ CHAR(13) + ' ,CONVERT( VARCHAR(10) ,DATEADD(MINUTE ,0 ,' + @PARAMETER_DATE_FIELD_NAME + ') ,121) AS DAY_VALUE'
+ CHAR(13) + 'FROM '
+ CHAR(13) + ' ' + @PARAMETER_DB_NAME + '.DBO.' + @PARAMETER_TABLE_NAME + ' AS DVS WITH (NOLOCK)'
+ CHAR(13) + 'WHERE '
+ CHAR(13) + ' 1 = 1 '
--+ CHAR(13) + ' ' + @DATE_CONDITION
+ CHAR(13) + 'GROUP BY'
+ CHAR(13) + ' CONVERT( VARCHAR(10) ,DATEADD(MINUTE ,0 ,' + @PARAMETER_DATE_FIELD_NAME + ') ,121) WITH CUBE'
+ CHAR(13) + 'ORDER BY DAY_VALUE'
END
--PRINT @QUERY
EXECUTE SP_EXECUTESQL @QUERY
--PRINT @QUERY
END
END
BEGIN
SELECT
@ROW_INDEX = 1
,@ROWS_COUNT = 2
WHILE @ROW_INDEX <= @ROWS_COUNT
BEGIN
BEGIN --(@TABLE_COUNT)RECORD COUNT AVIALABLE OR NOT
SELECT @PARAMETER_DEFINATION = N'@QUERY_ROWS_COUNT BIGINT OUTPUT'
IF @IS_DATE_UTC = 1
BEGIN
SELECT @DATE_CONDITION = ' AND DVS.' + @PARAMETER_DATE_FIELD_NAME + ' <= ' + CHAR(39) + CONVERT(VARCHAR(23) , DATEADD(MINUTE ,-330 ,@END_DATE_TIME) ,121) + CHAR(39)
END
ELSE IF @IS_DATE_FLOAT = 1
BEGIN
SELECT @FLOAT_END_DATE_TIME = CAST( CAST( CONVERT(VARCHAR(10) ,@END_DATE_TIME ,121) AS DATETIME) AS FLOAT)
SELECT @DATE_CONDITION = ' AND DVS.' + @PARAMETER_DATE_FIELD_NAME + ' <= ' + CAST( @FLOAT_END_DATE_TIME AS VARCHAR)
END
ELSE
BEGIN
SELECT @DATE_CONDITION = ' AND DVS.' + @PARAMETER_DATE_FIELD_NAME + ' <= ' + CHAR(39) + CONVERT(VARCHAR(23) ,@END_DATE_TIME ,121) + CHAR(39)
END
SELECT @QUERY = 'SELECT @QUERY_ROWS_COUNT = COUNT(1) FROM ' + @PARAMETER_DB_NAME + '.DBO.' + @PARAMETER_TABLE_NAME + ' AS DVS WITH (NOLOCK)'
+ CHAR(13) + 'WHERE 1 = 1 ' + @DATE_CONDITION
EXECUTE SP_EXECUTESQL @QUERY ,@PARAMETER_DEFINATION ,@QUERY_ROWS_COUNT = @TABLE_COUNT OUTPUT;
END
IF @TABLE_COUNT > 0
BEGIN
SELECT
@START_DATE_TIME = CONVERT(VARCHAR(8) ,@END_DATE_TIME ,121) + '01'
SELECT
@UTC_START_DATE_TIME = DATEADD(MINUTE ,-330 ,@START_DATE_TIME)
,@UTC_END_DATE_TIME = DATEADD(MINUTE ,-330 ,@END_DATE_TIME)
,@FLOAT_START_DATE_TIME = CAST( @START_DATE_TIME AS FLOAT)
SELECT
@FLOAT_END_DATE_TIME = CAST( CAST( CONVERT(VARCHAR(10) ,@END_DATE_TIME ,121) AS DATETIME) AS FLOAT)
SELECT
@ARCHIVE_TABLE_NAME = @PARAMETER_TABLE_NAME + '_' + REPLACE(CONVERT(VARCHAR(7) ,@START_DATE_TIME ,121) , '-' ,'_')
BEGIN --CHECK N CREATE TABLE IN ARCHIVE DATABASE
SELECT @QUERY = N'USE ' + @ARCHIVE_DB_NAME
+ CHAR(13) + 'IF NOT EXISTS(SELECT ST.object_id FROM SYS.tables AS ST WHERE ST.object_id = OBJECT_ID(' + CHAR(39) + @ARCHIVE_TABLE_NAME + CHAR(39) + '))'
+ CHAR(13) + 'BEGIN'
+ CHAR(13) + ' SELECT TOP 0 * INTO ' + @ARCHIVE_DB_NAME +'.DBO.' + @ARCHIVE_TABLE_NAME + ' FROM ' + @PARAMETER_DB_NAME + '.dbo.' + @PARAMETER_TABLE_NAME + ' AS DPDL WITH (NOLOCK)'
+ CHAR(13) + 'END'
EXECUTE SP_EXECUTESQL @QUERY
SELECT @QUERY = ''
END
BEGIN
SELECT
@TABLE_NAME = @Archive_DB_NAME + '.dbo.' + @ARCHIVE_TABLE_NAME
IF @IS_DATE_UTC = 1
BEGIN
SELECT @DATE_CONDITION = CHAR(13) + ' AND DVS.' + @PARAMETER_DATE_FIELD_NAME + ' BETWEEN ' + CHAR(39) + CONVERT(VARCHAR(23) ,@UTC_START_DATE_TIME ,121) + CHAR(39) + ' AND ' + CHAR(39) + CONVERT(VARCHAR(23) ,@UTC_END_DATE_TIME ,121) + CHAR(39) + ' '
END
ELSE IF @IS_DATE_FLOAT = 1
BEGIN
SELECT @DATE_CONDITION = CHAR(13) + ' AND DVS.' + @PARAMETER_DATE_FIELD_NAME + ' BETWEEN ' + CAST( @FLOAT_START_DATE_TIME AS VARCHAR) + ' AND ' + CAST( @FLOAT_END_DATE_TIME AS VARCHAR) + ' '
END
ELSE
BEGIN
SELECT @DATE_CONDITION = CHAR(13) + ' AND DVS.' + @PARAMETER_DATE_FIELD_NAME + ' BETWEEN ' + CHAR(39) + CONVERT(VARCHAR(23) ,@START_DATE_TIME ,121) + CHAR(39) + ' AND ' + CHAR(39) + CONVERT(VARCHAR(23) ,@END_DATE_TIME ,121) + CHAR(39) + ' '
END
SELECT @QUERY = @DEFAULT_QUERY_01
+ @DATE_CONDITION
+ @DEFAULT_QUERY_02 + @TABLE_NAME
+ @DEFAULT_QUERY_03
IF @EXECUTE = 1
BEGIN
PRINT CONVERT(VARCHAR(23), @START_DATE_TIME, 121) + ' ' + CONVERT(VARCHAR(23), @END_DATE_TIME, 121)
SELECT @ROWS_PROCESSED = 0
PRINT 'START 0.1 ' + @TABLE_NAME + ' ' + RIGHT('000000000000' + CAST(@ROWS_PROCESSED AS VARCHAR) ,LEN('000000000000')) + ' ' + CONVERT(VARCHAR(13) ,GETDATE() ,114)
BEGIN TRY
DECLARE
@LOOP_ROW_INDEX BIGINT
,@LOOP_ROWS_COUNT BIGINT
,@LOOP_ROWS_PROCESSED BIGINT
SET ROWCOUNT 10000
SELECT
@LOOP_ROW_INDEX = 1
,@LOOP_ROWS_COUNT = 2
WHILE @LOOP_ROW_INDEX <= @LOOP_ROWS_COUNT
BEGIN
SELECT @PARAMETER_DEFINATION = N'@QUERY_ROWS_COUNT BIGINT OUTPUT'
EXECUTE SP_EXECUTESQL @QUERY ,@PARAMETER_DEFINATION ,@QUERY_ROWS_COUNT = @LOOP_ROWS_PROCESSED OUTPUT;
IF ISNULL( @LOOP_ROWS_PROCESSED ,0) > 0
BEGIN
SELECT @ROWS_PROCESSED = ISNULL( @ROWS_PROCESSED ,0) + ISNULL( @LOOP_ROWS_PROCESSED ,0)
WAITFOR DELAY '00:00:00.500'
END
ELSE
BEGIN
SELECT @LOOP_ROW_INDEX = @LOOP_ROW_INDEX + 1
END
END
SET ROWCOUNT 0
END TRY
BEGIN CATCH
PRINT @TABLE_NAME + ' : ' + ERROR_MESSAGE()
PRINT '-----------------------------------------------------------------------------'
PRINT @QUERY
PRINT '-----------------------------------------------------------------------------'
END CATCH
SELECT @ROWS_PROCESSED = ISNULL( @ROWS_PROCESSED ,0)
PRINT 'STOPS 1.1 ' + @TABLE_NAME + ' ' + RIGHT('000000000000' + CAST(@ROWS_PROCESSED AS VARCHAR) ,LEN('000000000000')) + ' ' + CONVERT(VARCHAR(13) ,GETDATE() ,114) + CHAR(13)
SELECT
@ROWS_COUNT = @ROWS_COUNT + 1
,@ROWS_PROCESSED = 0
END
SELECT
@END_DATE_TIME = DATEADD(SECOND ,-1 ,@START_DATE_TIME)
END
END
SELECT @ROW_INDEX = @ROW_INDEX + 1
END
END
PRINT CHAR(13) + '1.1 STOPS ' + OBJECT_NAME(@@PROCID) + ' ' + CONVERT(VARCHAR(13) ,GETDATE() ,114)
SET NOCOUNT OFF;
END
GO
i have created a procedure to take Current database table backup to another database(archive), it will create table in the archive db, but i want to create a index as well in archive db, where as in my current database table contains the index.
Please suggest a better idea
Note:- Bold Italic and Underlined code(BEGIN --CHECK N CREATE TABLE IN ARCHIVE DATABASE) is the main table creating code
Thanks
Patel Mohamad
June 6, 2018 at 5:11 am
You mean you want to create the same index on the current table as on the new one? You'll need to query sys.indexes to generate the CREATE INDEX DDL.
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply