Questions tagged [bulk-insert]
The bulk-insert tag has no usage guidance.
49
questions with no upvoted or accepted answers
5
votes
0
answers
2k
views
Insertion into postgres database stops (hangs) after table reaches a certain size without error
We are currently trying to insert a large amount of data, about 27 million records with 200 columns, into a single table in a postgres 9.4 database. (Yes it is probably better to refactor the table ...
- 51
2
votes
0
answers
7k
views
UPSERT multiple rows, do ON CONFLICT DO UPDATE SET conditional check/update row-wise
I'm trying to UPSERT multiple rows into a Postgres table (ie. INSERT ON CONFLICT DO UPDATE SET multiple rows).
But I want the ON CONFLICT DO UPDATE SET conditional check/update do be done row-wise (ie....
- 133
2
votes
0
answers
1k
views
Create BCP Format File From SSMS
I want to create a BCP format file from within SSMS. Is there anyway to do this? All the sources are asking to use the BCP command line. I would rather utilize in SSMS window.
https://learn....
user155025
2
votes
1
answer
391
views
Bulk load data and provide row-by-row feedback
We want to allow our users to import CSV files containing many thousands of records to "pre-load" a particular table in our system. It's not strictly a one-time operation, it may be ...
- 121
2
votes
0
answers
697
views
Bulk Updates (inserts, update) and check constraints
Every now and then I need to update all the records (of one attribute) in a very large table. My default strategy is to create a new table, fill all the new values, create indexes, and rename the ...
- 868
2
votes
0
answers
124
views
BULK INSERT clent and session requirements
I'm setting up an ETL process using SQL Server 2014 and SSIS. My Bulk Insert Task is utilizing a source file and format file on ShareA targeting a DB on InstanceA. The package is run via SSA job on ...
- 1,933
2
votes
0
answers
501
views
oracle bulk loader processing columns with newline characters
I'm trying to load a file with description columns which may contain newline characters. The file is Unix format utf16, tab separated and description columns are enclosed in ". The problem is bulk ...
- 121
2
votes
0
answers
667
views
SSIS Bulk Insert Task Operating system error code 1311
I have been running SSIS 2008 R2 Packages for a long time and when ever my Packages trough SQL Server Job accesses FTP Server which is on the same domain it uses correct credentials ,e.g. domain\...
- 141
1
vote
2
answers
182
views
Is it safe to use lastval/currval for multiple inserts?
Lets say I have those tables:
CREATE TABLE buildings (
id_building serial4 NOT NULL,
street text NULL,
CONSTRAINT buildings_pkey PRIMARY KEY (id_building)
);
CREATE TABLE families (
...
- 284
1
vote
1
answer
259
views
Open Row insert- SQL
When we run a SSIS package to import data from excel , we get an initial error:
The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered.
If the 64-bit driver is not installed, run the ...
- 19
1
vote
0
answers
49
views
Replicating/Inserting 300M MYSQL records from 6 Remote Servers on Local using Unique Identifier
I have 6 remote servers that dial phone numbers daily. I have a total record count of over 300 million combined and now I want to procress on them. The table data looks like this using this query
...
- 11
1
vote
2
answers
809
views
BULK INSERT does not fail when file contains commas instead of semicolons when FIRSTROW > 1
I encountered an issue where I received a CSV file that supposed to be delimited by semicolons (;) but was delimited by commas (,).
Bulk insert called by sqlcmd did not fail but did not insert either. ...
- 304
1
vote
0
answers
490
views
Bottleneck when inserting many rows into different tables
TLDR: I want to bulk insert into many initially empty tables at once, without the performance of each insert being affected too badly. I seem to be hitting a performance bottleneck I can't yet explain....
- 11
1
vote
1
answer
3k
views
Bulk Inserts in Postgres
This is with respect to Data Migration activity where the historical data from the client database is migrated to vendor Postgres Database. There will be millions of transactions that need to be ...
- 11
1
vote
1
answer
694
views
Parallel insert and delete into staging tables
I work on a small system that receives quite a lot of data from multiple sources (hundreds of them). Depending on few factors I can get anywhere from few rows to a couple of thousands (in fact ...
1
vote
0
answers
715
views
Improve parallel running of bulk copy insert and stored procedures
I have a Java message listener, which upon receiving a message starts bulk inserting thousands of records into a table. After that it calls a few stored procedures sequentially that make some updates ...
- 111
1
vote
0
answers
2k
views
Incorrect syntax near 'BULK' for bulk INSERT in Azure Data Studio
I am trying to bulk insert a large CSV file delimited by tabs. I am on a Mac, and executing queries on Azure Data Studio (formerly SQL Operations Studio), since SSMS is not available for Macs. Here is ...
- 111
1
vote
0
answers
322
views
Increase Bulk load
We have a SSIS package which do bulk insert in batch. But from couple of days the duration of the job which runs the package has been increased. Its running for 4-5 hours which usually used to get ...
- 180
1
vote
0
answers
585
views
bulk-insert import fails, can't handle some forward slashes in data
Table like so:
[Hotelid] [int] NULL,
[CountryCode] [nvarchar](10) NULL,
[CountryName] [nvarchar](100) NULL,
[State] [nvarchar](100) NULL,
[CityName] [nvarchar](100) NULL,
[DisplayName] [nvarchar](100)...
- 11
1
vote
0
answers
332
views
MySql slave thinks that the query didn't run on the master, no helpful error
We have a master-slave replication that was running perfectly until recently.
Today i've found the error:
Query caused different errors on master and slave. Error on master: message (format)='Size ...
- 11
1
vote
0
answers
237
views
EF vs SP performance inserting multiple rows
I know of three most common ways of doing so:
Using a relation level as Entity Framework, which as far as I know has the worst performance.
Using a bulk insert, such as INSERT INTO table1 (x,y) ...
- 430
1
vote
0
answers
171
views
MySQL Insert from Select and schema alter Busy Production without downtime
Strategy for migration of Huge MySQL (Percona 5.7) Table (InnoDB) in production.
The aim is to have minimum downtime (offline schema change would take a week or more...)
old has a 100 plus columns
...
- 111
1
vote
0
answers
173
views
MySQL - Consequences of high rate inserts
I have a cluster of asynchronous workers that will be extracting text from PDFs and inserting them into a single MySQL table with no relations. This data will then be slurped into a Solr server via a ...
- 153
1
vote
0
answers
359
views
efficient way to store immutable numeric data
I'm trying to put a bunch of worldwide climate data into some kind of database structure so I can quickly access a collation of all 10 datasets simultaneously with fast indexing and hopefully ...
- 111
1
vote
0
answers
2k
views
How do I ensure that bulk inserts must fire my DML trigger?
I have a table using a DML trigger to ensure data integrity. Although authors of SSIS packages, BULK INSERT queries, and bcp executions can enable DML trigger execution, which is disabled by default, ...
- 173
1
vote
0
answers
374
views
Parallel insert in db2
I am trying to import from 8 files and insert into a single table in db2. I am running 8 .sql files that will do the task, but only one of them actually seem to be running and inserting into table.
...
1
vote
0
answers
4k
views
Mongodb extremely slow with bulk inserts and showing erratic behavior
I have been massively frustrated by the erratic behaviour of Mongodb's bulk inserts. I'm trying to write 20K documents in a bulk-insert query. Sometimes the inserts take as low as 5 seconds and ...
- 253
0
votes
0
answers
58
views
Best way to bulk create with foreign key
This might have been asked but I have not found a sufficient satisfactory answer yet.
Use case is that users can create a (temporary) network on a map via clicks and then press save to finish creating....
0
votes
1
answer
81
views
Using OUTPUT INSERTED.id to UPDATE (i.e. not insert new record) existing rows
1.) @MULTIPLE_RESULTS_TABLEVAR has fields (x, y, OrdersTableID) and values:
[a,b,Null], [c,d,Null], [e,f,Null]
2.) Goal is to bulk insert @MULTIPLE_RESULTS_TABLEVAR data into an OrdersTable having ...
- 21
0
votes
1
answer
125
views
MSSQL - Insert Into Select number from global count table and update the global count for each row then inserted
Not sure I've articulated that well in the title, so here goes...
I have three tables
Table1
ID
NewNames
10
some row data
11
some more row data
Table2
ID
OldNames
1
my old data
2
and more data
...
- 1
0
votes
0
answers
88
views
Check Existence Before Creating - Select, Insert, on conflict return
I've the following code that creates mapbox vector tiles and inserts them for storage. In the final code each xyz tile will be unique. Before the below code I would like to check the storage table to ...
0
votes
1
answer
3k
views
How to insert multiple values and handle conflict at the same time
I have a table where the primary key is a combination of two columns.
When inserting multiple values at a time (batching) conflicts might happen since in the
list of values passed there might be ...
- 103
0
votes
0
answers
373
views
How to write 10M inserts/minute without COPY, in tables 300M rows or more?
What kind of setup do I need to have to be able to insert 10M records/minute, and maintain/query tables of 300M rows?
My data is two varchar(50) fields that are indexed, and one is unique. No other ...
- 101
0
votes
0
answers
897
views
Bulk INSERT ... ON DUPLICATE KEY UPDATE inserts only a small fraction of rows in MariaDB
I have a table like the following:
CREATE TABLE `demo_upsert` (
`ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
`Url` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
`LastListID` BIGINT(20) NULL ...
- 115
0
votes
0
answers
413
views
Sql server bulk loading optimization
We've got a process that's importing a 7gig / 334 million row data file. The data file is sorted on the first column
The process is built on the "import first, index later" methodology. It uses BCP.
...
- 207
0
votes
0
answers
70
views
LOAD DATA INFILE Performance Degrading
I have very little database experience, but I've inherited a database for a production system that has major problems that I am attempting to fix.
The system receives a continuous stream of data from ...
0
votes
0
answers
2k
views
Import from csv file to SQL table - Double quotes on the very end
I was able to import a .CSV file into my sql table, here is my BULK INSERT script:
BULK INSERT TLS_Sysprocess
FROM 'D:\Dennis\DevAPPServers2.csv'
WITH (
FirstRow=2
,FIELDTERMINATOR = '","'
,...
- 79
0
votes
2
answers
5k
views
It's possible to skip duplicate keys (i.e. MERGE) in a BULK INSERT in MySQL?
I have a INSERT...SELECT statement that I want to run several times from TABLE1 to TABLE2 as the data in TABLE1 will change overtime.
I don't expect the past data from select to change overtime, the ...
- 468
0
votes
0
answers
73
views
MySQL 8.0 appending unknown character to column
I'm inserting a CSV files into MySQL 8.0 using Python 3.7:
bulk_insert_sql = "LOAD DATA LOCAL INFILE filename.csv INTO TABLE table_name FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 ...
- 231
0
votes
2
answers
1k
views
Hints on bulk loading 3.6 billion rows to InnoDB on Aurora MySQL
everyone!
I'm struggling for a week to bulk load 3.6 billion rows to an InnoDB table on Aurora MySQL 5.6.10a. This table has one FK to a "Main" table and has 12 columns. The first 1.4 billion were ...
0
votes
0
answers
242
views
How to debug INSERT statements
I have issues importing "not-so-small" dumps to MariaDB 10.4.6 in CentOS, but the error log and stderr is empty.
I dumped many databases with mysqldump sized around 50MB / 500.000 rows each, and ...
0
votes
1
answer
5k
views
Large bulk insert performance difference PostgreSQL vs MariaDB (InnoDB)
I've got the very same single-table definition in both PostgreSQL 11.4 and MariaDB 10.4.6 (InnoDB) and I'm seeing a significant difference in INSERT performance I cannot explain for PostgreSQL.
My ...
- 227
0
votes
1
answer
517
views
SELECT Every Parent Table Record and INSET Multiple Record in Child Table Against the Parent_Id
Suppose I have two tables name company and screensavers as given below:
**Company:**
id (pk auto inc)
name (varchar)
**ScereenSavers:**
id (pk auto inc)
name (varchar)
path (varchar)
company_id (int)...
- 129
0
votes
0
answers
1k
views
buffer overflow detected in mariadb 10.3.11
We are getting Assertion failure every time memory usage of MySQL spikes up to the overall system's memory.
Example:
InnoDB: Assertion failure in file
/home/buildbot/buildbot/build/mariadb-10.3....
0
votes
0
answers
65
views
Doing bulk load on database and monitoring the growth
I have a small doubt on how this works. We came to know last night one of our sql databases had bulk load operation done with vendor giving that data of approx 300 GB would be bulk loaded.
Before and ...
- 2,169
0
votes
1
answer
1k
views
Why does LOAD DATA INFILE not insert any data?
I have this project I am working on, below is the sample of the text file I need to load into my database.
routes.txt:
"route_id","origin","destination","distance&...
- 1
0
votes
0
answers
5k
views
Are mongodb bulk inserts really increase server perfomance (and faster)?
Bulk inserts always are faster in client side because of saving round-trips, but at the server-side is there any sensible performance improve?
It seems the code for bulk inserts, just loops over ...
- 365
0
votes
1
answer
140
views
MYSQL INSERT takes too long and each next portion takes more than previous
First 1000 takes 8 sec, next 1000 takes 12 sec and each next more and more. I have two databases with 1,5 million rows each. My goal is to fill empty table jf first db with relationship links.
Items, ...
- 1
-1
votes
2
answers
226
views
Slowing down in MySQL insertion with 3 indexes and 1 foreign key
I was working on a MySQL database using Innodb with billions of lines of data.
My table got 16 fields, 3 indexes, 1 foreign key, and 1 primary key. My buffer pool size is 47 GiB.
When I tested ...
