Data Import using Schema config xml; dynamic SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Data Import using Schema config xml; dynamic SQL

Author

Topic

tijujohn83
Starting Member

4 Posts

Posted - 2009-08-11 : 02:27:53

Hello guys..
I am new in this forum and am working in sql 2005 for 7-8 months now.

My current activity consisted of working on a requirement where
1. Data import from excel file to production DB.
2. The xls file will pass through 4 validation processes (DataFormat check, Validity check,RequiredFields check and permissions check ) once uploaded.
3. Once all the validation are done, valid records should be imported to production.

We had written an windows service to run on the backgroud. the user will upload the .xls file from the application and the status will be uploaded in a uploadstatus table.

once past the integrity checks, all the data is bulk copied to a temp table which has all the columns as nvarchar type.
after this the stored procedure is called for the further processing.

The whole issue about the thing is that , the import was to be generic and the target table schema was to be taken from a schema config .xml file.

its something like


  <CONFIGURATION>
  <INFORMATION_SCHEMA.TABLE IMPORT_TYPE="Person" WORKSHEET_NAME="WorkSheetName1" HEADING_ROW="1">
  <INFORMATION_SCHEMA.COLUMNS REQUIRED="TRUE" EXCEL_COLUMN_NAME="UploadID" COLUMN_NAME="UploadID" DATA_TYPE="uniqueidentifier" IS_NULLABLE="FALSE" VISIBLE="FALSE" /> 
  <INFORMATION_SCHEMA.COLUMNS REQUIRED="TRUE" EXCEL_COLUMN_NAME="" COLUMN_NAME="ID" DATA_TYPE="uniqueidentifier" IS_NULLABLE="FALSE" VISIBLE="FALSE" /> 
  <INFORMATION_SCHEMA.COLUMNS REQUIRED="FALSE" EXCEL_COLUMN_NAME="PersonID" COLUMN_NAME="PersonID" DATA_TYPE="uniqueidentifier" IS_NULLABLE="TRUE" VISIBLE="FALSE" /> 
  <INFORMATION_SCHEMA.COLUMNS REQUIRED="TRUE" EXCEL_COLUMN_NAME="PersonAge" COLUMN_NAME="PersonAge" DATA_TYPE="decimal" IS_NULLABLE="FALSE" NUMERIC_PRECISION="18" NUMERIC_SCALE="3" VISIBLE="TRUE" />   <
  <INFORMATION_SCHEMA.COLUMNS REQUIRED="FALSE" EXCEL_COLUMN_NAME="PersonName" COLUMN_NAME="PersonName" DATA_TYPE="nvarchar" CHARACTER_MAXIMUM_LENGTH="200" IS_NULLABLE="TRUE" VISIBLE="TRUE" /> 
  <INFORMATION_SCHEMA.COLUMNS REQUIRED="FALSE" EXCEL_COLUMN_NAME="Comments" COLUMN_NAME="Comments" DATA_TYPE="nvarchar" CHARACTER_MAXIMUM_LENGTH="200" IS_NULLABLE="TRUE" VISIBLE="TRUE" /> 
  </INFORMATION_SCHEMA.TABLE>
  </CONFIGURATION>

now this xml will be processed by the sp ,


DECLARE @config xml
DECLARE @idoc int
			EXEC sp_xml_preparedocument @idoc OUTPUT, @config

			INSERT INTO TempImportSchema (ColumnName,DataType,MaxLength,Nullable,NumericPrecision,NumericScale,DateTimePrecision,CondensedType,Visible,Required,ExcelColumnName)
			SELECT ColumnName,DataType,MaxLength,Nullable,NumericPrecision,NumericScale,DateTimePrecision,
				(CASE DataType 
					WHEN 'uniqueidentifier' THEN 'uniqueidentifier'
					WHEN 'decimal' THEN 'decimal(' + CAST(NumericPrecision AS nvarchar) + ',' + CAST(NumericScale AS nvarchar) + ')'
					WHEN 'nvarchar' THEN 'nvarchar(' + CAST(MaxLength AS nvarchar) + ')'
					WHEN 'varchar' THEN 'varchar('+ CAST(MaxLength AS varchar) + ')'
					WHEN 'datetime' THEN 'datetime'
					ELSE DataType
				 END),Visible,Required,ExcelColumnName
			FROM   OPENXML (@idoc, '/CONFIGURATION/INFORMATION_SCHEMA.TABLE/INFORMATION_SCHEMA.COLUMNS',0)
					 WITH (ImportType        nvarchar(20)  '../@IMPORT_TYPE',
						   ColumnName        nvarchar(20)  '@COLUMN_NAME',
						   DataType          nvarchar(20)  '@DATA_TYPE',
						   MaxLength         nvarchar(10)  '@CHARACTER_MAXIMUM_LENGTH',
						   Nullable          nvarchar(5)   '@IS_NULLABLE',
						   NumericPrecision  nvarchar(5)   '@NUMERIC_PRECISION',
						   NumericScale      nvarchar(5)   '@NUMERIC_SCALE',
						   DateTimePrecision nvarchar(5)   '@DATETIME_PRECISION',
						   Visible           nvarchar(20)  '@VISIBLE',
						   Required          nvarchar(20)  '@REQUIRED',
						   ExcelColumnName   nvarchar(20)  '@EXCEL_COLUMN_NAME')
			WHERE ImportType like @UploadType;

