home

Move import data using field definition tables.
Author Nigel Rivett

This assumes that the rows of data have been imported into a single column table ImportFile.
The field locations within that table are held in table ColDef.
The mapping of those fields to the destination table fields are held in ColMapping.
All that is needed to add a new file to the process is to add the entries to ColDef and ColMapping.

The process splits the data in ImportFile into columns in ImportFileCols.
It is then converted and moved to the relevant fields in InputData.

The process will take the next record to be processed and process all records of that type.
In a real situation there should be a limit on the number of records processed.

You will notice the use of FileType_id and ImportFile_id.
FileType_id would be created when the column definitions are set up and would be set by the file import.
ImportFile_id is a reference to a ImportFile table - the entry also inserted by the file import.
The ImportFile table would also contain FileType_id but I duplicate this on the working tables.


print 'processing file - ColDef.sql ' 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ColDef]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ColDef]
GO

create table ColDef
	(
	FileType_id	int ,
	Colid		int ,
	StartLoc	int ,
	Length		int
	)
go

Create unique index ix_ColDef_01 on ColDef
	(FileType_id, Colid)
go

insert ColDef select 1, 1, 1, 10	-- addr1
insert ColDef select 1, 2, 11, 10	-- addr2
insert ColDef select 1, 3, 21, 6	-- postcode
insert ColDef select 1, 4, 27, 8	-- dob
insert ColDef select 1, 5, 37, 10	-- amount
insert ColDef select 1, 6, 47, 10	-- name

go
 
print 'processing file - ColMapping.sql ' 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ColMapping]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ColMapping]
go

create table ColMapping
	(
	FileType_id	int ,
	DestField	varchar(20) ,
	FieldType	varchar(10) ,
	Colid		int
	)
go

Create unique index ix_ColMapping_01 on ColMapping
	(FileType_id, DestField)
go

Insert ColMapping select 1, 'Name', 'char', 6
Insert ColMapping select 1, 'DateOfBirth', 'date', 4
Insert ColMapping select 1, 'amount1', 'int', 5
Insert ColMapping select 1, 'amount2', 'int', 5
Insert ColMapping select 1, 'Addr1', 'char', 1
Insert ColMapping select 1, 'Addr2', 'char', 2
Insert ColMapping select 1, 'Postcode', 'char', 3

go
 
print 'processing file - ImportFileCols.sql ' 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportFileCols]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ImportFileCols]
GO

Create table ImportFileCols
	(
	ImportFileData_id	int ,
	ImportFile_id		int ,
	FileType_id		int ,
	Col1			varchar(200) null ,
	Col2			varchar(200) null ,
	Col3			varchar(200) null ,
	Col4			varchar(200) null ,
	Col5			varchar(200) null ,
	Col6			varchar(200) null ,
	Col7			varchar(200) null ,
	Col8			varchar(200) null ,
	Col9			varchar(200) null ,
	Col10			varchar(200) null ,
	Col11			varchar(200) null ,
	Col12			varchar(200) null ,
	Col13			varchar(200) null ,
	Col14			varchar(200) null ,
	Col15			varchar(200) null ,
	Col16			varchar(200) null ,
	Col17			varchar(200) null ,
	Col18			varchar(200) null ,
	Col19			varchar(200) null ,
	Col20			varchar(200) null ,
	ImportDate		datetime default getdate()
	)
go

Create unique index ix_ImportFileCols_01 on ImportFileCols
	(ImportFileData_id)
go
 
print 'processing file - ImportFile.sql ' 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportFileData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ImportFileData]
GO

Create table ImportFileData
	(
	id			int identity (1,1) ,
	ImportFile_id		int ,
	FileType_id		int ,
	Processed		int ,
	Data			varchar(7900) ,
	ImportDate		datetime default getdate()
	)
go

Create unique index ix_ImportFileData_01 on ImportFileData
	(id)
go

Create index ix_ImportFileData_02 on ImportFileData
	(Processed)
go

