SSC Forum Updates
-
Is there any way to increase the resolution of images posted, currently they are far to small to be usable?
😎 - Eirikur Eiriksson - Friday, January 20, 2017 8:43 AM
It would be nice to be able to adjust the resolution of images as one is posting too.
-
Zounds. The SQL Code IFCode windows are pretty much a train wreck now. If I copy and paste from SSMS, indentation (leading spaces) disappears and double, triple, and, sometimes, quadruple spacing occurs. If I copy and paste to Notepad and then copy and paste from Notepad to the IFCode, the indentation and line spacing is fine but the colors make absolutely no sense at all. All vestiges of correct coloration of dynamic SQL are all but lost.
Here's some of the latest code I tried to post using the SQL Code IFCode. The first one is a paste directly from SSMS and the second one is a paste from Notepad. To be sure, we shouldn't have to go through Notepad. The copy and paste from SSMS should work.
--==================================================================================================
-- 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.
+ 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
;
This is the paste from Notepad.
--==================================================================================================
-- 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
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 - Sunday, January 22, 2017 9:14 PM
Hi Jeff,
Do you mind me asking what browser (and version) you're using please.
Thanks
Damon
-
--==================================================================================================
-- 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>>Wink))'
,'<<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
Interestingly, I get a very different paste when I do it from SSMS (as I highlighted in my earlier post). I've used Jeff's Notepad paste and put it into SSMS, then copied and pasted that into SSC. As you can see, you get something different again! (Notice the allignments are all over the place). I actually lose whitespace, instead of gaining it.I am using Firefox 50.1, and Windows 7 here. Also, the cursor bug still exists it seems, that's really annoying. Especially when pasting (as it either loses focus, or just end up somewhere random), and half the time is one character behind where you type. Considering my posts are riddled with typoes as it is (yeah, my bad), it makes them a real pain to fix them, as backspace doesn't delete the characters it should! >_<
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
There seems to be something wrong with posting quotes.
Works fine when I try here, but exactly the same thing doesn't work properly in another thread: https://www.sqlservercentral.com/Forums/FindPost1852973.aspx
Definitely something odd going on with quotes:
https://www.sqlservercentral.com/Forums/FindPost1853001.aspxGail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Maybe not give new users the ability to create polls:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Doing what Jeff did, using his code...
--==================================================================================================
-- 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.
+ REPLACE(REPLACE(REPLACE(REPLACE(
'<<indent>>,<<ColName>> = LTRIM(RTRIM(SUBSTRING(WholeRow,<<ColStart>>,<<ColLength>>Wink))'
,'<<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
;
Above is
cut-n-paste from SSMS.
Below is cut-n-paste from notepad.--==================================================================================================
-- 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>>Wink))'
,'<<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
;I am using IE 11.0.9600.18537, Update versions: 11.0.38.
- Brandie Tarvin - Monday, January 23, 2017 4:24 AM
What the heck?
The "Above is cut-n-paste is from SSMS" was originally all on one line when I first wrote the post. It should not have cracked the line like that. And when I went back to edit mode to fix the line, it wouldn't let me backspace from cut-n-paste to move up to the line above with the "Above is". In fact, it deleted the "c" and then just stayed stuck.
What's going on with that?
- Thom A - Monday, January 23, 2017 4:20 AMMaybe not give new users the ability to create polls:
You're just jealous that you didn't think of that one first. @=)
- Brandie Tarvin - Monday, January 23, 2017 4:24 AM
Thanks Brandie, We're looking into this. It looks like IE is doing some strange things with formatting and is preserving formatting between SSMS and IE which then gets wrapped in a further format with the IFCode.
- Brandie Tarvin - Monday, January 23, 2017 4:28 AMThom A - Monday, January 23, 2017 4:20 AMMaybe not give new users the ability to create polls:
You're just jealous that you didn't think of that one first. @=)
Don't worry, I'm in the middle of making a Poll where you can vote about what's better; my linkedIn profile, a local furniture store, or a website you can illegally watch American Football games on. I'll ensure to include links to all websites in the Poll results and questions ^_^
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Monday, January 23, 2017 4:41 AMBrandie Tarvin - Monday, January 23, 2017 4:28 AMThom A - Monday, January 23, 2017 4:20 AMMaybe not give new users the ability to create polls:
You're just jealous that you didn't think of that one first. @=)
Don't worry, I'm in the middle of making a Poll where you can vote about what's better; my linkedIn profile, a local furniture store, or a website you can illegally watch American Football games on. I'll ensure to include links to all websites in the Poll results and questions ^_^
Hurry up! The NFL season is almost over. Until then...Go Falcons!
- Luis Cazares - Monday, January 23, 2017 6:44 AM
All this time, I honestly thought Falcons were a Hockey team.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Luis Cazares - Monday, January 23, 2017 6:44 AMThom A - Monday, January 23, 2017 4:41 AMBrandie Tarvin - Monday, January 23, 2017 4:28 AMThom A - Monday, January 23, 2017 4:20 AMMaybe not give new users the ability to create polls:
You're just jealous that you didn't think of that one first. @=)
Don't worry, I'm in the middle of making a Poll where you can vote about what's better; my linkedIn profile, a local furniture store, or a website you can illegally watch American Football games on. I'll ensure to include links to all websites in the Poll results and questions ^_^
Hurry up! The NFL season is almost over. Until then...Go Falcons!
Don't worry, Luis. It's always some season somewhere. I'm sure some sports organization will have something to post. The cheap kitchens and muscle-building supplements won't monopolize the spam.
Viewing 15 posts - 181 through 195 (of 224 total)
You must be logged in to reply to this topic. Login to reply