This error appears when you are using SQL Server Authentication and SQL Server is not allowed to access the bulk load folder. So giving SQL server access to the folder will solve the issue. Here is how to: Go to the folder right click ->properties->Security tab->Edit->Add(on the new window) ->Advanced -> Find Now. Under the … Read more
Here is the binary equivalent of COPY FROM for Python 3: from io import BytesIO from struct import pack import psycopg2 # Two rows of data; “id” is not in the upstream data source # Columns: node, ts, val1, val2 data = [(23253, 342, -15.336734, 2494627.949375), (23256, 348, 43.23524, 2494827.949375)] conn = psycopg2.connect(“dbname=mydb user=postgres”) curs … Read more
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
Normally, each time db.insert() is used, SQLite creates a transaction (and resulting journal file in the filesystem), which slows things down. If you use db.beginTransaction() and db.endTransaction() SQLite creates only a single journal file on the filesystem and then commits all the inserts at the same time, dramatically speeding things up. Here is some pseudo … Read more
Django 1.4 provides a bulk_create() method on the QuerySet object, see: https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.bulk_create https://docs.djangoproject.com/en/dev/releases/1.4/ https://code.djangoproject.com/ticket/7596
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
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
Add an id column to the csv file and leave it blank: id,Name,Address ,name1,addr test 1 ,name2,addr test 2 Remove KEEPIDENTITY keyword from query: BULK INSERT Employee FROM ‘path\tempFile.csv ‘ WITH (FIRSTROW = 2,FIELDTERMINATOR = ‘,’ , ROWTERMINATOR = ‘\n’); The id identity field will be auto-incremented. If you assign values to the id field … Read more
I don’t really have experience with TVP yet, however there is an nice performance comparison chart vs. BULK INSERT in MSDN here. They say that BULK INSERT has higher startup cost, but is faster thereafter. In a remote client scenario they draw the line at around 1000 rows (for “simple” server logic). Judging from their … 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