so the above code will convert the xml schema into a table.

we have a temp table, say table1 which will contain raw data from excel file. then another table, table2 with similar columns , but the actual data type.
the stored procedure will take all the records from table1 and push it to the table2, performing the type validations.
the further 3 types of validations(validity,completeness and permission) are further performed on the table2.
once all checks are done, valid data from table2 is moved to production table , tableProduction.

the sp is something like



			/* For each operationn, i am trying to create dynameic SQL and am executing it in the main sp.*/
			
			SET @ImportOperationCursor = dbo.[Generate_ImportOperationCursor_DataFormat_SQL](
			  @UploadID 
			, @UploadType
			, @UploadTableName
			, @TempUploadTableName 
			)
			EXEC sp_executesql @ImportOperationCursor     


			--Proceed for those rows only whose CanImport = true;

			SET @ImportOperationCursor = dbo.[Generate_ImportOperationCursor_InvalidRecords_SQL](
			  @UploadID 
			, @UploadType
			, @UploadTableName
			, @TempUploadTableName 
			)	
			
			EXEC sp_executesql @ImportOperationCursor 
			select @ImportOperationCursor as Invalid
			--Proceed for those rows only whose CanImport = true;

			SET @ImportOperationCursor = dbo.[Generate_ImportOperationCursor_DataPermission_SQL](
			  @UploadID 
			, @UploadType
			, @UploadTableName
			, @TempUploadTableName 
			)	

			EXEC sp_executesql @ImportOperationCursor 

			--Proceed for those rows only whose CanImport = true;


			--
			SET @ImportOperationCursor = dbo.[Generate_ImportOperationCursor_IncompleteRecords_SQL](
			  @UploadID 
			, @UploadType
			, @UploadTableName
			, @TempUploadTableName 
			)			
			EXEC sp_executesql @ImportOperationCursor 
					
				--update the status in Upload
			SET @UpdateImportStatus_SQL = N' IF EXISTS (SELECT * FROM ' + @UploadTableName + N' Where CanImport = 0 AND UploadID = @UploadID)
				UPDATE Upload SET StatusID = 4 WHERE UploadID = @UploadID  ELSE UPDATE Upload SET StatusID = 7 WHERE UploadID = @UploadID  '
					
			EXEC sp_executesql @UpdateImportStatus_SQL,N'@UploadID UniqueIdentifier', @UploadID

			/* some code missing for clarity.. if all checks are passed, then...*/
			
						
			BEGIN TRANSACTION 	
			--if status is pass, import into production.
			/* If all the records of the Current Import passes validations , then move the records to the production table as ADD/EDIT */
			IF (@UploadIntoProduction = 1 AND @StatusID = 7)
				EXEC dbo.[ImportIntoProduction]
				  @UploadID 
				, @UploadType
				, @UploadTableName
				, @TempUploadTableName

I have made different scalar-values functions for generatino the SQL which i have to execute on the fly based on the schema.
eg. [Generate_ImportOperationCursor_DataFormat_SQL]


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go






ALTER Function [dbo].[Generate_ImportOperationCursor_DataFormat_SQL]
( 
  @UploadID uniqueidentifier
, @UploadType nvarchar(20)
, @UploadTableName varchar(30)
, @TempUploadTableName varchar(30)
)
Returns nvarchar(max)
AS 
BEGIN
DECLARE @SQL nvarchar(max)
, @ImportOperationCursor_DeclareVariables_SQL nvarchar(max)
, @ImportOperationCursor_SelectPlaceholders_SQL nvarchar(max) 
, @ImportOperationCursor_SelectPlaceholders_WhereClause_SQL nvarchar(max) 
, @ImportOperationCursor_BeforeCellOperationCode_SQL nvarchar(max)
, @ImportOperationCursor_CellOperationCode_SQL nvarchar(max) 
, @ImportOperationCursor_AfterCellOperationCode_SQL nvarchar(max) 
, @ImportOperationCursor_Insert_SQL nvarchar(max) 
, @ImportOperationCursor_BeforeNextLoop_SQL nvarchar(max) 
, @ImportOperationCursor_InvalidDataFormatColumns_SQL nvarchar(max)
, @ColumnName nvarchar(20)
, @DataType nvarchar(20)
, @CondensedType nvarchar(20)
, @MaxLength nvarchar(10)
, @NumericPrecision nvarchar(5)
, @NumericScale nvarchar(5)

