Questions tagged [bulk-insert]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
2 answers
302 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 ? ...
humberto herrara's user avatar
1 vote
1 answer
969 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 ...
j4nd3r53n's user avatar
  • 221
1 vote
1 answer
1k 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 ...
630's user avatar
  • 305
1 vote
1 answer
396 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 ...
Bruce's user avatar
  • 21
0 votes
0 answers
912 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 ...
Serge Rogatch's user avatar
0 votes
1 answer
51 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 ...
query_question's user avatar
0 votes
1 answer
180 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. ...
SQLserving's user avatar
0 votes
0 answers
417 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. ...
user1664043's user avatar
6 votes
1 answer
6k 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 ...
Aleksey Vitsko's user avatar
0 votes
1 answer
198 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 ...
VolrathTheFallen's user avatar
0 votes
0 answers
73 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 ...
user205632's user avatar
1 vote
2 answers
8k 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 ...
axdna's user avatar
  • 111
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 = '","' ,...
Dennis M.'s user avatar
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 ...
Glasnhost's user avatar
  • 478
0 votes
0 answers
78 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 ...
I'm Root James's user avatar
2 votes
2 answers
2k 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 (...
Profeeder's user avatar
0 votes
2 answers
4k 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 ...
Lou's user avatar
  • 181
1 vote
1 answer
778 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 ...
B.Muthamizhselvi's user avatar
9 votes
1 answer
2k 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 ...
Daniel James Bryars's user avatar
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 ...
Yago Carvalho's user avatar
-1 votes
2 answers
236 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 ...
Wenjun Chang's user avatar
1 vote
1 answer
2k 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 ...
user9516827's user avatar
  • 1,315
1 vote
1 answer
1k 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 ...
Kevin Cianfarini's user avatar
0 votes
0 answers
258 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 ...
Daniel Grosso's user avatar
1 vote
1 answer
752 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 ...
Jędrzej Dudkiewicz's user avatar
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 ...
gertvdijk's user avatar
  • 227
5 votes
2 answers
195 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 ...
Triynko's user avatar
  • 615
0 votes
1 answer
165 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 ...
NOOK's user avatar
  • 23
0 votes
2 answers
2k 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 '...
Patrick's user avatar
  • 678
2 votes
1 answer
62 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 ...
thedandyman's user avatar
0 votes
1 answer
531 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)...
Khuram's user avatar
  • 129
0 votes
1 answer
1k 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 ...
Sandhya Balakrishnan's user avatar
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....
Sathish Kumar S's user avatar
2 votes
2 answers
6k 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 ...
sql_girl's user avatar
  • 345
0 votes
1 answer
320 views

SQL Server 2017 Express edition Bulk load

I am working on the SQL Server 2017. I need to import 20 text file into one table. Every text file has the same data type and column name. I have checked the data and they have in the same order also. ...
Avinash Kumar's user avatar
1 vote
1 answer
3k 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. ...
Eternum's user avatar
  • 115
2 votes
2 answers
362 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 ...
Pரதீப்'s user avatar
2 votes
1 answer
660 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 ...
Victor Lira's user avatar
1 vote
2 answers
311 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 ...
user1664043's user avatar
1 vote
0 answers
736 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 ...
A5300's user avatar
  • 111
1 vote
1 answer
163 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 ...
Hugo's user avatar
  • 11
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 ...
BeginnerDBA's user avatar
  • 2,169
4 votes
2 answers
11k 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),...
T.H.'s user avatar
  • 307
0 votes
4 answers
408 views

Dynamically generate batched data for import

I need to execute a test in which I have to simulate 20 years' historical data in PostgreSQL (and TimescaleDB) DB. My problem is that I have to generate .sql files and ingest them (using psql-client) ...
Benjamin Soulas's user avatar
3 votes
1 answer
270 views

leaf_allocation_count in dm_db_index_operational_stats not changing on insert bulk

I was doing some index maintenance on a SQL Server 2016 SP2 CU2 and I noticed something I couldn't explain. I've a clustered index on a table of about 66 million rows and rows are added throughout ...
Frederik Vanderhaegen's user avatar
-3 votes
1 answer
3k views

Database name 'mydb' ignored, referencing object in tempdb. Bulk Insert

I'm trying to insert data from an xlsx file into my temporal table. I've also tried with a real table, but it throws me the following error: (0 rows affected) I'm pretty sure my error is in the ...
E.Rawrdríguez.Ophanim's user avatar
-3 votes
1 answer
2k views

Bulk Insert csv file error [closed]

I am trying to BULK INSERT a csv file to my SQL Server. Using code I found on this SO question, I ran: BULK INSERT Temp FROM 'c:\csv\student.csv ' WITH ( FIELDTERMINATOR = ',', ...
csveriu's user avatar
2 votes
0 answers
2k 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....
user avatar
0 votes
1 answer
2k views

SQL Bulk import vs SSIS in Simplicity

We are deciding between bulk insert or SSIS. We have a simple spreadsheet with 5 columns, customerid, cost, date, product name, etc. The spreadsheet is generally only around 200 rows every week. All ...
user avatar
2 votes
1 answer
412 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 ...
Anthony Mastrean's user avatar