How to insert a large amount of records to the database in a short time?

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.

StackExchange Database Administrators Q#104105, answer score: 12