--loop through the #TempImportSchema and make dynamic 'DECLARE' and  format validation code.


DECLARE GenerateDynamicValidationSQL CURSOR
FOR SELECT  ColumnName,DataType,CondensedType,MaxLength,NumericPrecision,NumericScale FROM TempImportSchema 
OPEN GenerateDynamicValidationSQL
FETCH NEXT FROM GenerateDynamicValidationSQL INTO @ColumnName, @DataType, @CondensedType,@MaxLength,@NumericPrecision,@NumericScale
WHILE @@FETCH_STATUS = 0 
BEGIN 	
--make dynamic 'DECLARE'.
	SET @ImportOperationCursor_DeclareVariables_SQL = 
	ISNULL(@ImportOperationCursor_DeclareVariables_SQL,'') 
	+ '; DECLARE @' + ISNULL(@ColumnName,'') + ' ' + 'nvarchar(255) '
	+ '; DECLARE @_' + ISNULL(@ColumnName,'') + ' ' + ISNULL(@CondensedType,'')
	
	--SELECT columns comma seperated for cursor
	SET @ImportOperationCursor_SelectPlaceholders_SQL = 
	ISNULL(@ImportOperationCursor_SelectPlaceholders_SQL,'') 
	+ ', @' + ISNULL(@ColumnName,'')	
	
	--Call Method to generate sql for data format validation code.
	SET @ImportOperationCursor_CellOperationCode_SQL = 
	ISNULL(@ImportOperationCursor_CellOperationCode_SQL,'')
    + dbo.[Generate_ImportOperation_DataFormat_CellValidation_SQL](ISNULL(@DataType,''),ISNULL(@MaxLength,''),ISNULL(@ColumnName,''),ISNULL(@CondensedType,''),ISNULL(@NumericScale,''),ISNULL(@NumericPrecision,''),ISNULL(@UploadType,''))

	FETCH NEXT FROM GenerateDynamicValidationSQL INTO @ColumnName, @DataType, @CondensedType,@MaxLength,@NumericPrecision,@NumericScale 
END
CLOSE GenerateDynamicValidationSQL
DEALLOCATE GenerateDynamicValidationSQL 


 -- remove the starting comma 
SET @ImportOperationCursor_DeclareVariables_SQL = Substring(@ImportOperationCursor_DeclareVariables_SQL,2,len(@ImportOperationCursor_DeclareVariables_SQL))  -- remove the extra semicolans and commas in front.
--declare and set additional  flag variables 
SET @ImportOperationCursor_DeclareVariables_SQL = 
ISNULL(@ImportOperationCursor_DeclareVariables_SQL,'')
+ ';DECLARE @DFCanImport bit,
@ImportOperationCursor_InvalidDataFormatColumns_SQL nvarchar(max); 
SET @DFCanImport=1 ;
SET @ImportOperationCursor_InvalidDataFormatColumns_SQL = '''';'

 -- remove the starting comma 
SET @ImportOperationCursor_SelectPlaceholders_SQL = Substring(@ImportOperationCursor_SelectPlaceholders_SQL,2,len(@ImportOperationCursor_SelectPlaceholders_SQL))
-- WHERE clause for records of the current upload request.
SET @ImportOperationCursor_SelectPlaceholders_WhereClause_SQL = ' WHERE Ltrim(Rtrim(UploadID)) LIKE ''' + CAST(@UploadID AS nvarchar(40)) + ''' '

/* To do data format and transfer valid data from [Temp_UploadData] --> [Upload_Data] table*/

SET @ImportOperationCursor_Insert_SQL=' INSERT INTO ' + @UploadTableName + ' ('
SET @ImportOperationCursor_Insert_SQL = ISNULL(@ImportOperationCursor_Insert_SQL,'') + Replace(@ImportOperationCursor_SelectPlaceholders_SQL,'@','') + ', InvalidDataFormatColumns, CanImport) VALUES(' + Replace(@ImportOperationCursor_SelectPlaceholders_SQL,'@','@_') + ',@ImportOperationCursor_InvalidDataFormatColumns_SQL,@DFCanImport) '  -- the insert code into the target table like Upload_Position

--reset the flags for the next record from the temp_upload table.
SET @ImportOperationCursor_BeforeNextLoop_SQL = '
 SET  @ImportOperationCursor_InvalidDataFormatColumns_SQL = '''';
 SET  @DFCanImport = 1; ' 



