I have to insert a bunch of records(500,000) to the database at once, using the fastest way. I've tried inserting 1,700 records at once but it took twenty minutes!! I am using SQL-SERVER, and C#.
Although the following table won't contribute to the technique for fast inserting, I decide to link it anyway so you get a better understanding about the number of fields I need to insert(duplicate it by around 500K).
There are several options. Here are the main ones that come to mind.
SSIS - SQL Server Integration Services: This is SQL Servers ETL tool and you can move data from pretty much any source to pretty much any destination and it can be quite fast.
BCP - Bulk Copy Program: This is a command line tool that comes with SQL Server. It's very good at moving text files into SQL Server or you can export into a native format from one SQL Server and import using that same format into another SQL Server. BCP is also very fast but a bit more limited than SSIS.
INSERT INTO tablename SELECT fieldlist FROM othertable: This assumes that you are moving from one SQL table to another on the same server. This is the fastest but at 500k rows you are looking at a fairly big transaction or having to break it up like you said.
OPENROWSET: This is a SQL Server function that will let you pull data into a table from various formats. It's also supposed to be fairly quick but I haven't worked with it much.
General note: Your primary key is mbr_id. If this is something that is stored in your source data (as opposed to being an identity column) your insert will go MUCH faster if the source data is in mbr_id order.
Since you are using. NET, you can actually stream the data in without calling an external process. You would use Table-Valued Parameters. See my answer on StackOverflow for details and a link to another variation of this method:
In order to make it faster (especially if this is an initial population of a table) see about removing indexes as well.
