Oracle DBA by Example. Code samples, Scripts, Reference.: SQLServer code example - load data into tables and process data by delimiter

=======================
Scripts to load data
=======================

main_server_data_load.bat

bcp_load_server_data.bat
server_data_process.bat
move_files.bat

=======================
T-SQL to process data
=======================
main_populate_server_data

=======================
Scripts to load data
=======================
main_server_data_load.bat
REM call to bcp script
ECHO OFF
SET fileName=%1%
REM SET fileName=AUS_VODAF.txt
ECHO Handling file %fileName%
CALL bcp_load_server_data.bat %fileName%
CALL move_files.bat %fileName%

bcp_load_server_data.bat

REM load data with bcp utility
ECHO OFF

SET fileName=%1%

ECHO Loading data from file %fileName%

ECHO Starting to load data from file %fileName% to table SupportTool.dbo.[server_raw_data_input]

bcp SupportTool.dbo.[server_raw_data_input] in D:\serverData\input\%fileName% -S"10.135.100.106" -P"1qaz!QAZ" -c -e..\logs\Data_Load.err

ECHO Done

server_data_process.bat
REM invoke a procedure in SQL Server 2000, is done via osql utility
osql -E -S <server> -d <database> -Q "EXEC <schema>.<procedure>" > ../logs/process.log

move_files.bat

REM move processed files to history folder
ECHO OFF

SET from_fileName=%1%

SET to_fileName=%1%_loaded

ECHO Moving file %from_fileName%

ECHO move D:\serverData\input\%from_fileName% D:\serverData\history\%to_fileName%

REM move D:\serverData\input\%from_fileName% D:\serverData\history\%to_fileName%

=======================
T-SQL to process data
=======================
main_populate_server_data
clear_server_data_before_load
clear_server_data_by_server
populate_server_raw_data
populate_server_data
clear_server_data_after_load

