Rick Rudolph
asked on
SQL Query to Append a large number of records to a table from a flat file
I have a text file that has about 3,000,000 records in it, it is a flat file with about 45 fields, the file has no formatting, strictly fixed length. So record 1 is all in row 1 etc. We currently import this file using a utility provided by the software vendor, but it is painfully slow. We have what should be an extremely fast SQL server setup on SSD drives, with plenty of RAM and CPU capacity. I wanted to write an SQL query to append these records from within Microsoft SQL Server Management Studio and see if this query would be faster than what the vendor provides. (I have no access to the vendor code, but the base code is 30 years old and was written in Cobol.
I have no serious experience with writing scripts of any kind for SQL, let alone efficient scripts. My experience is strictly limited to MS Access SQL running inside of VBA.
The file name is always the same:
prcfil00.exp............I create this file in MSAcess, with a number of programs, so I am aware of all of the necessary fields that need data.
Only about 10 of the 45 fields require data.
Some are strings fields and some are decimal fields
So I want to loop through the entire file and for each row, add a new record for the existing table.
Below are the fields, if someone can get me started, I can certainly do the grunt work of adding all of the fields needed.
prc_rec_typ is length 1
prc_key_flds is length 35
prc_alt_item_no is length 15
the number type fields show in access as decimal numbers, but I would guess that might be a different type in SQL
prc_rec_typ prc_key_flds prc_alt_item_no prc_alt_1_conv_fac prc_alt_1_prc_cod prc_alt_1_prc_1 prc_alt_1_prc_2 prc_alt_1_prc_3 prc_alt_1_prc_4 prc_alt_1_prc_5 prc_alt_2_conv_fac prc_alt_2_prc_cod prc_alt_2_prc_1 prc_alt_2_prc_2 prc_alt_2_prc_3 prc_alt_2_prc_4 prc_alt_2_prc_5 prc_whs_item_no prc_whs_whs_cod prc_whs_prc_1 prc_whs_prc_2 prc_whs_prc_3 prc_whs_prc_4 prc_whs_prc_5 prc_whs_1_prc_1 prc_whs_1_prc_2 prc_whs_1_prc_3 prc_whs_1_prc_4 prc_whs_1_prc_5 prc_whs_2_prc_1 prc_whs_2_prc_2 prc_whs_2_prc_3 prc_whs_2_prc_4 prc_whs_2_prc_5 spc_prc_item_no spc_prc_whs spc_prc_end_dat spc_prc_start_dat spc_prc_prc spc_prc_alt_1_prc spc_prc_alt_2_prc disc_prod_cat disc_prod_sub_cat disc_whs_cod disc_end_dat disc_start_dat disc_basis disc_pct disc_alt_1_pct disc_alt_2_pct con_prc_cust_no con_prc_item_no con_prc_end_dat con_prc_start_dat con_prc_contrac_no con_prc_or_low_flg con_prc_prc con_prc_alt_1_prc con_prc_alt_2_prc cd_disc_cust_no cd_disc_prod_cat cd_disc_sub_cat cd_disc_end_dat cd_disc_start_dat cd_disc_con_no cd_disc_or_low_flg cd_disc_basis cd_disc_pct cd_disc_alt_1_pct cd_disc_alt_2_pct timestamp A4GLIdentity
Thank you in advance.
I have no serious experience with writing scripts of any kind for SQL, let alone efficient scripts. My experience is strictly limited to MS Access SQL running inside of VBA.
The file name is always the same:
prcfil00.exp............I create this file in MSAcess, with a number of programs, so I am aware of all of the necessary fields that need data.
Only about 10 of the 45 fields require data.
Some are strings fields and some are decimal fields
So I want to loop through the entire file and for each row, add a new record for the existing table.
Below are the fields, if someone can get me started, I can certainly do the grunt work of adding all of the fields needed.
prc_rec_typ is length 1
prc_key_flds is length 35
prc_alt_item_no is length 15
the number type fields show in access as decimal numbers, but I would guess that might be a different type in SQL
prc_rec_typ prc_key_flds prc_alt_item_no prc_alt_1_conv_fac prc_alt_1_prc_cod prc_alt_1_prc_1 prc_alt_1_prc_2 prc_alt_1_prc_3 prc_alt_1_prc_4 prc_alt_1_prc_5 prc_alt_2_conv_fac prc_alt_2_prc_cod prc_alt_2_prc_1 prc_alt_2_prc_2 prc_alt_2_prc_3 prc_alt_2_prc_4 prc_alt_2_prc_5 prc_whs_item_no prc_whs_whs_cod prc_whs_prc_1 prc_whs_prc_2 prc_whs_prc_3 prc_whs_prc_4 prc_whs_prc_5 prc_whs_1_prc_1 prc_whs_1_prc_2 prc_whs_1_prc_3 prc_whs_1_prc_4 prc_whs_1_prc_5 prc_whs_2_prc_1 prc_whs_2_prc_2 prc_whs_2_prc_3 prc_whs_2_prc_4 prc_whs_2_prc_5 spc_prc_item_no spc_prc_whs spc_prc_end_dat spc_prc_start_dat spc_prc_prc spc_prc_alt_1_prc spc_prc_alt_2_prc disc_prod_cat disc_prod_sub_cat disc_whs_cod disc_end_dat disc_start_dat disc_basis disc_pct disc_alt_1_pct disc_alt_2_pct con_prc_cust_no con_prc_item_no con_prc_end_dat con_prc_start_dat con_prc_contrac_no con_prc_or_low_flg con_prc_prc con_prc_alt_1_prc con_prc_alt_2_prc cd_disc_cust_no cd_disc_prod_cat cd_disc_sub_cat cd_disc_end_dat cd_disc_start_dat cd_disc_con_no cd_disc_or_low_flg cd_disc_basis cd_disc_pct cd_disc_alt_1_pct cd_disc_alt_2_pct timestamp A4GLIdentity
Thank you in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER