Questions tagged [bulk-insert]
The bulk-insert tag has no usage guidance.
217
questions
0
votes
1
answer
1k
views
BULK INSERT: Trials and Tribulations
I recently ran into several problems with a BULK INSERT task, so I thought I'd share my experiences. I'm not looking for help, just offering help for others that may experience something similar in ...
- 968
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 ...
- 143
3
votes
2
answers
7k
views
How to improve insert in RDS of AWS?
I have a MySQL db.t2.small in AWS, the server isn't in my country, but I get 70ms. Now I need to insert over 7000 records in a table, but it takes a lot, over 6min. I tried many configurations:
-...
- 31
4
votes
1
answer
5k
views
Fast way to load a large amount of test data
I've trying to populate a table with a load of dummy data so I can about with optimisation etc.
I've got the following:
WHILE @RowCount < 3000000
BEGIN
SELECT @Random = ROUND(@Upper * RAND(),...
- 981
2
votes
1
answer
4k
views
Permissions problem with BULK INSERT
We are getting the following error when trying to use BULK INSERT.
Msg 4861, Level 16, State 1, Line 10 Cannot bulk load because the file
"\filer\share\loadfile.txt" could not be opened. ...
- 24.1k
1
vote
1
answer
195
views
Seeking Suggestion for faster DB Insertion Policy
I have written a server which keeps on receiving concurrent requests from 30K-40K different clients concurrently. Now I need to dump all those individual requests into a database. The database right ...
- 11
1
vote
1
answer
499
views
Automate: importing xls to table (without microsoft excel)
I am importing 5 different reports from payment sites (paypal, worldpay, etc)
It works like a charm using csv!
BULK
INSERT Report_Table
FROM '\\Files_Server\departments\finance\Paypal.csv'
WITH
(...
- 261
2
votes
1
answer
3k
views
Populating a Junction Table
I'm a SQLite and SQL beginner so please bear with me. I'm trying my hand a creating a simple podcast index database. Here's the schema for my EPISODE, CAST_MEMBER, and EPISODE_CAST tables.
CREATE ...
- 23
2
votes
2
answers
4k
views
Import CSV in Postgres with static data in other columns
How can I COPY a CSV file in Postgres along with static data for the columns not in the CSV? In MySQL I can do LOAD DATA INFILE ... and profile the column names along with any values for the columns ...
- 203
0
votes
1
answer
3k
views
PHPMyAdmin very slow inserts
I was trying to insert around 70K records to mysql database through phpmyadmin and was shocked of how slow it is. It took around 5 hours to complete!
Please find below the details of the table/...
- 103
0
votes
1
answer
3k
views
MongoDB insertion in two collections or one to improve insertion speed
I have a collection, in which I have too many records (~300 million).
And the insertion rate is also very high and it takes time to insert documents, which is my main problem.
As I have two types of ...
- 155
-3
votes
1
answer
288
views
How to achieve high performance management of a huge list of distinct strings [closed]
I am trying to achieve high performance management of a huge list of distinct strings in SQL Server (but I'm open to suggestions of alternate platforms that might be more performant). My specific use ...
- 441
1
vote
4
answers
4k
views
How to handle HUGE 16GB SQL file?
I've abc.sql file exported from MySQL Table.
This abc.sql file is 16 GB in size with around 28 million insert statements.
I'm looking for option to store it in SQL Server Database.
Couple of ...
- 125
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
1
answer
861
views
Bulk Insert into view and find missing values
I have 2 tables, Categories, and SubCategories:
CREATE TABLE [dbo].[tbCategories]
(
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [varchar](250) NULL,
)
CREATE TABLE [dbo].[...
4
votes
2
answers
740
views
Is it possible to rebuild a dropped index?
Is it possivle to drop and index, and rebuild it, instead of disable it and rebuild?
- 7,364
1
vote
1
answer
3k
views
Create a stored-procedure to insert multiple rows on a junction table and other tables
I am new to Databases and Design and needs some help on a INSERT issue.
I want to create a procedure that will insert multiple values to different tables, I need to make sure it stays atomic so I need ...
2
votes
1
answer
478
views
TSQL Bulk Insert Syntax Error
I run the following code, received a syntax error.
SQL Server 2012 Express (version 11)
OS: Windows 7 home premium (x64 bit)
The server complains that I have a syntax error near ROWTERMINATOR='\n'...
- 215
1
vote
1
answer
675
views
MongoDB start ID and end ID
I want to insert bulk data into a MongoDB.
Later on I want to find exactly the bulk I inserted.
In MySQL I would have done:
SELECT * FROM data WHERE id >= start_id AND id <= end_id
How can I ...
- 11
0
votes
1
answer
2k
views
sql server 2012 Express bulk-insert fialed due to message Operating system error code 5(Access is denied.)
I used my personal laptop computer (Samsung RV520)
OS: Windows 7 home premium (x64 bit)
SQL Server 2012 Express version 11
I encountered an error when execut the code below.
error message:
Msg ...
- 215
0
votes
3
answers
4k
views
How to get currently inserted rows and sum them within Mysql trigger before insert
I have a mysql trigger that fires BEFORE INSERT.
My php application is inserting multiple rows at once like so
INSERT INTO table1(id,name,price) VALUES(1,'LH32400', 1990),(2,LH32401,2000);
The idea ...
- 1
0
votes
2
answers
3k
views
Insert the same time, it is possible to happen?
Good evening.
I have a doubt about inclusion in the database. A common example is a user account where the user names must be unique.
In the following logic:
User enters data into a form.
Data are ...
0
votes
1
answer
418
views
MsSql Bulk Insert With commit size 1 vs Simple Insert
Using SSIS OLE DB Destination, what is the difference between doing fast load with commit size 1 or inserts without the fast load option.
I understand that using fast load is going to invoke the ...
- 1
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
4
votes
1
answer
1k
views
Why SqlBulkCopy gets slower by table's row count?
I'm using SqlBulkCopy to insert a lot of data (10000/second at peek) into a database. And when the table starts empty, each bulk insertion takes like 300 milliseconds. But after a day when the records ...
- 1,417
2
votes
1
answer
883
views
Database in SIMPLE recovery model full using SSIS bulk insert task
I have a database in SIMPLE recovery model with 10GB free.
The file I am trying to load through SSISs bulk load is 1GB.
The table is empty (truncated prior to each loading), is a heap and has no ...
- 1,621
4
votes
1
answer
4k
views
Mongodb Bulk find and remove is taking long time
I'm just making my hands dirty(handson) with MongoDB. First I did bulk insert of 1 million documents.
// Bulk insert query
var bulk = db.bulktest.initializeUnorderedBulkOp();
for(var i=0;i<1000000;...
- 245
3
votes
1
answer
3k
views
SQL Bulk Insert Impersonation issue
I am running Microsoft SQL Server 2012 SP on a Windows Server 2008 R2 Standard SP1 box. The SQL Server service is running as a simple windows domain user (nothing special, no admin rights, etc.)
I am ...
- 245
0
votes
1
answer
4k
views
Line-breaks in Text column of CSV file
I have come across an awkward situation where I have got CSV files with | as the column delimiter. In these files, I don't have Text column qualifier. (i.e. ").
My problem is when I am importing these ...
- 522
6
votes
1
answer
4k
views
MySQL Write performance bottleneck
We are trying to diagnose a MySQL Bottleneck, specifically for data writes. Basically, we have a single process that picks files from a folder and load into a MySQL table X using LOAD DATA CONCURRENT ...
- 61
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
3
votes
1
answer
2k
views
Slow inserts in oracle 12c inmemory table
We are running some tests to check the in-memory performance on a 12c (12.1.0.2.4 EE) RAC. Servers have 56GB memory and 20core CPUs.
Our plan is to have a few read-performance critical tables in ...
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
1
vote
2
answers
683
views
BCP - Different Schema
I have a transactional Replication (Non-SQL Server Publishers - Oracle) implemented for one SQL Server Database. Let's say Publishers: A, B
Subscriber: C
Note:
I can not modify the Oracle DBs.
The ...
- 11
5
votes
5
answers
14k
views
Bulk Insert - (0 row(s) affected)
I am trying to import a .CSV file into a mssql table using bulk insert (Although I am open to other methods). I have a bulk insert sql statement but it says (0 row(s) affected) when I execute the the ...
- 81
11
votes
1
answer
5k
views
Foreign keys become untrusted after bulk insert
In a SQL 2014 edition server (12.0.2430.0 - no SP1 yet)
with a database in 2012 compatibility mode (working on getting it switched to 2014...) I have a handful of foreign key objects which are ...
- 10.9k
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
10
votes
1
answer
69k
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 ...
- 171
0
votes
1
answer
867
views
Behaviour of Bulk Insert in pgsql or any Database
I'm using PostgreSQL 9.2.4.
I'm working with multiple tables with large number of data.
I have two tables Table_One which has one of the field with varchar(250) and Table_Two with one of the field ...
- 39
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
0
votes
1
answer
389
views
T-SQL bulk insert fails on specific word "DENY" in string-data [closed]
I have CSV files structured as string ; string ; string
First string is quoted with "". The other 2 strings are not.
I'm bulk inserting them into a table using nchar(x) for each of the 3 columns with ...
- 113
0
votes
1
answer
512
views
huge table copy avoid duplicates
I've a table with more than 10 billion records. Many NEW records (in quantum of 40-50 million records)are added regularly in a file as input part of batch process at night. The active table is majorly ...
- 113
2
votes
2
answers
7k
views
MYSQL: How to improve performance of inserting over 1M rows to a table with over 100M indexed rows
I have this mysql table:
CREATE TABLE `codes` (
`code` bigint(11) unsigned NOT NULL,
`allocation` int(11) NOT NULL DEFAULT '0',
`used` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`code`),
...
- 169
1
vote
1
answer
9k
views
DBEAVER: SQL error or missing database (near ",": syntax error)
I'm using DBeaver (because Base only works on OS X) to work with an SQLite database. When I attempt to execute the following query, I get the error message 'SQL error or missing database (near ",": ...
- 249
6
votes
1
answer
6k
views
Is there a way to minimally log insert under Always on?
I have a primary database in FULL recovery mode which is part of Always On group. Is there a way to minimally log insert operation under FULL recovery model?
I have a process that is executed each ...
- 3,771
1
vote
0
answers
372
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
2
answers
940
views
inserting data from one table to another after casting the values
I have a #temp table containing all the data that needs to be inserted into a table named Customer.
The column names of the two tables are exactly the same, but the data types are different. So I need ...
- 133
3
votes
1
answer
4k
views
Bulk insert in multiple tables
I have two tables on which I perform some bulk inserts:
key: key_id (pk), key_name
related_key: related_key_id (pk), key_id (fk)
First INSERT:
values_data = "($$key_1$$), ($$key_2$$)"
INSERT INTO ...
- 673
0
votes
2
answers
2k
views
CSV data export to SQL Database with double quote problems [closed]
I am trying to import data from falt files (.CSV) into SQL table. I am able to import them no problem but the data is coming with double quotes. What is the best way of resolving this problem?
...
- 355