January 12, 2017 at 9:29 am
--==================================================================================================
-- These variables could be parameters for a stored procedure
--==================================================================================================
DECLARE @pFilePath VARCHAR(8000)
,@pFmtFilePath VARCHAR(8000)
;
SELECT @pFilePath = 'C:\Temp\test_loading_UnixCommandOutput_to_SQL.txt'
,@pFmtFilePath = 'C:\Temp\WholeRow.BCPFmt'
;
--==================================================================================================
-- Temp Tables
--==================================================================================================
--===== If the Temp Tables exist, drop them to make reruns in SSMS easier.
-- This section may be commented out if part of a stored procedure
IF OBJECT_ID('tempdb..#ImportStaging' ,'U') IS NOT NULL DROP TABLE #ImportStaging;
IF OBJECT_ID('tempdb..#ColumnPosition','U') IS NOT NULL DROP TABLE #ColumnPosition;
IF OBJECT_ID('tempdb..#Result' ,'U') IS NOT NULL DROP TABLE #Result;
--===== Create the table that will contain the data as whole rows
CREATE TABLE #ImportStaging
(
RowNum INT IDENTITY(0,1) PRIMARY KEY CLUSTERED
,WholeRow VARCHAR(8000)
)
;
--===== Create the table that will hold the parsed results.
CREATE TABLE #Result
(
RowNum INT NOT NULL PRIMARY KEY CLUSTERED
,LogicalServerName VARCHAR(256)
,[Primary] VARCHAR(256)
,assistUser VARCHAR(256)
,[Role] INT
)
--==================================================================================================
-- Presets
--==================================================================================================
--===== Supress the auto-display of rowcounts for appearance sake.
SET NOCOUNT ON
;
--===== Common Local Variables
DECLARE @SQL VARCHAR(8000)
;
--==================================================================================================
-- Import the raw data as whole rows so that we can work on them.
-- Note that this uses a BCP format file because the file doesn't have a "RowNum" column.
--==================================================================================================
--===== Import the file as whole rows including the header,
-- which will live at RowNum = 0.
SELECT @SQL = REPLACE(REPLACE(REPLACE('
BULK INSERT #ImportStaging
FROM <<@pFilePath>>
WITH
(
BATCHSIZE = 0
,CODEPAGE = "RAW"
,DATAFILETYPE = "char"
,FORMATFILE = <<@pFmtFilePath>>
,TABLOCK
)
;'
,'"' ,'''')
,'<<@pFilePath>>' ,QUOTENAME(@pFilePath,''''))
,'<<@pFmtFilePath>>',QUOTENAME(@pFmtFilePath,''''))
;
--===== Print and then execute the Dynamic SQL.
PRINT @SQL;
EXEC (@SQL)
;
--==================================================================================================
-- Determine the "shape" of the data by finding the starting position of each column in
-- the header row.
--==================================================================================================
--===== Determine the start of each column by finding the first character of each column name.
-- Note that this assumes that there will never be spaces in any of the column names.
-- This uses a "Tally Table" function to find the start of each column by looking for characters
-- that have a space just to the left of them. Of course, we also assume the first character
-- is the beginning of the first column, as well.
WITH cteColInfo AS
(
SELECT RowNum = ROW_NUMBER() OVER (ORDER BY t.N)
,ColStart = t.N
FROM #ImportStaging stg
CROSS APPLY dbo.fnTally(1,LEN(WholeRow)) t
WHERE stg.RowNum = 0
AND (t.N = 1 OR SUBSTRING(stg.WholeRow,t.N-1,2) LIKE ' [^ ]')
)
SELECT *
,ColName = CASE
WHEN RowNum = 1 THEN 'LogicalServerName'
WHEN RowNum = 2 THEN '[Primary]'
WHEN RowNum = 3 THEN 'assistUser'
WHEN RowNum = 4 THEN 'Role'
ELSE 'ERROR'
END
INTO #ColumnPosition
FROM cteColInfo
;
--==================================================================================================
-- Build the Dynamic SQL to correctly split the input data based on the variable column
-- positions discovered above.
--==================================================================================================
--===== Reset the @SQL variable so that we can overload it to build the "Select List" that will do
-- the splitting for us.
SELECT @SQL = ''
;
--===== Create the Dynamic "Select List".
-- This uses a simple self-join on the #ColumnPosition table to calculate the lengths.
-- This could be simplified in 2012 using the new Lead/Lag functionality.
SELECT @SQL = @SQL + CHAR(10)
+ REPLACE(REPLACE(REPLACE(REPLACE(
'<<indent>>,<<ColName>> = LTRIM(RTRIM(SUBSTRING(WholeRow,<<ColStart>>,<<ColLength>>)))'
,'<<ColName>>' , CONVERT(CHAR(20),lo.ColName))
,'<<ColStart>>' , CONVERT(VARCHAR(10),lo.ColStart))
,'<<ColLength>>', CONVERT(VARCHAR(10),ISNULL(hi.ColStart-lo.ColStart,8000)))
,'<<indent>>' , SPACE(8))
FROM #ColumnPosition lo
LEFT JOIN #ColumnPosition hi ON lo.RowNum = hi.RowNum - 1
;
--===== Add the static portion of the Dynamnic SQL to the SELECT list we just created above.
SELECT @SQL = '
INSERT INTO #Result
(RowNum,LogicalServerName,[Primary],assistUser,[Role])
SELECT RowNum' + @SQL + '
FROM #ImportStaging
WHERE RowNum > 0
ORDER BY RowNum
;'
--===== Display and then execute the Dynamic SQL to populate the results table.
PRINT @SQL;
EXEC (@SQL)
;
--==================================================================================================
-- Do a "Data Smear" to populate any missing column 1 and column 2 data.
-- This uses a "Quirky Update", which works in any version of SQL Server.
-- If you're using 2012+, this could be converted to more modern and supported by MS.
--==================================================================================================
--===== Declare the "forwarding" variables for the "Quirky Update"
DECLARE @PrevLogicalServerName VARCHAR(256)
,@PrevPrimary VARCHAR(256)
,@SafetyCounter INT
;
--===== Preset the safety count to assume that there's at least one row in the #Result table.
SELECT @SafetyCounter = 1
;
--===== Do the "Quirky Update" using the proprietary "3 part update" that SQL Server has.
-- The safety counter is to ensure that if MS ever breaks updates for this method, the code
-- will fail and alert us to the problem. Replace this code with more modern "Lead/Lag" code
-- if that ever happens (this code works on all versions through 2016).
UPDATE tgt
SET @PrevLogicalServerName
= LogicalServerName
= CASE
WHEN LogicalServerName > ''
THEN LogicalServerName
ELSE @PrevLogicalServerName
END
,@PrevPrimary
= [Primary]
= CASE
WHEN LogicalServerName > ''
THEN LogicalServerName
ELSE @PrevLogicalServerName
END
,@SafetyCounter
= CASE
WHEN RowNum = @SafetyCounter
THEN @SafetyCounter + 1
ELSE 'Safety counter violation'
END
FROM #Result tgt WITH (TABLOCKX,INDEX(1))
OPTION (MAXDOP 1)
;
--==================================================================================================
-- Display the final result.
-- From here, you could use the contents of the #Result table to populate a final table
-- for all such results.
--==================================================================================================
SELECT LogicalServerName
,[Primary]
,assistUser
,[Role]
FROM #Result
ORDER BY RowNum
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply