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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy