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.
Rick RudolphAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you try Import Data Wizard that's available in SQL Server Management Studio (SSMS)?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rick RudolphAuthor Commented:
Thanks, I had not thought to do that. That holds a lot of promise.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.