Best option to choose when importing data from a text file where heavy inserts and updates are involved!
A specific task of importing data from a text file or any data source where heave inserts and updates are involved is quite common scenario in any SQL Server platform. So when you need to perform such actions on regular basis you need what's the best way to import data from text files into SQL Server where a mix of inserts and updates is required?
Worktable or rather we can call a staging table, a best way to take this forward. As per the configuration the bulk-load features in SQL Server only provide fast insert capabilities. If you have control over the process that creates the text files, it might be better to create two files—one with inserts and another with updates only. This way you can bulk insert the new rows using bulk copy and use a worktable approach for updates.
So based upon the usage since SQL70 version BCP & DTS has become every SQL envrionment tool, think same for SSIS (Integration Services) too. All of these tools can handle the mix of inserts and updates more elegantly and more efficiently that any other method, and it provides the same benefits as using Bulk Copy Program (BCP) and worktables.
Always test all the available options in ETL process, before taking the process or scheduled tasks on the production server.