March 24, 2013 at 8:05 pm
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.
April 9, 2013 at 1:10 pm
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply