Hi,

Thank for your advise, I had check on SQL bulk import before,
but it does not met my requirement as all my legacy data
is not consistence in their format.

>>Hi,
>>
>>Anyone know of any solid flexible importing program that can map
>>from text file of huge legacy data into MS SQL Server 2000.
>>
>>Or have anyone done any flexible importing program to import
>>data.
>>
>>I hope that with it's flexiblity, we only had to change
>>the configuration to import the data without having to
>>change/code for each data file.
>>
>>Any advise or comment is appreciated.
>>Thank u
>
>One approach is to use BULK INSERT with a format file e.g.

BULK INSERT main04sql.dbo.pharmacare
>	FROM 'c:\access_dev\mheccu\data\ascii\pharmacare\pharmacare89(abcg).txt'
>	WITH (
>		FORMATFILE = 'c:\access_dev\mheccu\data\ascii\pharmacare\pharmacare.fmt'
>	)

where a format .fmt file looks something like

7.0
>21
>1  SQLCHAR 0  0 ""  0 pharm_pkey
>2  SQLCHAR 0  1 ""  2 plan_type
>3  SQLCHAR 0  0 ""  0 dob15
>4  SQLCHAR 0  6 ""  4 dob_orig
>5  SQLCHAR 0  1 ""  5 gender
>6  SQLCHAR 0  8 ""  6 drug_num
>7  SQLCHAR 0  0 ""  0 serv_date
>8  SQLCHAR 0  8 ""  8 serv_date_orig
>9  SQLCHAR 0  0 ""  0 ingr_cost
>10 SQLCHAR 0  8 "" 10 ingr_cost_orig
>11 SQLCHAR 0  5 "" 11 pharm_code
>12 SQLCHAR 0  5 "" 12 prac_num
>13 SQLCHAR 0  0 ""  0 pro_fee
>14 SQLCHAR 0  6 "" 14 pro_fee_orig
>15 SQLCHAR 0  0 ""  0 total_paid
>16 SQLCHAR 0  9 "" 16 total_paid_orig
>17 SQLCHAR 0  9 "" 17 ther_code
>18 SQLCHAR 0 10 "" 18 seq_num
>19 SQLCHAR 0  9 "\n" 19 patient_id
>20 SQLCHAR 0  0 ""  0 ascii_file_year
>21 SQLCHAR 0  0 ""  0 serv_year_fiscal
>

This uses the Microsoft Text OLEDB driver. Note that source text files MUST end in a ".txt" extension to work with this driver.
>
>I used this method for a project that loaded about 13GB of text data into SQL Server 7.0. It took about 2 hours on an Athlon/1000 with 512MB RAM.