Using the example in the manual on the new bulkWrite() API, consider the following test collection which contains the following documents: { “_id” : 1, “char” : “Brisbane”, “class” : “monk”, “lvl” : 4 }, { “_id” : 2, “char” : “Eldon”, “class” : “alchemist”, “lvl” : 3 }, { “_id” : 3, “char” : … Read more
It isn’t possible to do a bulk insert for this file, from MSDN: To be usable as a data file for bulk import, a CSV file must comply with the following restrictions: Data fields never contain the field terminator. Either none or all of the values in a data field are enclosed in quotation marks … 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
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
To locate the troublesome row use the errorfile specifier. BULK INSERT myData FROM ‘C:\…\…\myData.csv’ WITH ( FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘\n’, ERRORFILE = ‘C:\…\…\myRubbishData.log’ ); myRubbishData.log will have the offending rows and a companion file myRubbishData.log.txt will give you row numbers and offsets into the file. Companion file example: Row 3 File Offset 152 … Read more
There’s nothing wrong with your stored procedure code – the point is: the BULK INSERT command cannot accept a file name as a variable. This does work: BULK INSERT ZIPCodes FROM ‘e:\5-digit Commercial.csv’ WITH but this never works – within a stored proc or not: DECLARE @filename VARCHAR(255) SET @filename=”e:\5-digit Commercial.csv” BULK INSERT ZIPCodes FROM … Read more
I’ll address your questions in turn. Will the executeBatch method tries to send all the data at once? This can vary with each JDBC driver, but the few I’ve studied will iterate over each batch entry and send the arguments together with the prepared statement handle each time to the database for execution. That is, … Read more
DBCollection.insert accepts a parameter of type DBObject, List<DBObject> or an array of DBObjects for inserting multiple documents at once. You are passing in a string array. You must manually populate documents(DBObjects), insert them to a List<DBObject> or an array of DBObjects and eventually insert them. DBObject document1 = new BasicDBObject(); document1.put(“name”, “Kiran”); document1.put(“age”, 20); DBObject … Read more
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 felt compelled to contribute as I was having the same issue, and I need to read 2 UNIX files from SAP at least a couple of times a day. Therefore, instead of using unix2dos, I needed something with less manual intervention and more automatic via programming. As noted, the Char(10) works within the sql … Read more