February 18, 2015 at 3:47 am
Hi,
I would appreciate any help. I am trying to loop through a table which has table metadata and create a:
- 'STORED PROCEDURE'
- This will SELECT all the data in the table and add a hashed column at the end
- Each table has a unique ID
-
Instead of a cursor would there be a set-based approach to achieving this?
METADATA TABLE :
IDTableNameColumnNameHashByteCalculation
111dbo.TableAColA CAST(ISNULL(LEFT(CONVERT(VARCHARColA, 120), 10),'NA') AS varchar) + '|' +
111dbo.TableAColBCAST(ISNULL(LEFT(CONVERT(VARCHAR,ColB, 120), 10),'NA') AS varchar) + '|' +
111dbo.TableAColCISNULL(ColC,'NA') + '|' +
111dbo.TableAColDISNULL(ColD,'NA') + '|' +
222dbo.TableBColAACAST(ISNULL(LEFT(CONVERT(VARCHAR,ColAA, 120), 10),'NA') AS varchar) + '|' +
222dbo.TableBColBBISNULL(ColBB,'NA') + '|' +
222dbo.TableBColCCISNULL(ColCC,'NA') + '|' +
From the above data I want to generate:
SELECT
ColA
,ColB
,ColC
,ColD
, (CAST(ISNULL(LEFT(CONVERT(VARCHARColA, 120), 10),'NA') AS varchar) + '|' + CAST(ISNULL(LEFT(CONVERT(VARCHAR,ColB, 120), 10),'NA') AS varchar) + '|' + ISNULL(ColC,'NA') + '|' + ISNULL(ColD,'NA') + '|' )
FROM dbo. TableA
SELECT
ColAA
,ColBB
,ColCC
,
(
CAST(ISNULL(LEFT(CONVERT(VARCHAR,ColAA, 120), 10),'NA') AS varchar) + '|' + ISNULL(ColBB,'NA') + '|' + 222dbo.TableBColCCISNULL(ColCC,'NA') + '|'
)
FROM dbo. TableB
February 18, 2015 at 6:29 am
well something like this gives me the componenents to build a query;
for me, the trailing plus sign does not belong in the data, i'd use FOR XML and use the pluss sign as the delimiter instead; otherwise you have to append something to the very end;
note the data you pasted, probably due to the obfuscation, creates invalid commands since theres a missing comma after varchar in the hashedvalues strings.
/*--Results
TableNameColumnsHashVals
dbo.TableAColA,ColB,ColC,ColDCAST(ISNULL(LEFT(CONVERT(VARCHARColA, 120), 10),'NA') AS varchar) + '|' + CAST(ISNULL(LEFT(CONVERT(VARCHAR,ColB, 120), 10),'NA') AS varchar) + '|' + ISNULL(ColC,'NA') + '|' + ISNULL(ColD,'NA') + '|' +
dbo.TableBColAA,ColBB,ColCCCAST(ISNULL(LEFT(CONVERT(VARCHAR,ColAA, 120), 10),'NA') AS varchar) + '|' + ISNULL(ColBB,'NA') + '|' + ISNULL(ColCC,'NA') + '|' +
my code:
;WITH METADATATABLE([ID],[TableName],[ColumnName],[HashByteCalculation])
AS
(
SELECT '111','dbo.TableA','ColA','CAST(ISNULL(LEFT(CONVERT(VARCHARColA, 120), 10),''NA'') AS varchar) + ''|'' +' UNION ALL
SELECT '111','dbo.TableA','ColB','CAST(ISNULL(LEFT(CONVERT(VARCHAR,ColB, 120), 10),''NA'') AS varchar) + ''|'' +' UNION ALL
SELECT '111','dbo.TableA','ColC','ISNULL(ColC,''NA'') + ''|'' +' UNION ALL
SELECT '111','dbo.TableA','ColD','ISNULL(ColD,''NA'') + ''|'' +' UNION ALL
SELECT '222','dbo.TableB','ColAA','CAST(ISNULL(LEFT(CONVERT(VARCHAR,ColAA, 120), 10),''NA'') AS varchar) + ''|'' +' UNION ALL
SELECT '222','dbo.TableB','ColBB','ISNULL(ColBB,''NA'') + ''|'' +' UNION ALL
SELECT '222','dbo.TableB','ColCC','ISNULL(ColCC,''NA'') + ''|'' +'
)
SELECT DISTINCT
t.[TableName],
sq.Columns,
sq.HashVals
FROM METADATATABLE t
JOIN (
SELECT [TableName],
--DELIMITED BY COMMA
Columns = STUFF((SELECT ',' + [ColumnName]
FROM METADATATABLE sc
WHERE sc.[TableName] = s.[TableName]
FOR XML PATH('')),1,1,''),
--DELIMITED BY SINGLE SPACE
HashVals = STUFF((SELECT ' ' + [HashByteCalculation]
FROM METADATATABLE sc
WHERE sc.[TableName] = s.[TableName]
FOR XML PATH('')),1,1,'')
FROM METADATATABLE s
) sq ON t.[TableName] = sq.[TableName]
Lowell
February 18, 2015 at 4:02 pm
Thank you .. this is much better than the cursor option which is there at present!
Your help is much appreciated!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply