Data conversion data validating using information_schema.columns

  • doodleshelp

    SSC Enthusiast

    Points: 114

    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.

  • x

    SSC-Insane

    Points: 23433

    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!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply