Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data conversion data validating using information_schema.columns


Data conversion data validating using information_schema.columns

Author
Message
doodleshelp
doodleshelp
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
patrickmcginnis59 10839
patrickmcginnis59 10839
SSC Eights!
SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)

Group: General Forum Members
Points: 887 Visits: 5111
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!

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search