Questions tagged [bulk-insert]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
2answers
18 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` ...
-3
votes
1answer
23 views

How to bulk insert with scope_identity in sql server [closed]

Is there any way to get the inserted id and then bulk insert into another table with new id as foreign key. I have Customer Table and Addresses table. Customer ID is the common column in both tables. ...
0
votes
1answer
27 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 ...
1
vote
1answer
47 views

How can I check if BULK INSERT is running on a table?

A BULK INSERT command may take a long time to run. I have a program that receives multiple files and pushes them to the DB using the BULK INSERT command to a specific table. After a file is uploaded, ...
0
votes
2answers
45 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 ...
0
votes
2answers
43 views

How to insert records using select statement?

Hi I am working on one project, here what I am trying is, I want to insert records what I get using select statements into temp table. but it is giving error to me, can any one help me to solve this ? ...
1
vote
1answer
17 views

How to find error rows in bulk insert?

MySQL 5.6: I'm experimenting with copying data from one table to another in different ways. The source table is on a remote server and has about 500,000 rows - I use the federated engine to connect. I ...
0
votes
0answers
19 views

BULK INSERT - why does FIRSTROW skip by FIELDTERMINATOR rather than ROWTERMINATOR?

In reading the answers for this question about the caution against using FIRSTROW as a means of skipping column header rows, it struck me that it seems really inefficient and non-intuitive that a ...
1
vote
1answer
41 views

Bulk Insert Permissions Issues

I'm experiencing the dreaded Msg 4861 error message when attempting to use Bulk Insert on a 2012 SP4 SQL Server. "Cannot bulk load because the file "\PathToFileShare.d1.xxx.com\folder\file.dat" could ...
1
vote
1answer
34 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 ...
0
votes
0answers
26 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 ...
0
votes
1answer
26 views

MS SQL Management Studio - Script of INSERT statements leaves empty chars for shorter than limit NVARCHAR?

I have just observed in MS Management Studio that if we want to get the script for table's inserted values the output includes the unused free data space which is in excess for the e.g. NVARCHAR data ...
0
votes
1answer
24 views

Upload multiple files to SQL Server without access to the Server Box

I am trying to create a process that would bulk upload multiple csv files periodically to individual tables in SQL server. The issue I am having is those files are not located on the same server. ...
0
votes
0answers
32 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. ...
2
votes
1answer
208 views

SQL Server - What is the difference between BULKADMIN and ADMINISTER BULK OPERATIONS

To grant user ability to run Bulk Insert T-SQL command, login/user needs to be granted: BULKADMIN server role - or - ADMINISTER BULK OPERATIONS server-level permission connect on target database ...
0
votes
1answer
40 views

sqlcmd returns Msg 701 when importing +10MB queries

I have a powershell script that exports data from DBF files, saves them as .SQL files and then imports the SQL files into MS SQL 2016 using the sqlcmd command. There's one SQL file per table in my ...
0
votes
0answers
43 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
0answers
6 views

how do I generate a series starting from the last id in the table, generate_series(last id, 10000)

how do I generate a series starting from the last id in the table, generate_series(last id, 10000)? Say the last id is 101 in the table I would like to generate ids 102-10000 in the table. Currently I ...
0
votes
2answers
332 views

Why BULK INSERT insert data in random order?

I have a csv file with 350,000 rows. Would like to insert data to temporary table in same order as is in csv file. I am trying to BULK INSERT using: BULK INSERT ##AllRows FROM @FilePath WITH ...
0
votes
0answers
18 views

Problems Add/Update Big Data on PostgresSQL

I want to add large amounts of data to the table. Before adding, I check whether the data exists in the table or not. I am dealing with the following: Example: Table users id | name | address .....
0
votes
0answers
173 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 = '","' ,...
0
votes
2answers
54 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 ...
0
votes
0answers
44 views

mysqljs batch insert placeholder for mysql-function

using mysqljs/mysql how to correctly use placeholder when doing batch insert where one or more value is using mysql-functions? e.g: UUID(), UNHEX(), NOW(), DEFAULT, FROM_UNIXTIME(), etc example: ...
0
votes
0answers
32 views

Insert multiple values with associations from json

Say we had a schema like this: create table person ( id serial primary key, first_name text ); create table vehicle ( id serial primary key, name text not null, person_id int unique not ...
0
votes
0answers
8 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 ...
2
votes
2answers
507 views

Bulk insert from a complex text file into a table

I want to use bulk insert to load a text file into a table, but my coding is only able to load half of the text file because my text file has two parts: (ID, CompanyName and Date) as one part; and (...
0
votes
2answers
149 views

How can I insert a record into a table that depends on a foreign key linking to a previous table?

Following on from this question. I have a dictionary file written in XML. I've written a program in VB.NET to parse this file and store all the data in Class objects, and now I want to write a ...
1
vote
1answer
144 views

How to bulk insert data in vertical into sql server?

My data in below format. I have to insert into sql server table. agency NO booknbr 06000330 bookdttm 2006-07-19 10:56:00 arrdttm 2006-05-07 05:42:00 aj_id A000009454 casenbr 06006640 ...
8
votes
1answer
777 views

What is the idiomatic solution in SQL Server for reserving a block of ids for use in a bulk insert?

I have a table with an identity column and I want to reserve a block of ids which I can use for bulk inserting, whilst allowing inserts to still happen into that table. Note this is part of a bulk ...
0
votes
1answer
378 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
2answers
56 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 ...
1
vote
1answer
160 views

Inserting few millions of rows: Does indexing happens along with the insert operation

I am inserting 50 million records to an existing table with a primary key , and a non clustered key. My question is whether indexing of those will existing indexes will happen along with the insert ...
1
vote
1answer
26 views

What are the performance differences of inserting a bunch of data with one INSERT versus a bunch of INSERTs in a transaction?

I'm trying to understand the performance differences between something like INSERT INTO Person (id, name) VALUES (1, "Kevin"), (2, "John"), (3, "Jane"), ... and BEGIN TRANSACTION; INSERT INTO ...
0
votes
0answers
39 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 ...
1
vote
0answers
168 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 ...
0
votes
1answer
1k 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 ...
5
votes
2answers
103 views

Best way to configure and maintain a table with many insertions and deletions that's empy on average?

There exists a "batch query" table that has two columns (BatchID, RecordID) which are both of TSQL type 'uniqueidentifier'. The unique clustering key is on (BatchID, RecordID), and there are no ...
0
votes
1answer
48 views

Bulk Import appending the data to the data file

We have a bulk import task running in SQL Server 2016 which imports more than 5 million rows. The task itself is configured at cloud using third party services. The strange thing is that the new data ...
0
votes
2answers
252 views

Bulk insert inserting 0 rows

I am trying to use bulk insert to insert data from a csv file into a sql server table, but it is returning 0 rows. This is the bulk insert statement I am using BULK INSERT dbo.DEA_Availability from '...
1
vote
1answer
27 views

Which part of a DB bulk insert takes the most time?

One of the most common tip with regards to DB writing is to group your inserts and execute them as a single transaction. My question is, which part of the [begin transaction] - insert - [commit ...
-1
votes
1answer
42 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)...
0
votes
1answer
351 views

I gave 3 large Excel files need to be loaded in Single SQL table

I have 3 large Excel files each sized 56896 KB, I need to load all 3 files into same SQL table. what is better way to handle? since My excel has more data, my excel hang up when I open and search any ...
0
votes
0answers
558 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....
2
votes
2answers
1k views

specify delimiter in openrowset bulk insert

I'm trying to insert data from CSV files into tables using a cursor and open rowset bulk insert. this works for me 99% of the times but if one of the fields in the csv files contains a comma( , ) the ...
1
vote
1answer
1k views

Bulk insert statement fails in SSMS with Operating system error code 5 (Access is denied.)

This issue was discussed in several places and it seems that general consensus is that if Windows Authentication is used then SSMS will access source file using Windows Domain user credentials. ...
2
votes
2answers
274 views

How to import Azure blob file into Sql Server 2008

Is there a way to import Blob files into sql server 2008 other than using CLR ? The file size is around 300MB so CLR isn't efficient enough. Is there a possibility to use BULK INSERT or OPENROWSET in ...
2
votes
1answer
76 views

How to modify values on INSERT based on referenced table, even if foreign key doesn't exist yet?

Summary I want to add rows to a (projected) very huge table, but switch 2 values for references in other tables. The catch is that the referenced tables should be updated automatically when new ...
1
vote
2answers
168 views

SQL Server bulk transfer help needed

We've got this very badly designed logging table that we want to add functionality to. The problem is that it's already a scalability nightmare and we want to fix the design before adding to it, but ...
1
vote
0answers
354 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 ...
1
vote
1answer
29 views

Database transaction consistency on PostgreSQL

In a table with three columns (serial id, varchar name, varchar email) and having two different transactions executing concurrently as the ones bellow: Transaction 1: BEGIN; INSERT INTO public.users ...