Loading data from untyped tables to tables is a very frequently occurring scenario in a Data warehouse application.Normally we load all the data we need in a table which is NVARCHAR or VARCHAR column and then check , row-by-row if all the data satisfies the data type check. If the row passes the type check validation then it is loaded to the typed else it is flagged as INVALID and / or is redirected to another table where we have error out records. So far , so good…But there are few things which are quite painful about this processes.
No 1:- You have to write some kind of LOOP operation for this .
No 2:- You need to write code for each untyped to typed table load.
Even if you use SSIS and use error redirect the INSERT operation becomes row-by-row and no longer is set based which again hits the performance.
If we look at the problem scenario we have
1. A Untyped Table
2. A Typed Table
3. A Flag Column to mark Invalid records
4. A table where all errors go
I am going to try to write a Stored Procedure that does that just. Pass any typed table to it and untyped typed and / or error table , Is_Valid column name (the 4 parameters mentioned above) and it will just find out all the rows that satisfy the type check validation and load then to typed table and will mark the other rows as invalid.
So as long as you pass the typed table name and corresponding untyped table name , where all the column of typed table are in the untyped table then this SP should work .
Lets Create 2 tables Untyped and Typed
CREATE TABLE UNTYPED
(
ID VARCHAR(20)
,TITLE VARCHAR(20)
,NAME VARCHAR(20)
,SALARY VARCHAR(20)
,IS_VALID BIT
)
CREATE TABLE TYPED
(
ID INT
,TITLE VARCHAR(20)
,NAME VARCHAR(20)
,SALARY FLOAT
)
INSERT INTO UNTYPED VALUES('1','Mr','Ash','100.00',NULL)
INSERT INTO UNTYPED VALUES('2','Mr','John','300.00',NULL)
INSERT INTO UNTYPED VALUES('3','Mr','Doe','400.00',NULL)
INSERT INTO UNTYPED VALUES('4','Mr','Bell','500.00',NULL)
INSERT INTO UNTYPED VALUES('5','Mr','Jack','150.00',NULL)
INSERT INTO UNTYPED VALUES('6','Miss','Jane','200.00',NULL)
INSERT INTO UNTYPED VALUES('7','Mrs','Ann','130.00',NULL)
INSERT INTO UNTYPED VALUES('8','12','Judy','Foo',NULL)
INSERT INTO UNTYPED VALUES('Bar','13','Jude','BAR',NULL)
So The Data in the untyped table Now is
So This is this is the SP . Pleas read the comments. You may have to extend it a bit and might like to add more error handling etc.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_load_untyped_to_typed_staging]
@stg_unTyped_table_name NVARCHAR(256)
, @stg_Typed_table_name NVARCHAR(256)
, @is_valid_column_name NVARCHAR(256)
, @i_debugOnly INT = 0
AS
/**
* <Example>
EXEC [dbo].[usp_load_untyped_to_typed_staging]
@stg_typed_table_name = 'typed'
,@stg_unTyped_table_name = 'untyped'
,@is_valid_column_name = 'IS_VALID'
,@i_debugOnly = 1
* <Comment>.
* As of now it does type checking for only INT and FLOAT bit it can be easily extended
* It does not do NOT NULL Validation as of now but it can be extended
* ----------------
*
* Ver Author Date Comments
* --- ------------------- ----------- ------------------------------
* 1 Ashwani Roy 09-04-2010 Load From Untyped Staging to Typed Staging with type checking and error flagging
*/
SET NOCOUNT ON;
DECLARE @sql_script NVARCHAR(MAX)
DECLARE
-- Define user variables.
@ERR_GENERAL INT,
@ERR_OBJ_NULL INT,
@ErrCode INT = 0,
@ErrMsg NVARCHAR(4000),
@StepNbr SMALLINT = 0,
@SQL VARCHAR(MAX);
SET @ERR_GENERAL =99
SET @ERR_OBJ_NULL =0
DECLARE @numeridTypes TABLE (datatype NVARCHAR(255))
INSERT INTO @numeridTypes
SELECT 'INT'
UNION ALL
SELECT 'FLOAT'
BEGIN
/**
* STEP 1
* Set variables.
*/
SET @StepNbr += 1;
SET @ErrMsg = N'ERROR - Stored procedure [' + OBJECT_NAME(@@PROCID) + N'] ';
/**
* STEP 2
* CREATE DYNAMIC SQL FOR INSERT
* MY AUTOMATIC TYPE CHECKING PATTERN FOR SQL
*/
SELECT
COLUMN_NAME
,DATA_TYPE =
CASE WHEN DATA_TYPE = 'VARCHAR' THEN DATA_TYPE+'(' +CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) +')'
ELSE DATA_TYPE
END
,IS_NULLABLE
INTO #temp_columns
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) WHERE TABLE_NAME =@stg_typed_table_name
IF(@@ROWCOUNT=0)
BEGIN
SET @ErrCode = @ERR_OBJ_NULL
SET @ErrMsg = 'TYPED TABLE :'+'['+@stg_typed_table_name+']'+N'DOES NOT EXISTS'
GOTO FAIL;
END
--Flag the records that Fail Data type Validation as INVALID--
SET @sql_script = N''
SET @sql_script= @sql_script+ 'UPDATE'+'['+ @stg_unTyped_table_name+']'+CHAR(13)
SET @sql_script= @sql_script+ 'SET'+CHAR(9)+@is_valid_column_name+'=0'+CHAR(13)
SET @sql_script= @sql_script+ 'WHERE '+STUFF((SELECT 'OR '+ '1 = CASE WHEN ISNULL(LEN('+COLUMN_NAME+'),0) != 0
AND ISNUMERIC('+COLUMN_NAME+')= 0 THEN 1 WHEN ISNULL(LEN('+COLUMN_NAME+'),0) = 0 THEN 1 ELSE 0 END '
FROM #temp_columns
WHERE DATA_TYPE IN(SELECT datatype FROM @numeridTypes) FOR XML PATH('')),1, 3, '')
BEGIN TRY
IF @i_debugOnly = 1
BEGIN
PRINT @sql_script
END
ELSE
BEGIN
EXEC SP_EXECUTESQL @sql_script
END
END TRY
BEGIN CATCH
IF(@ErrCode NOT IN (0,1))
SET @ErrCode = @ERR_GENERAL;
SET @ErrMsg = @ErrMsg + N'raised error (' + CAST(ERROR_NUMBER() AS NVARCHAR) + N') in step number ('
+ CAST(@StepNbr AS NVARCHAR) + N') at line number ('
+ CAST(ERROR_LINE() AS NVARCHAR) + N') with message: ' + ERROR_MESSAGE() ;
GOTO FAIL;
END CATCH;
--INSERT THE GOOD ONES IN typed--
--If the Untyped is added with a IS_VALID column then the below SQL can be optimized to JUST a simple SELECT WHERE IS_VALID = 1--
SET @sql_script = N''
SET @sql_script= @sql_script+ 'INSERT INTO'+'['+ @stg_typed_table_name+']'+CHAR(13)
SET @sql_script= @sql_script+ 'SELECT'+CHAR(13)
SET @sql_script= @sql_script+ STUFF((SELECT ', ' + '['+COLUMN_NAME+']' FROM #temp_columns FOR XML PATH('')),1, 2, '')
+CHAR(13)
SET @sql_script= @sql_script+ 'FROM'+CHAR(9)+'['+@stg_unTyped_table_name+']'+CHAR(13)
SET @sql_script= @sql_script+ 'WHERE '+STUFF((SELECT 'AND '+ '1 = CASE WHEN ISNULL(LEN('+COLUMN_NAME+'),0) != 0
AND ISNUMERIC('+COLUMN_NAME+')= 1 THEN 1 WHEN ISNULL(LEN('+COLUMN_NAME+'),0) = 0 THEN 1 ELSE 0 END '
FROM #temp_columns
WHERE DATA_TYPE IN(SELECT datatype FROM @numeridTypes) FOR XML PATH('')),1, 3, '')
BEGIN TRY
IF @i_debugOnly = 1
BEGIN
PRINT @sql_script
END
ELSE
BEGIN
EXEC SP_EXECUTESQL @sql_script
END
END TRY
BEGIN CATCH
IF(@ErrCode NOT IN (0,1))
SET @ErrCode = @ERR_GENERAL;
SET @ErrMsg = @ErrMsg + N'raised error (' + CAST(ERROR_NUMBER() AS NVARCHAR) + N')
in step number (' + CAST(@StepNbr AS NVARCHAR) + N') at line number ('
+ CAST(ERROR_LINE() AS NVARCHAR) + N') with message: ' + ERROR_MESSAGE() ;
GOTO FAIL;
END CATCH;
GOTO SUCCESS;
FAIL:
RAISERROR(@ErrMsg, 16, 1);
RETURN @ErrCode;
SUCCESS:
-- Commit the transaction.
RETURN 0;
DROP TABLE #temp_columns
END;
Now when you run this SP in DEBUG MODE as show in the example in the SP code itself
It will print out the SQL that it generated. Setting debug_only to 0 will run this. The SQL generated is like
UPDATE[untyped]
SET IS_VALID=0
WHERE 1 = CASE WHEN ISNULL(LEN(ID),0) != 0 AND ISNUMERIC(ID)= 0 THEN 1 WHEN ISNULL(LEN(ID),0) = 0
THEN 1 ELSE 0 END OR 1 = CASE WHEN ISNULL(LEN(SALARY),0) != 0 AND ISNUMERIC(SALARY)= 0 THEN 1
WHEN ISNULL(LEN(SALARY),0) = 0 THEN 1 ELSE 0 END
INSERT INTO[typed]
SELECT
[ID], [TITLE], [NAME], [SALARY]
FROM [untyped]
WHERE 1 = CASE WHEN ISNULL(LEN(ID),0) != 0 AND ISNUMERIC(ID)= 1 THEN 1 WHEN ISNULL(LEN(ID),0) = 0 THEN 1
ELSE 0 END
AND 1 = CASE WHEN ISNULL(LEN(SALARY),0) != 0 AND ISNUMERIC(SALARY)= 1 THEN 1 WHEN ISNULL(LEN(SALARY),0) = 0 THEN 1
ELSE 0 END
You can see how using dynamic SQL and Information schemas a nice Type check is generated and another
UPDATE to set the validation column = 0 is also created. Best Part is table names and Validation Column names and ISNUMRIC validation types all are configurable.
Now I will run the query with Debug flag = 0 and you would see that the output is as expected. Out of 9 ROWS 7 Will be loaded in Typed Tabled and 2 Will be marked as invalid in the untyped table.
There you go. Job Done.
Hope you find it useful and will be able to extend to do more validation , checks and Error Redirect type stuff using one SP making you life a little easier.
