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