With FastMember, you can do this without ever needing to go via DataTable (which, in my tests, more-than-doubles the performance): using(var bcp = new SqlBulkCopy(connection)) using(var reader = ObjectReader.Create(data, “Id”, “Name”, “Description”)) { bcp.DestinationTableName = “SomeTable”; bcp.WriteToServer(reader); } Note that ObjectReader can also work with non-generic sources, and it is not necessary to specify the … Read more
I’m loading 50,000 records in 15 or so seconds using Array Binding in ODP.NET It works by repeatedly invoking a stored procedure you specify (and in which you can do updates/inserts/deletes), but it passes the multiple parameter values from .NET to the database in bulk. Instead of specifying a single value for each parameter to … Read more
Sounds like you want to do an “upsert”. MongoDB has built-in support for this. Pass an extra parameter to your update() call: {upsert:true}. For example: key = {‘key’:’value’} data = {‘key2′:’value2’, ‘key3′:’value3’}; coll.update(key, data, upsert=True); #In python upsert must be passed as a keyword argument This replaces your if-find-else-update block entirely. It will insert if … Read more
Unfortunately SQL Server interprets the quoted comma as a delimiter. This applies to both BCP and bulk insert . From http://msdn.microsoft.com/en-us/library/ms191485%28v=sql.100%29.aspx If a terminator character occurs within the data, it is interpreted as a terminator, not as data, and the data after that character is interpreted as belonging to the next field or record. Therefore, … Read more
Please do not create a DataTable to load via BulkCopy. That is an ok solution for smaller sets of data, but there is absolutely no reason to load all 10 million rows into memory before calling the database. Your best bet (outside of BCP / BULK INSERT / OPENROWSET(BULK…)) is to stream the contents from … Read more
Old thread but just looked into this, so here goes: if you are using InnoDB on a recent version of MySQL, you can get the list of IDs using LAST_INSERT_ID() and ROW_COUNT(). InnoDB guarantees sequential numbers for AUTO INCREMENT when doing bulk inserts, provided innodb_autoinc_lock_mode is set to 0 (traditional) or 1 (consecutive). Consequently you … Read more
I found that using DAO in a specific manner is roughly 30 times faster than using ADO.NET. I am sharing the code and results in this answer. As background, in the below, the test is to write out 100 000 records of a table with 20 columns. A summary of the technique and times – … Read more
See populate a database in the PostgreSQL manual, depesz’s excellent-as-usual article on the topic, and this SO question. (Note that this answer is about bulk-loading data into an existing DB or to create a new one. If you’re interested DB restore performance with pg_restore or psql execution of pg_dump output, much of this doesn’t apply … Read more
PostgreSQL has a guide on how to best populate a database initially, and they suggest using the COPY command for bulk loading rows. The guide has some other good tips on how to speed up the process, like removing indexes and foreign keys before loading the data (and adding them back afterwards).
As noted in a comment to another answer, the T-SQL BULK INSERT command will only work if the file to be imported is on the same machine as the SQL Server instance or is in an SMB/CIFS network location that the SQL Server instance can read. Thus it may not be applicable in the case … Read more