Questions tagged [bulk-insert]
The bulk-insert tag has no usage guidance.
223
questions
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&...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
1
vote
1
answer
469
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 ...
CommunityBot
- 1
0
votes
1
answer
374
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 ...
CommunityBot
- 1
0
votes
2
answers
2k
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 ...
CommunityBot
- 1
0
votes
1
answer
569
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)...
CommunityBot
- 1
1
vote
2
answers
1k
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. ...
CommunityBot
- 1
2
votes
1
answer
431
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 ...
CommunityBot
- 1
1
vote
1
answer
81
views
Creating indexes before bulk-loading data is weirdly much faster than vice versa
EDIT:
An important thing that I missed is the fact that I parallelize COPY operations by N number of cores, by splitting the data file into N equal parts, and issuing N requests through separate ...
- 189
1
vote
1
answer
5k
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 ...
CommunityBot
- 1
1
vote
1
answer
827
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 ...
CommunityBot
- 1
-1
votes
1
answer
59
views
SQL Bulk Insert Saves extra character
i use this code for Bulk Insert
BULK INSERT [Table]
FROM 'D:\phones.txt'
WITH
(
ROWTERMINATOR = '0x0a'
)
My data has only one column of 10 characters
For example
| 1234567890 |
| 1234567891 | ...
- 9
8
votes
5
answers
3k
views
Copying a table (and all of its data) from one server to another?
I have a massive table, let's say 500,000 rows. I want to copy it (schema and data) from one server to another. This is not an upsert or any kind of update; It's a one-off straight copy and paste. ...
- 645
0
votes
0
answers
70
views
Catching errors in bulk insert
I am currently adding a new feature to my ASP.NET website, the goal is to upload a CSV file and have it processed by a SQL Server stored procedure. To perform the bulk insert, I am using this command ...
1
vote
1
answer
86
views
mysql bulk insert with constraints
I created a table like this,
CREATE TABLE Leaderboard(
userId bigint not null,
matchId bigint not null,
score mediumint not null,
country CHAR(10),
tournamentId int not null
)
...
- 37
0
votes
1
answer
138
views
Why is Ignore Duplicate Keys not allowed on a Unique Key?
If I understand this answer correctly the difference between an Index with Is Unqiue on and a Unique Key is mainly cosmetic.
In the MSSQL interface when I switch to a Unique Key the Ignore Duplicate ...
- 11.1k
1
vote
1
answer
82
views
Moving records between tables in CockroachDB is taking too long
I have a requirement where I have to move records from table original_table to table new_table for the last N days.
Below is a small sample of my basic script that does this.
while True:
result = ...
- 134
1
vote
2
answers
6k
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 ...
CommunityBot
- 1
6
votes
3
answers
7k
views
MySQL INSERT INTO SELECT vs BULK INSERTs performance
Currently, I am trying to copy data from TABLE1 to TABLE2.
In terms of insertions performance, would it be the same or faster if I would to do
BULK INSERT manually (i.e BULK insert every 10K records ...
- 316
0
votes
0
answers
253
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
145
views
Is there a stronger guarantee of minimal logging when running INSERT INTO [...] WITH (TABLOCK) on a heap than on a B-tree?
The documentation lists the following requirements for bulk imports with minimal logging
The recovery model of the database is set to simple or bulk-logged.
The target table is an empty or non-empty ...
- 176
0
votes
1
answer
455
views
PostgreSQL: does the order of rows in a bulk insert control the order of `FOR EACH ROW` triggers?
I have a trigger like BEFORE INSERT ON table_a FOR EACH ROW EXECUTE FUNCTION upsert_into_table_b().
If I execute a bulk insert like INSERT INTO table_a ([columns]) VALUES ([row 1 values]), ([row 2 ...
- 54.9k
1
vote
1
answer
674
views
How to handle deadlocks while multiple batch inserts?
I need suggestions/ideas of handling a issue with deadlocks.
I'm having multiple batch inserts commands to same table:
insert into ip (id, p_id, m_type, m_id, sha256, path) VALUES ($1, $2, $3, $4, $5, ...
- 54.9k
1
vote
1
answer
193
views
SQL Server stored proc locking, serialization
Stored proc in SQL Server INSERT INTO table1 value sets A , B
Value set A has as one of its parameters: (SELECT TOP 1 cashbalance - 1 FROM table1 WHERE userid = __ ORDER BY createdUTC, ID)
Value set B ...
- 88.3k
0
votes
1
answer
270
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
...
- 38.6k
1
vote
1
answer
920
views
BULK INSERT from DFS shared folder using Kerberos
I am trying to enable my users to BULK INSERT / OPENROWSET() a CSV file that is stored on our DFS/cifs network shares.
I have MS SQL Server 2016 Standard Edition running as a domain account and that ...
- 1
15
votes
4
answers
63k
views
Bulk Insert Through Network
Can someone help me with these?
BULK INSERT DATABESE01.dbo.TABLE01
FROM '\\COMPUTER01\FOLDER01\TextFile.txt'
WITH
(
FIELDTERMINATOR = ' ',
rowterminator = '\n',
tablock
)
The error shows,...
- 2,120
0
votes
1
answer
1k
views
How to update a column with multiple row values for each group?
In my Postgres table, there are about 2 million rows in total and for each ticker, there are 752 rows. I have an algorithm that shows different prediction price values for each date and ticker which ...
CommunityBot
- 1
10
votes
1
answer
71k
views
Insert into table select * from table vs bulk insert
I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ?
If no, Is there any way exclude index while inserting records. Process is inserting ...
- 2,201
4
votes
2
answers
12k
views
How to set the first row of CSV data as column name in SQL Server?
I am using BULK INSERT to read a csv file in SQL Server. Is there a way to keep the first row as column name while I am reading the file?
If not, after reading the data from the csv (from second row),...
- 88.3k
1
vote
2
answers
279
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 (
...
- 13.9k
0
votes
2
answers
942
views
PostgreSQL, INSERT INTO SELECT DISTINCT generates more rows?
I have a large PostgreSQL table (NOT indexed) with duplicates so I ran the following query to eventually end up with a unique rows table:
INSERT INTO newTable(Field1, Field2, Field3)
SELECT DISTINCT ...
- 11
13
votes
3
answers
32k
views
How to insert 10000 new rows?
Presumably there is a straight forward and easy solution. I'm wanting to create 10000 new rows - that are numbered sequentially with no data per row (except sequentially numbered id).
I have used:
...
1
vote
2
answers
144
views
Optimising small mysql DB for large inserts
So, an AWS micro instance with around 300mb given to mysql and rest for apache with a live site on it. Everything runs fine in regards to the operation of this setup (didnt really test for ddos but ...
- 19
1
vote
1
answer
439
views
MySQL InnoDB Insertion Faster Than MyISAM
I wrote code that inserts about 15 millions rows into tables in MyISAM and InnoDB for comparsion.
MyISAM and InnoDB are set up with no optimization configuration in /etc/mysql/my.cnf.
And tables ...
CommunityBot
- 1
0
votes
0
answers
116
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
35
views
Multiple Query Insert and Return
I have the following code which creates a Mapbox Vector Tile(MVT). At the moment it creates, stores and returns to the user. I would like however to create multiple in one query however i'm getting ...
- 2,412
1
vote
1
answer
2k
views
How to perform updates on newly created rows within the same transaction?
Fiddle link: https://dbfiddle.uk/EOE627Oa
Tables
CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
login text ...
- 276
0
votes
3
answers
2k
views
Is updating a table by delete+insert better than merging with a temp table?
I have two tables in Postgres. The first table has 120 columns (stock metrics like returns, sales etc.), and the second table has 5 columns with a one-to-many relationship and on cascade delete ...
CommunityBot
- 1
1
vote
1
answer
376
views
How to perform dummy multi-inserts with generate_series()?
Given the tables like this: https://dbfiddle.uk/Z8hOhnYG
CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
);
CREATE TABLE profiles (
id bigint GENERATED ALWAYS AS ...
- 3,583
0
votes
0
answers
462
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
2
answers
1k
views
how to insert big data in mariadb with execution time less than max_statement_time value
i have some table with this structure.
CREATE TABLE `trcdmppdetail` (
`mpp_code` varchar(50) NOT NULL,
`company_code` varchar(50) NOT NULL,
`mppdetail_date` datetime(3) NOT NULL,
`position_id` ...
CommunityBot
- 1
8
votes
1
answer
6k
views
Why is Insert-Ignore so expensive in MySQL?
TL;DR:
Inserting a large number of rows with no collisions is MUCH faster than (not) re-inserting the same rows, BOTH using the INSERT IGNORE syntax.
Why is this? I would assume that the index lookup ...
- 26.6k
1
vote
0
answers
50
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
3
votes
0
answers
8k
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....
- 143
1
vote
0
answers
607
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....
- 1
15
votes
3
answers
16k
views
How does one investigate the performance of a BULK INSERT statement?
I am mainly a .NET developer using Entity Framework ORM. However, because I don't want to fail in using the ORM, I am trying to understand what happens within the data layer (database). Basically, ...
- 1,271
0
votes
1
answer
1k
views
Insert from select without losing progress in the middle by Postgresql? "INSERT INTO ... SELECT ... WHERE ... AND NOT EXISTS (...)"
Is it possible to move data to the new table sourcing other tables without losing progress in the middle?
I think of plpgsql that read data by chunks and commit after every N inserts... If the process ...
- 180k
0
votes
0
answers
23
views
Replace Postgres table while keeping foreign keys in other tables [duplicate]
I have a project where I'd like to replace all the rows in a table periodically with new data. That data will 99% of the time have the previous rows with updated data and some new ones.
There are ...
- 101