The best way to fill table with data using npgsql

Question

I'm connecting to PostgreSQL using npgsql. At this time, I'm filling table in database by manually generating thousands of INSERT statements with multiple data records like

INSERT INTO tablename ( field1, field2 ) VALUES (1, "a"), (2, "b"), (3, "c") ...+ 1000 records;

and putting them to NpgsqlCommand.

How this can be done in more efficient way? I've heard about stored procedures and BULK inserts, but a good example is much needed.

Answer 1

PostgreSQL includes a COPY feature for bulk data loading: http://www.postgresql.org/docs/9.4/static/sql-copy.html

Npgsql exposes COPY in its API. However, the next major version of Npgsql, 3.0, is still in beta and we've totally redone COPY support for it. So it wouldn't be a great idea to code for the 2.2 COPY API, only to have to migrate away from it soon. If you're OK with using beta, go with 3.0, else wait a few weeks until it's released.

Npgsql 2.2 COPY API: https://github.com/npgsql/npgsql/wiki/User-Manual (search for "Fast bulk data copy into a table")

Npgsql 3.0 COPY API: http://npgsql.github.io/npgsql/doc/copy.html (temporary URL which will change to http://www.npgsql.org/doc/copy.html)