insert ImportFileData (ImportFile_id, FileType_id, Processed, Data) select 1, 1, 0, 'address11 Address21 pcode119781113  1234      myname1   '
insert ImportFileData (ImportFile_id, FileType_id, Processed, Data) select 1, 1, 0, 'address12 Address22 pcode219781113  1234      myname2   '
insert ImportFileData (ImportFile_id, FileType_id, Processed, Data) select 1, 1, 0, 'address13 Address23 pcode319781113  1234      myname3   '
insert ImportFileData (ImportFile_id, FileType_id, Processed, Data) select 1, 1, 0, 'address14 Address24 pcode419781113  1234      myname4   '
insert ImportFileData (ImportFile_id, FileType_id, Processed, Data) select 2, 2, 0, 'address14 Address24 pcode419781113  1234      myname4   '
insert ImportFileData (ImportFile_id, FileType_id, Processed, Data) select 2, 2, 0, 'address14 Address24 pcode419781113  1234      myname4   '
insert ImportFileData (ImportFile_id, FileType_id, Processed, Data) select 2, 2, 0, 'address14 Address24 pcode419781113  1234      myname4   '
insert ImportFileData (ImportFile_id, FileType_id, Processed, Data) select 2, 2, 0, 'address14 Address24 pcode419781113  1234      myname4   '
insert ImportFileData (ImportFile_id, FileType_id, Processed, Data) select 2, 2, 0, 'address14 Address24 pcode419781113  1234      myname4   '
insert ImportFileData (ImportFile_id, FileType_id, Processed, Data) select 3, 1, 0, 'address14 Address24 pcode419781113  1234      myname4   '
insert ImportFileData (ImportFile_id, FileType_id, Processed, Data) select 3, 1, 0, 'address14 Address24 pcode419781113  1234      myname4   '
insert ImportFileData (ImportFile_id, FileType_id, Processed, Data) select 3, 1, 0, 'address14 Address24 pcode419781113  1234      myname4   '

go
 
print 'processing file - InputData.sql ' 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InputData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[InputData]
go

create table InputData
	(
	ImportFileData_id	int ,
	ImportFile_id		int ,
	Name			varchar(20) ,
	DateOfBirth		datetime ,
	amount1			numeric(15,4) ,
	amount2			numeric(15,4) ,
	Addr1			varchar(20) ,
	Addr2			varchar(20) ,
	Postcode		varchar(10)
	)
go

Create unique index ix_InputData_01 on InputData
	(ImportFileData_id)
go
 
print 'processing file - s_SplitRowCols.sql ' 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SplitRowCols]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_SplitRowCols]
go

Create procedure s_SplitRowCols
as

set nocount on

declare	@rowcount	int ,
	@error		int

declare @ImportFileData_id 	int ,
	@FileType_id		int
	
	select	@ImportFileData_id = min(id)
	from	ImportFileData
	where	Processed = 0
	
	select 	@FileType_id = FileType_id
	from	ImportFileData
	where	id = @ImportFileData_id
	
