c# - Compound entries with different dates using SQL -

i have read in historical customs tariff data large text file database, data bit messed up.

a tariff consists of type , measure (the actual rates , effective dates)

a type defined typecode , description. measures contain rates, geographical area applies to, rate , start date , end date.

the problem there multiple entries same tariff different effective dates needs compounded 1 entry.

the text file looks this:

(typecode, area, rate, startdate, enddate, description)
1: 01021000#gen #free #20050101#20061231#pure-bred breeding animals #
2: 01021000#gen #free #20070101#20071231#pure-bred breeding animals #
3: 01021000#gen #free #20080101#99999999#pure-bred breeding animals #
4: 01029000#gen #00000040.000% #20050101#20061231#other #
5: 01029000#gen #00000040.000% #20070101#20071231#other #
6: 01029000#gen #00000030.000% #20080101#20091231#other #
7: 01029000#eu #00000030.000% #20070101#20071231#other #

in example:

• 1, 2 , 3 needs compounded 1 measure first startdate , last enddate (01021000#gen #free #20050101#99999999#pure-bred breeding animals #)
• 4, 5 needs compounded 1 measure first startdate , last enddate (01029000#gen #00000040.000% #20050101#20071231#other #)
• 6 must stay separate because has different rate
• 7 must stay separate because different geographical area

i using c# , sql compact edition. have got working extremely slow... there has more efficient way @ moment takes 40 minutes on intel i3 laptop (66000 entries)

i've written down steps , given code compounding part. need check if dates subsequent well.

steps:
read textfile line line
split line tokens
insert unique typecodes , descriptions type table
insert values measure table following code:

// check see if measure same typecode, area , rate has been inserted string select = string.format("select typecode measure typecode = '{0}' , areacode = '{1}' , rate = '{2}'", tokens[1], tokens[3], tokens[4]);//string.format("select typecode measure typecode = '{0}'", tokens[1]); sqlcedataadapter adapter = new sqlcedataadapter(select, con); datatable table = new datatable(); // use dataadapter fill datatable adapter.fill(table);  // if there no similar records insert 1 if (table.rows.count <= 0) {     string insert = "insert measure values (@typecode, @uom, @areacode, @rate, @startdate, @enddate)";     sqlcecommand com = new sqlcecommand(insert, con);      com.parameters.addwithvalue("@typecode", tokens[1]);     com.parameters.addwithvalue("@uom", tokens[2]);     com.parameters.addwithvalue("@areacode", tokens[3]);     com.parameters.addwithvalue("@rate", tokens[4]);     com.parameters.addwithvalue("@startdate", tokens[5]);     com.parameters.addwithvalue("@enddate", tokens[6]);      com.executenonquery(); } else {     // update current record new enddate     string update = "update measure set enddate = @enddate typecode = @typecode , areacode = @areacode , rate = @rate";     sqlcecommand com = new sqlcecommand(update, con);      com.parameters.addwithvalue("@enddate", tokens[6]);     com.parameters.addwithvalue("@typecode", tokens[1]);     com.parameters.addwithvalue("@areacode", tokens[3]);     com.parameters.addwithvalue("@rate", tokens[4]);      com.executenonquery(); } 

any or suggestions appreciated!

if you're using sql server 2008 ce, can use merge statement go through table once (http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/). may want create index measures on typecode, areacode , rate speed process well.