Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data conversion data validating using information_schema.columns Expand / Collapse
Author
Message
Posted Sunday, March 24, 2013 8:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2014 8:29 PM
Points: 2, Visits: 12
I am looking for an efficient to validate data for a data conversion. The path I am taking is importing data into a working db \ tables and then run through a series of validation checks. My plan was to start by checking the length of data to import against the information_schema.columns of the varchar fields of the table I am importing into. The problem I am having is how to loop through the loop through the varchar data_type fields of a specific table. I can get it to work when I filter on a specific column_name but would like to program it to loop through all of the varchar fields of the selected table. Any creative solutions to help? Thanks.
Post #1434727
Posted Tuesday, April 9, 2013 1:10 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 411, Visits: 2,413
Interesting proposition! Posting this for discussion's sake, use at your own risk!

for each destination table TABLE_NAME, have a matching table TABLE_NAME_IMPORT with the same named columns.

IF OBJECT_ID('tempdb..#COLUMNS_2_CHECK') is not null
BEGIN
DROP TABLE #COLUMNS_2_CHECK
END

IF OBJECT_ID('tempdb..#ERRORS') is not null
BEGIN
DROP TABLE #ERRORS
END

CREATE TABLE #COLUMNS_2_CHECK (
ID INT IDENTITY(1,1),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(100),
CHARACTER_MAXIMUM_LENGTH INT
)

CREATE TABLE #ERRORS(
COLUMN_NAME VARCHAR(500),
TABLE_NAME VARCHAR(500),
COLUMN_VALUE VARCHAR(1000),
DATA_COLUMN_LENGTH INT,
CHARACTER_MAXIMUM_LENGTH INT
)

INSERT INTO #COLUMNS_2_CHECK
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'varchar'
AND TABLE_NAME IN ('example_table_name','example_table_name_2')

DECLARE @COLNUM INT
DECLARE @DYNSQL VARCHAR(500)
DECLARE @COLUMN_NAME VARCHAR(500)
DECLARE @TABLE_NAME VARCHAR(500)
DECLARE @CHARACTER_MAXIMUM_LENGTH VARCHAR(10)
DECLARE @STAGE_SUFFIX VARCHAR(100)

SET @STAGE_SUFFIX = '_IMPORT'

SET @COLNUM = (SELECT COUNT(*) FROM #COLUMNS_2_CHECK)
WHILE @COLNUM > 0
BEGIN
SELECT @COLUMN_NAME = COLUMN_NAME,
@TABLE_NAME = TABLE_NAME,
@CHARACTER_MAXIMUM_LENGTH = CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH)
FROM #COLUMNS_2_CHECK
WHERE ID = @COLNUM

SET @DYNSQL =
'INSERT INTO #ERRORS (COLUMN_NAME, TABLE_NAME, COLUMN_VALUE, DATA_COLUMN_LENGTH, CHARACTER_MAXIMUM_LENGTH) ' +
'SELECT ''' + @COLUMN_NAME + ''' COLUMN_NAME, ''' + @TABLE_NAME + ''' TABLE_NAME, ' +
@COLUMN_NAME + ' COLUMN_VALUE, DATALENGTH(' + @COLUMN_NAME + ') DATA_COLUMN_LENGTH, ' +
@CHARACTER_MAXIMUM_LENGTH + ' CHARACTER_MAXIMUM_LENGTH FROM ' + @TABLE_NAME + @STAGE_SUFFIX +
' WHERE DATALENGTH(' + @COLUMN_NAME + ') > ' + @CHARACTER_MAXIMUM_LENGTH
EXEC (@DYNSQL)
SET @COLNUM = @COLNUM - 1
END
SELECT * FROM #ERRORS


edit, left in an extra '+', my bad!
Post #1440515
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse