Questions tagged [bulk-insert]
The bulk-insert tag has no usage guidance.
217
questions
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 ...
- 315
1
vote
1
answer
349
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 ...
- 21
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
1
answer
50
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 ...
- 151
0
votes
1
answer
177
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.
...
- 476
0
votes
0
answers
412
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
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
...
- 5,472
0
votes
1
answer
186
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 ...
- 101
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 ...
1
vote
2
answers
7k
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
...
- 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 = '","'
,...
- 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
72
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
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
(...
- 29
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 ...
- 181
1
vote
1
answer
754
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
...
- 113
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 ...
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 ...
-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 ...
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 ...
- 1,295
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 ...
- 113
0
votes
0
answers
241
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
1
answer
690
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
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
5
votes
2
answers
182
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 ...
- 615
0
votes
1
answer
162
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 ...
- 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 '...
- 678
2
votes
1
answer
60
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 ...
- 21
0
votes
1
answer
512
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
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 ...
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....
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 ...
- 335
0
votes
1
answer
297
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. ...
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.
...
- 115
2
votes
2
answers
361
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 ...
- 1,369
2
votes
1
answer
604
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 ...
- 23
1
vote
2
answers
294
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 ...
- 207
1
vote
0
answers
711
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
1
answer
147
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 ...
- 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 ...
- 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),...
- 307
0
votes
4
answers
396
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) ...
3
votes
1
answer
264
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 ...
- 1,994
-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 ...
-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 = ',',
...
- 1
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
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 ...
user155025
2
votes
1
answer
389
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
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 ...
- 247
2
votes
0
answers
696
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