main_populate_server_data
USE [SupportTool]
GO
/****** Object: StoredProcedure [dbo].[main_populate_server_data] Script Date: 12/13/2015 18:05:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Alec Kaplan>
-- Create date: <30 03 2015,,>
-- Description: <Populate server_xxx tables>
-- =============================================
--DROP PROCEDURE [dbo].[main_populate_server_data]
ALTER PROCEDURE [dbo].[main_populate_server_data]
AS
DECLARE @v_server_name [nvarchar](100);
DECLARE @v_module_name [nvarchar](100);
DECLARE @v_step_sts INT;
SET @v_server_name = '*';
SET @v_module_name = 'main_populate_server_data';
SET @v_step_sts = 0;

BEGIN
SET NOCOUNT ON;

EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Starting','*',@v_step_sts;
--------------------------------
EXEC [dbo].[add_server_trace] @v_server_name, @v_module_name, 'Calling clear_server_data_before_load','*',@v_step_sts;
EXEC [dbo].[clear_server_data_before_load];
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Call to clear_server_data_before_load Completed','*',@v_step_sts;
--------------------------------
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Calling populate_server_raw_data','*',@v_step_sts;
EXEC [dbo].[populate_server_raw_data]
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Call to populate_server_raw_data Completed','*',@v_step_sts;
--------------------------------
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Calling populate_server_data','*',@v_step_sts;
EXEC [dbo].[populate_server_data];
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Calling populate_server_data Completed','*',@v_step_sts;
--------------------------------
EXEC [dbo].[add_server_trace] @v_server_name, @v_module_name, 'Calling clear_server_data_after_load','*',@v_step_sts;
EXEC [dbo].[clear_server_data_after_load];
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Call to clear_server_data_after_load Completed','*',@v_step_sts;
--------------------------------

EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Finished','*',@v_step_sts;

END

clear_server_data_before_load
USE [SupportTool]
GO
/****** Object: StoredProcedure [dbo].[clear_server_data_before_load] Script Date: 12/13/2015 18:13:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Alec Kaplan>
-- Create date: <30 03 2015,,>
-- Description: <Populate server_xxx tables>
-- =============================================
--DROP PROCEDURE [dbo].[clear_server_data_before_load]
ALTER PROCEDURE [dbo].[clear_server_data_before_load]
AS
DECLARE @v_server_name [nvarchar](100);
DECLARE @v_module_name [nvarchar](100);
DECLARE @v_step_name [nvarchar](100);
DECLARE @v_step_sts INT;

SET @v_server_name = '*';
SET @v_module_name = 'clear_server_data';

BEGIN

SET NOCOUNT ON;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Starting','*',0;
--------------------------------

BEGIN TRANSACTION
--------------------------------
SET @v_step_name = 'DELETE FROM server_load_trace'
PRINT 'In clear_server_data Before '+ @v_step_name
DELETE FROM server_load_trace
SET @v_step_sts = @@ERROR
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @v_step_name,'Completed',@v_step_sts;
PRINT 'In clear_server_data. Step completed: '+ @v_step_name + ' status ' + CONVERT(varchar(1), @v_step_sts)
--------------------------------
SET @v_step_name = 'DELETE FROM server_raw_data'
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @v_step_name,'Starting',0;
PRINT 'In clear_server_data Before '+ @v_step_name
DELETE FROM server_raw_data
SET @v_step_sts = @@ERROR
EXEC [dbo].[add_server_trace] @v_server_name, @v_module_name, @v_step_name, 'Completed', @v_step_sts;
PRINT 'In clear_server_data. Step completed: '+ @v_step_name + ' status ' + CONVERT(varchar(1), @v_step_sts)
--------------------------------
COMMIT TRANSACTION
PRINT 'In clear_server_data '+ 'Finished'
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Finished','*',0;
--------------------------------

END

clear_server_data_by_server
USE [SupportTool]
GO
/****** Object: StoredProcedure [dbo].[clear_server_data_by_server] Script Date: 12/13/2015 18:08:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Alec Kaplan>
-- Create date: <30 03 2015,,>
-- Description: <Populate server_xxx tables>
-- =============================================
--DROP PROCEDURE [dbo].[clear_server_data_by_server]
ALTER PROCEDURE [dbo].[clear_server_data_by_server] @p_hostname nvarchar(100), @p_ip nvarchar(100)
AS
DECLARE @v_module_name [nvarchar](100);
DECLARE @v_step_name [nvarchar](100);
DECLARE @v_step_sts INT

SET @v_module_name = 'clear_server_data_by_server';
BEGIN

SET NOCOUNT ON;
BEGIN TRANSACTION
---------------------------------------------------------
PRINT 'Starting Delete by hostname ' + @p_hostname + ' and ip: ' + @p_ip
---------------------------------------------------------
SET @v_step_name = 'DELETE FROM server_details';
SET @v_step_sts = 0
PRINT 'Before ' + @v_step_name
EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Starting', @v_step_sts;
DELETE FROM server_details WHERE hostname = @p_hostname AND ip = @p_ip;
SET @v_step_sts = @@ERROR
EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Finished', @v_step_sts;
PRINT 'After ' + @v_step_name
---------------------------------------------------------
SET @v_step_name = 'DELETE FROM server_services';
SET @v_step_sts = 0
PRINT 'Before ' + @v_step_name
EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Starting', @v_step_sts;
DELETE FROM server_services WHERE hostname = @p_hostname AND ip = @p_ip;
SET @v_step_sts = @@ERROR
EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Finished', @v_step_sts;
PRINT 'After ' + @v_step_name
---------------------------------------------------------
SET @v_step_name = 'DELETE FROM server_features';
SET @v_step_sts = 0
PRINT 'Before ' + @v_step_name
EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Starting', @v_step_sts;
DELETE FROM server_features WHERE hostname = @p_hostname AND ip = @p_ip;
SET @v_step_sts = @@ERROR
EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Finished', @v_step_sts;
PRINT 'After ' + @v_step_name
---------------------------------------------------------
SET @v_step_name = 'DELETE FROM server_disks';
SET @v_step_sts = 0
PRINT 'Before ' + @v_step_name
EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Starting', @v_step_sts;
DELETE FROM server_disks WHERE hostname = @p_hostname AND ip = @p_ip;
SET @v_step_sts = @@ERROR
EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Finished', @v_step_sts;
PRINT 'After ' + @v_step_name
---------------------------------------------------------
PRINT 'Finished Handling hostname ' + @p_hostname + ' and ip: ' + @p_ip
---------------------------------------------------------
COMMIT TRANSACTION;

END

populate_server_raw_data
USE [SupportTool]
GO
/****** Object: StoredProcedure [dbo].[populate_server_raw_data] Script Date: 12/13/2015 18:06:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Alec Kaplan>
-- Create date: <30 03 2015,,>
-- Description: <Populate server_xxx tables>
-- =============================================
--DROP PROCEDURE [dbo].[populate_server_raw_data]
ALTER PROCEDURE [dbo].[populate_server_raw_data]
AS

DECLARE get_server_raw_data_cur CURSOR FOR
SELECT data_field
FROM server_raw_data_input;

DECLARE @v_hostname [nvarchar](100);
DECLARE @v_ip [nvarchar](100);
DECLARE @v_data_type [nvarchar](100);
DECLARE @v_field_01 [nvarchar](100);
DECLARE @v_field_02 [nvarchar](100);
DECLARE @v_field_03 [nvarchar](100);
DECLARE @v_field_04 [nvarchar](100);
DECLARE @v_field_05 [nvarchar](100);
DECLARE @v_field_06 [nvarchar](100);
DECLARE @v_field_07 [nvarchar](100);
DECLARE @v_field_08 [nvarchar](100);
DECLARE @v_field_09 [nvarchar](100);
DECLARE @v_field_10 [nvarchar](100);
DECLARE @v_field_11 [nvarchar](100);
DECLARE @v_field_12 [nvarchar](100);
DECLARE @v_field_13 [nvarchar](100);
DECLARE @v_field_14 [nvarchar](100);
DECLARE @v_field_15 [nvarchar](100);
DECLARE @v_field_16 [nvarchar](100);
DECLARE @v_field_17 [nvarchar](100);
DECLARE @v_field_18 [nvarchar](100);
DECLARE @v_field_19 [nvarchar](100);
DECLARE @v_field_20 [nvarchar](100);
DECLARE @v_field_21 [nvarchar](1000);

DECLARE @ItemList [nvarchar](4000);
DECLARE @DelimIndex INT
DECLARE @StartIndex INT
DECLARE @Delimiter [nvarchar](1);
DECLARE @TotalLength INT;
DECLARE @SearchStr [nvarchar](4000);
DECLARE @v_step_id INT;
DECLARE @v_step_sts INT;

DECLARE @v_server_name [nvarchar](100);
DECLARE @v_module_name [nvarchar](100);
DECLARE @step_name [nvarchar](100);
DECLARE @v_more_data INT;
SET @v_server_name = '*';
SET @v_module_name = 'populate_server_raw_data';

BEGIN

SET NOCOUNT ON;
SET @Delimiter = ',';
SET @v_step_sts = 0
--SET @v_step_id = 0;

EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Starting','*',@v_step_sts;

BEGIN TRANSACTION;

OPEN get_server_raw_data_cur
FETCH NEXT FROM get_server_raw_data_cur INTO @ItemList;
WHILE @@FETCH_STATUS = 0
BEGIN

------------------------------------------------------------
SET @v_field_01 = NULL
SET @v_field_02 = NULL
SET @v_field_03 = NULL
SET @v_field_04 = NULL
SET @v_field_05 = NULL
SET @v_field_06 = NULL
SET @v_field_07 = NULL
SET @v_field_08 = NULL
SET @v_field_09 = NULL
SET @v_field_10 = NULL
SET @v_field_11 = NULL
SET @v_field_12 = NULL
SET @v_field_13 = NULL
SET @v_field_14 = NULL
SET @v_field_15 = NULL
SET @v_field_16 = NULL
SET @v_field_17 = NULL
SET @v_field_18 = NULL
SET @v_field_19 = NULL
SET @v_field_20 = NULL
SET @v_field_21 = NULL
SET @v_more_data = 1
------------------------------------------------------------

SET @TotalLength = LEN(@ItemList);
SET @SearchStr = @ItemList;
SET @DelimIndex = 0;
SET @StartIndex = 0;

EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr,@v_step_sts;
---------------------------------------
PRINT 'Search String: '+ @SearchStr
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
IF @DelimIndex > 0
BEGIN
SET @v_hostname = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'host name',@v_hostname, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr,@v_step_sts;
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
---------------------------------------
PRINT 'Search String: '+ @SearchStr
IF @DelimIndex > 0
BEGIN
SET @v_ip = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'ip',@v_ip, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
---------------------------------------
PRINT 'Search String: '+ @SearchStr
IF @DelimIndex > 0
BEGIN
SET @v_data_type = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'data type',@v_data_type, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
---------------------------------------
PRINT 'compute @v_field_01 Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
IF (@DelimIndex > 0 AND @v_more_data = 1)
BEGIN
SET @v_field_01 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'field_01',@v_field_01, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE
BEGIN
SET @v_field_02 = @SearchStr
SET @v_more_data = 0
END
---------------------------------------
PRINT 'compute @v_field_02 Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
IF @DelimIndex > 0
BEGIN
SET @v_field_02 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'field_02',@v_field_02, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
---------------------------------------
SET @step_name = 'field_03'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_03 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_03 = @SearchStr
SET @v_more_data = 0
END
END
IF @v_field_03 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_03;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_03, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
SET @step_name = 'field_04'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_04 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_04 = @SearchStr
SET @v_more_data = 0
END
END
IF @v_field_04 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_04;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_04, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
SET @step_name = 'field_05'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_05 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_05 = @SearchStr
SET @v_more_data = 0
END
END
IF @v_field_05 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_05;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_05, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END

---------------------------------------
SET @step_name = 'field_06'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_06 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_06 = @SearchStr
SET @v_more_data = 0
END
END
IF @v_field_06 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_06;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_06, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END

---------------------------------------
SET @step_name = 'field_07'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_07 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_07 = @SearchStr
SET @v_more_data = 0
END
END
IF @v_field_07 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_07;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_07, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
SET @step_name = 'field_08'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_08 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_08 = @SearchStr
SET @v_more_data = 0
END
END
IF @v_field_08 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_08;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_08, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
SET @step_name = 'field_09'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_09 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_09 = @SearchStr
SET @v_more_data = 0
END
END
IF @v_field_09 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_09;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_09, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END ---------------------------------------
SET @step_name = 'field_10'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_10 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_10 = @SearchStr
SET @v_more_data = 0
END
END
IF @v_field_10 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_10;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_10, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
SET @step_name = 'field_11'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_11 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_11 = @SearchStr
SET @v_more_data = 0
END
END
IF @v_field_11 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_11;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_11, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
SET @step_name = 'field_12'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_12 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_12 = @SearchStr
SET @v_more_data = 0
END
END
IF @v_field_12 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_12;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_12, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
SET @step_name = 'field_13'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_13 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_13 = @SearchStr
SET @v_more_data = 0
END
END
IF @v_field_13 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_13;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_13, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
SET @step_name = 'field_14'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_14 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_14 = @SearchStr
SET @v_more_data = 0
END
END

IF @v_field_14 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_14;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_14, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END

---------------------------------------
SET @step_name = 'field_15'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_15 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_15 = @SearchStr
SET @v_more_data = 0
END
END

IF @v_field_15 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_15;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_15, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
SET @step_name = 'field_16'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_16 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_16 = @SearchStr
SET @v_more_data = 0
END
END

IF @v_field_16 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_16;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_16, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
SET @step_name = 'field_17'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_17 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_17 = @SearchStr
SET @v_more_data = 0
END
END

IF @v_field_17 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_17;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_17, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
SET @step_name = 'field_18'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_18 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_18 = @SearchStr
SET @v_more_data = 0
END
END

IF @v_field_18 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_18;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_18, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
SET @step_name = 'field_19'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_19 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_19 = @SearchStr
SET @v_more_data = 0
END
END

IF @v_field_19 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_19;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_19, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
SET @step_name = 'field_20'
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
IF (@DelimIndex > 0) BEGIN
PRINT 'compute '+ @step_name+' Inside IF'
SET @v_field_20 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
END
ELSE BEGIN
IF (@v_more_data = 1) BEGIN
PRINT 'compute '+ @step_name+' Inside ELSE'
SET @v_field_20 = @SearchStr
SET @v_more_data = 0
END
END

IF @v_field_20 IS NULL BEGIN
PRINT @step_name + ' is empty'
END
ELSE BEGIN
PRINT @step_name +' was set to ' + @v_field_20;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_20, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
END
---------------------------------------
PRINT 'MY Row: ' + @v_hostname+ ' ' + @v_ip+ ' ' + @v_data_type
PRINT 'MY Row: ' + ISNULL(@v_field_01,' ')+ ' ' + ISNULL(@v_field_02,' ') + ' ' + ISNULL(@v_field_03,' ') + ' ' + ISNULL(@v_field_04,' ') + ' ' + ISNULL(@v_field_05,' ') + ' ' + ISNULL(@v_field_06,' ') + ' ' + ISNULL(@v_field_07,' ') + ' '+ ISNULL(@v_field_08,' ') + ' ' + ISNULL(@v_field_09,' ') + ' '+ ISNULL(@v_field_10,' ')
PRINT 'MY Row: ' + ISNULL(@v_field_11,' ')+ ' ' + ISNULL(@v_field_12,' ') + ' ' + ISNULL(@v_field_13,' ') + ' ' + ISNULL(@v_field_14,' ') + ' ' + ISNULL(@v_field_15,' ') + ' ' + ISNULL(@v_field_16,' ') + ' ' + ISNULL(@v_field_17,' ') + ' '+ ISNULL(@v_field_18,' ') + ' ' + ISNULL(@v_field_19,' ') + ' '+ ISNULL(@v_field_20,' ')

---------------------------------------

INSERT INTO server_raw_data (hostname, ip, data_type ,
field_01, field_02, field_03, field_04, field_05, field_06, field_07, field_08, field_09, field_10,
field_11, field_12, field_13, field_14, field_15, field_16, field_17, field_18, field_19, field_20)
VALUES (@v_hostname, @v_ip, @v_data_type,
@v_field_01, @v_field_02, @v_field_03, @v_field_04, @v_field_05, @v_field_06, @v_field_07, @v_field_08, @v_field_09, @v_field_10,
@v_field_11, @v_field_12, @v_field_13, @v_field_14, @v_field_15, @v_field_16, @v_field_17, @v_field_18, @v_field_19, @v_field_20);

FETCH NEXT FROM get_server_raw_data_cur INTO @ItemList;

END
CLOSE get_server_raw_data_cur;
DEALLOCATE get_server_raw_data_cur;

COMMIT TRANSACTION;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Finished','*', @v_step_sts;

END

populate_server_data
USE [SupportTool]
GO
/****** Object: StoredProcedure [dbo].[populate_server_data] Script Date: 12/13/2015 18:10:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Alec Kaplan>
-- Create date: <30 03 2015,,>
-- Description: <Populate server_xxx tables>
-- =============================================
--DROP PROCEDURE [dbo].[populate_server_data]
ALTER PROCEDURE [dbo].[populate_server_data]
AS

DECLARE get_server_raw_data_pk_cur CURSOR
FOR SELECT hostname, ip
FROM server_raw_data
WHERE server_raw_data.data_type = 'SERVER'
-- AND server_raw_data.hostname = 'aus-vod-2-aps-1'
ORDER BY hostname, ip;
-- ORDER BY DECODE (server_raw_data.data_type,'SERVER','1', 'SERVICE','2','DISK','3',data_type)
-- ORDER BY CASE WHEN server_raw_data.data_type = 'SERVER' THEN '1'
-- WHEN server_raw_data.data_type = 'SERVICE' THEN '2'
-- WHEN server_raw_data.data_type = 'DISK' THEN '3'
-- ELSE server_raw_data.data_type
-- END

DECLARE @v_hostname [nvarchar](100);
DECLARE @v_ip [nvarchar](100);
DECLARE @v_data_type [nvarchar](100);
DECLARE @v_field_01 [nvarchar](100);


DECLARE @v_type_server [nvarchar](100);
DECLARE @v_type_service [nvarchar](100);
DECLARE @v_type_disk [nvarchar](100);

DECLARE @v_step_name [nvarchar](100);

DECLARE @v_step_data [nvarchar](1000);
DECLARE @v_service_name [nvarchar](100);
DECLARE @v_schema_name [nvarchar](100);
DECLARE @v_feature_list [nvarchar](1000);
DECLARE @v_step_sts INT;
DECLARE @v_module_name [nvarchar](100);
DECLARE @feature_delimiter [nvarchar](1);
DECLARE @DelimIndex INT;
DECLARE @Delimiter [nvarchar](1);
DECLARE @StartIndex INT
DECLARE @v_feature_name [nvarchar](100);
DECLARE @TotalLength INT;
DECLARE @SearchStr [nvarchar](1000);

SET @v_type_server = 'SERVER';
SET @v_type_service = 'SERVICE';
SET @v_type_disk = 'DISK';

SET @v_module_name = 'populate_server_data';
SET @feature_delimiter = '|';
BEGIN

SET NOCOUNT ON;

PRINT 'Staring '+ @v_module_name
PRINT 'Opening Cursor get_server_raw_data_pk_cur in '+ @v_module_name

------------------------------------------------
-- Start Delete Loop
------------------------------------------------
OPEN get_server_raw_data_pk_cur
FETCH NEXT FROM get_server_raw_data_pk_cur INTO @v_hostname, @v_ip;
WHILE @@FETCH_STATUS = 0
BEGIN
---------------------------------------------------
BEGIN TRANSACTION;
---------------------------------------------------

PRINT 'Calling to clear_server_data_by_server for '+ @v_hostname +' '+ @v_ip
EXEC [dbo].[clear_server_data_by_server] @v_hostname, @v_ip;
PRINT 'After clear_server_data_by_server'

COMMIT TRANSACTION;
FETCH NEXT FROM get_server_raw_data_pk_cur INTO @v_hostname, @v_ip

END
CLOSE get_server_raw_data_pk_cur

------------------------------------------------
-- Start Populate Loop
------------------------------------------------

OPEN get_server_raw_data_pk_cur
FETCH NEXT FROM get_server_raw_data_pk_cur INTO @v_hostname, @v_ip;
WHILE @@FETCH_STATUS = 0
BEGIN

---------------------------------------------------
BEGIN TRANSACTION;
---------------------------------------------------
INSERT INTO server_details (hostname, ip,server_type, total_memory, free_memory, cpu_cores, cpu_speed, cpu_model, redhat_version)
SELECT hostname, ip,
field_01, field_02, field_03, field_04, field_05, field_06, field_07
FROM server_raw_data
WHERE server_raw_data.hostname = @v_hostname
AND server_raw_data.ip = @v_ip
AND server_raw_data.data_type = @v_type_server;

SET @v_step_sts = @@ERROR
SET @v_step_name = 'INSERT INTO server_details';
SELECT @v_step_data = ' hostname = ' + @v_hostname + ' ; IP = ' + @v_ip + ' ; TYPE = '+@v_type_server
EXEC [dbo].[add_server_trace] @v_hostname, @v_module_name, @v_step_name, @v_step_data, @v_step_sts;
PRINT 'After '+@v_step_name + ' for '+ @v_hostname
---------------------------------------------------
SET @v_step_name = 'INSERT INTO server_services';
SELECT @v_step_data = 'Before INSERT INTO server_services hostname = ' + @v_hostname + ' ; IP = ' + @v_ip + ' ; TYPE = '+ @v_type_service
EXEC [dbo].[add_server_trace] @v_hostname, @v_module_name, @v_step_name, @v_step_data, @v_step_sts;
PRINT 'Before '+@v_step_name + ' for '+ @v_hostname + ' ; IP = ' + @v_ip;

INSERT INTO server_services (hostname, ip,
service_name, schema_name, db_server_ip, db_version, ig_version,
ig_common_version, ig_infra_version, crm_version, glr_version,ulticom_version,
tcap_list, cnf_list, omni_version, is_redundant,prov_base,
prov_version, rpt_version)
SELECT hostname, ip,
field_01, field_02, field_03, field_04, field_05,
field_06, field_07, field_08, field_09, field_10,
field_11, field_12, field_13, field_14, field_15,
field_16,field_17
FROM server_raw_data
WHERE server_raw_data.hostname = @v_hostname
AND server_raw_data.ip = @v_ip
AND server_raw_data.data_type = @v_type_service;

-- aus-vod-2-aps-1,10.98.20.22,SERVICE,aus-hut-lid,AUS_HUTCH_IRMQQ,10.98.20.22,4.0.017,
-- ig_version,ig_common_version,ig_infra_version,crm_version,glr_version,
-- ulticom_version,tcap_list,cnf_list,omni_version,is_redundant,prov_base,prov_version,
-- aus-vod-2-aps-1,10.98.20.22,SERVICE,aus-hut-lid,AUS_HUTCH_IRMQQ,10.98.20.22 ,4.0.017 ,,,,,,,,,,,,,,<feature_list>
-- hostname ,ip ,SERVICE,field_01 ,field_02 ,field_03 ,field_04 ,,,,,,,,,,,,,,<feature_list>


SET @v_step_sts = @@ERROR
SET @v_step_name = 'INSERT INTO server_services';
SELECT @v_step_data = ' hostname = ' + @v_hostname + ' ; IP = ' + @v_ip + ' ; TYPE = '+ @v_type_server
EXEC [dbo].[add_server_trace] @v_hostname, @v_module_name, @v_step_name,@v_step_data, @v_step_sts;
PRINT 'After '+@v_step_name + ' for '+ @v_hostname+ ' ; IP = ' + @v_ip
----------------------------------------------------
SET @v_step_name = 'INSERT INTO server_features';
PRINT 'Before '+@v_step_name + ' for '+ @v_hostname + ' ; IP = ' + @v_ip
PRINT 'feature list: '+@v_feature_list
SELECT @v_field_01 = field_01, @v_feature_list = field_18
FROM server_raw_data
WHERE server_raw_data.hostname = @v_hostname
AND server_raw_data.ip = @v_ip
AND server_raw_data.data_type = @v_type_service
PRINT 'Getting feature list'+ ' for '+ @v_hostname + ' ; IP = ' + @v_ip + ' data_type=' + @v_type_service
PRINT 'feature list: '+@v_feature_list
--Roaming Information| System| Utilities| Leakage Identifier|
SET @Delimiter = @feature_delimiter;
SET @TotalLength = LEN(@v_feature_list);
SET @SearchStr = @v_feature_list;
SET @StartIndex = 0;

SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
WHILE @DelimIndex > 0
BEGIN
SET @v_feature_name = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
PRINT 'Adding server_feature : ' + @v_hostname +' '+ @v_ip+' '+ @v_field_01+' '+ @v_feature_name
INSERT INTO server_features (hostname, ip, service_name, feature_name)
VALUES (@v_hostname, @v_ip, @v_field_01, @v_feature_name);
PRINT 'Adding server_feature Done';
SET @SearchStr = LTRIM(SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1))
SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
SET @TotalLength = LEN(@SearchStr);
END;

SET @v_step_sts = @@ERROR
SELECT @v_step_data = ' hostname = ' + @v_hostname + ' ; IP = ' + @v_ip + ' ; TYPE = '+ @v_type_server
EXEC [dbo].[add_server_trace] @v_hostname, @v_module_name, @v_step_name, @v_step_data, @v_step_sts;
PRINT 'After '+@v_step_name + ' for '+ @v_hostname + ' ; IP = ' + @v_ip

---------------------------------------------------
SET @v_step_name = 'INSERT INTO server_disks';
SELECT @v_step_data = 'Before INSERT INTO server_disks hostname = ' + @v_hostname + ' ; IP = ' + @v_ip + ' ; TYPE = '+ @v_type_disk
EXEC [dbo].[add_server_trace] @v_hostname, @v_module_name, @v_step_name, @v_step_data, @v_step_sts;
PRINT 'Before '+@v_step_name + ' for '+ @v_hostname + ' ; IP = ' + @v_ip

INSERT INTO server_disks (hostname, ip, mount, total, used, free, used_pct)
SELECT hostname, ip, field_05, field_01, field_02, field_03, field_04
FROM server_raw_data
WHERE server_raw_data.hostname = @v_hostname
AND server_raw_data.ip = @v_ip
AND server_raw_data.data_type = @v_type_disk;
---------------------------------------------------
SET @v_step_sts = @@ERROR

SELECT @v_step_data = 'After INSERT INTO server_disks hostname = ' + @v_hostname + ' IP = ' + @v_ip + ' TYPE = '+@v_type_disk
--EXEC [dbo].[add_server_trace] @v_hostname, @v_module_name, @v_step_name, @v_step_data, @v_step_sts;
PRINT 'After '+@v_step_name + ' for '+ @v_hostname + ' ; IP = ' + @v_ip + ' ; TYPE = '+@v_type_disk
---------------------------------------------------

INSERT INTO server_log(hostname, ip, data_type) VALUES (@v_hostname, @v_ip, 'SERVER');

COMMIT TRANSACTION;
FETCH NEXT FROM get_server_raw_data_pk_cur INTO @v_hostname, @v_ip

END
CLOSE get_server_raw_data_pk_cur
DEALLOCATE get_server_raw_data_pk_cur

END

clear_server_data_after_load

USE [SupportTool]

/****** Object: StoredProcedure [dbo].[clear_server_data_after_load] Script Date: 12/13/2015 18:12:29 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: <Alec Kaplan>

-- Create date: <30 03 2015,,>

-- Description: <Populate server_xxx tables>

-- =============================================

--DROP PROCEDURE [dbo].[clear_server_data_after_load]

ALTER PROCEDURE [dbo].[clear_server_data_after_load]

DECLARE @v_server_name [nvarchar](100);

DECLARE @v_module_name [nvarchar](100);

DECLARE @v_step_name [nvarchar](100);

DECLARE @v_step_sts INT;

SET @v_server_name = '*';

SET @v_module_name = 'clear_server_data';

BEGIN

SET NOCOUNT ON;

EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Starting','*',0;

--------------------------------

BEGIN TRANSACTION

--------------------------------

SET @v_step_name = 'Skip Delete from server_raw_data_input'

PRINT 'In clear_server_data Before '+ @v_step_name

EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @v_step_name,'Starting', 0;

--DELETE FROM server_raw_data_input

SET @v_step_sts = @@ERROR

EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @v_step_name,'Completed', @v_step_sts;

--------------------------------

COMMIT TRANSACTION

PRINT 'In clear_server_data '+ 'Finished'

EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Finished','*',0;

--------------------------------

END

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Sunday, December 13, 2015

SQLServer code example - load data into tables and process data by delimiter

No comments:

Post a Comment