declare	@sqlCols1 	varchar(1000) ,
	@sqlCols2 	varchar(1000) ,
	@id		int ,
	@maxid		int
	
	create table #RecIds
		(
		id	int
		)
	
	insert	#RecIds
	select	id
	from	ImportFileData
	where	FileType_id = @FileType_id
	and	Processed = 0
	
	if not exists (select * from #RecIds )
		goto Success
	
	create table #ColDef
		(
		Colid		int ,
		StartLoc	int ,
		Length		int
		)
	
	insert	#ColDef
	select	Colid		int ,
		StartLoc	int ,
		Length		int
	from	ColDef
	where	FileType_id = @FileType_id
	
	if not exists (select * from #ColDef )
	begin
		raiserror('No col defs for file type %d', 16, -1, @FileType_id)
		return
	end
	
	select 	@id = 0 , @maxid = max(ColId) from #ColDef
	
	select 	@sqlCols1 = '' ,
		@sqlCols2 = ''
	while @id < @maxid
	begin
		select 	@id = min(Colid) from #ColDef where ColId > @id
		
		select	@sqlCols1 = @sqlCols1
				+ 'Col' 
				+ convert(varchar(10),@id)
				+ ',' ,
				
			@sqlCols2  = @sqlCols2
				+ 'substring(Data, ' 
				+ convert(varchar(10),StartLoc) 
				+ ',' 
				+  convert(varchar(10),Length) 
				+ '),'
		from	#ColDef
		where	Colid = @id
		
	end
	
	drop table #ColDef
	
	select @sqlCols1 = 'insert ImportFileCols (ImportFileData_id, ImportFile_id, FileType_id,'
				+ left(@sqlCols1, len(@sqlCols1)-1)
				+ ') ' ,
		@sqlCols2 = 'select '
				+ 'i.id,'
				+ 'i.ImportFile_id,'
				+ convert(varchar(10),@FileType_id) + ','
				+ left(@sqlCols2,len(@sqlCols2) - 1)
				+ ' from ImportFileData i, #RecIds where i.id = #RecIds.id'
	
declare	@sqlMove1	varchar(1000) ,
	@sqlMove2	varchar(1000)
	
	create table #Move
		(
		DestField	varchar(20) ,
		FieldType	varchar(10) ,
		ColName		varchar(10)
		)
	
	insert	#Move
	select	DestField ,
		FieldType ,
		'Col' + convert(varchar(10),Colid)
	from	ColMapping
	where	FileType_id = @FileType_id
	
	if not exists (select * from #Move )
	begin
		raiserror('No mapping defs for file type %d', 16, -1, @FileType_id)
		return
	end
	
declare	@DestField	varchar(20) ,
	@MaxDestField	varchar(20)
	
	select 	@DestField = '' , @MaxDestField = max(DestField) from #Move
	
	select 	@sqlMove1 = '' ,
		@sqlMove2 = ''
	while @DestField < @MaxDestField
	begin
		select 	@DestField = min(DestField) from #Move where DestField > @DestField
		
		select	@sqlMove1 = @sqlMove1
				+ @DestField 
				+ ',' ,
				
			@sqlMove2  = @sqlMove2
				+ 	case FieldType
						when 'int'
							then ' convert(int,' + ColName + ')'
						when 'char'
							then ColName
						when 'date'
							then ' convert(datetime,' + ColName + ')'
						else
							'fail'
					end
				+ ',' 
		from	#Move
		where	DestField = @DestField
	end
	
	drop table #Move
	
	select @sqlMove1 = 'insert InputData (ImportFileData_id, ImportFile_id, '
				+ left(@sqlMove1, len(@sqlMove1)-1)
				+ ') ' ,
		@sqlMove2 = 'select '
				+ 'i.ImportFileData_id,'
				+ 'i.ImportFile_id,'
				+ left(@sqlMove2,len(@sqlMove2) - 1)
				+ ' from ImportFileCols i, #RecIds where i.ImportFileData_id = #RecIds.id'
	
	begin tran
		exec (@sqlCols1 + @sqlCols2)
		select @error = @@error, @rowcount = @@rowcount
		if @error <> 0
		begin
			rollback tran
			raiserror('failed to insert sqlCols', 16, -1)
			return
		end
		
		exec (@sqlMove1 + @sqlMove2)
		select @error = @@error, @rowcount = @@rowcount
		if @error <> 0
		begin
			rollback tran
			raiserror('failed to insert sqlMove', 16, -1)
			return
		end
		
		update	ImportFileData
		set	Processed = 1
		from	#RecIds
		where	ImportFileData.id = #RecIds.id
		select @error = @@error, @rowcount = @@rowcount
		if @error <> 0
		begin
			rollback tran
			raiserror('failed to update ImportFileData', 16, -1)
			return
		end
	commit tran
	
	drop table #RecIds
	
Success:

go

exec s_SplitRowCols
select * from ImportFileCols
select * from InputData
go


home