-- construct the dynamic sql which will contain the cursor which loops on the temp upload table.
SET @SQL = ISNULL(@SQL,'')
+ ISNULL(@ImportOperationCursor_DeclareVariables_SQL,'')
+ N' DECLARE DFCursor CURSOR FOR SELECT '
+ Replace(ISNULL(@ImportOperationCursor_SelectPlaceholders_SQL,''),'@','') 
+ ' FROM ' + @TempUploadTableName
+ ISNULL(@ImportOperationCursor_SelectPlaceholders_WhereClause_SQL,'')
+ ' OPEN DFCursor '
+ ' FETCH NEXT FROM DFCursor INTO '
+ ISNULL(@ImportOperationCursor_SelectPlaceholders_SQL,'')
+ ' WHILE @@FETCH_STATUS = 0  '
+ ' BEGIN '
+ ISNULL(@ImportOperationCursor_BeforeCellOperationCode_SQL,'')
+ ISNULL(@ImportOperationCursor_CellOperationCode_SQL,'')
+ ISNULL(@ImportOperationCursor_AfterCellOperationCode_SQL,'')
+ ISNULL(@ImportOperationCursor_Insert_SQL,'')
+ ' FETCH NEXT FROM DFCursor INTO '
+ ISNULL(@ImportOperationCursor_SelectPlaceholders_SQL,'')
+ ISNULL(@ImportOperationCursor_BeforeNextLoop_SQL,'')
+ ' END '
+ ' CLOSE DFCursor '
+ ' DEALLOCATE DFCursor '



return @SQL

END


















set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER Function [dbo].[Generate_ImportOperation_DataFormat_CellValidation_SQL]
( 
  @DataType nvarchar(20)
, @MaxLength nvarchar(10)
, @ColumnName nvarchar(20)
, @CondensedType nvarchar(20)
, @NumericScale nvarchar(5)
, @NumericPrecision nvarchar(5)
, @UploadType nvarchar(20)
)
Returns nvarchar(max)
AS 
BEGIN
DECLARE @SQL nvarchar(max)

/* To verify if the datatype of the record is compatible with tht of its datatype in the Production table*/
/* To verify if the datalength of the record is within its length specified in the Production table*/
--update the flags of database, decalred in the calling function [Generate_ImportOperationCursor_DataFormat_SQL] based on the cell validation. 

if(@UploadType = 'Position')
	BEGIN
		SET @SQL = N'
				 BEGIN TRY 							
						SET @_' +  @ColumnName + ' = null '	+	
					  '	IF(@' +  @ColumnName + ' IS NOT NULL) '	+
					  '		IF(Ltrim(Rtrim(@' + @ColumnName + ')) <> '''') ' +
					N'	 		BEGIN
									IF(''' + @DataType + ''' LIKE ''varchar'' OR ''' + @DataType + ''' LIKE ''nvarchar'' OR ''' + @DataType + ''' LIKE ''decimal'')						
										IF(dbo.CheckMaxLength(''' + @DataType + ''',''' + @MaxLength + ''',''' + @NumericPrecision + ''',''' + @NumericScale + ''',@' +  @ColumnName + N')<>1)
											RAISERROR(''MaxLength check failed'',1,1);   
									SET @_' +  @ColumnName + ' = CAST(Ltrim(Rtrim(@' +  @ColumnName + ')) AS ' + @CondensedType + '); 			         							
								END
				      
				 END TRY 


				 BEGIN CATCH
					SET @DFCanImport = 0;
					SET @ImportOperationCursor_InvalidDataFormatColumns_SQL = @ImportOperationCursor_InvalidDataFormatColumns_SQL + ''~'' + ''' + @ColumnName + N''';
			     END CATCH '




	END
return @SQL

END

So the basic idea was to create sql containing cursors on the fly, based on the number and type of columns and executing it.
functin [Generate_ImportOperation_DataFormat_CellValidation_SQL] generates sql which will try to convert nvarchar to the target type and if any errors, it will update the correspondig flags.

I had developed this in a short time and had no time for extended reviews. i have a couple of question for the talented members of this forum.
1. how is this approach. did u guys find any major issues?
2. Can we have any other approaches for this kind of requirement.
3. any other valuable comments.

Let me know if u guys have any trobule in code.

Some best things in life can't be bought by money.

tijujohn83
Starting Member

4 Posts

Posted - 2009-08-13 : 09:14:08

no replies...common people.

Some best things in life can't be bought by money.

tijujohn83
Starting Member

4 Posts

Posted - 2009-08-25 : 06:38:08

this contains a lot of dynamic sql. Especially a cursor, creating the code string (the created sql is itself a cursor)

Cursor creating a dynamic cursor.
some of the things i've learnt.

To improve performance, use names like dbo.[table1], instead of [table1]. this improves sql object lookup. especially if the sql is running under a different user account.

Some best things in life can't be bought by money.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources