Questions tagged [bulk-insert]
The bulk-insert tag has no usage guidance.
151
questions
-3
votes
1answer
30 views
bulk insert CSV file include hebrew chars puts gibrish [on hold]
I need to bulk insert a csv file (utf-8) containing Hebrew characters (the field collation is Hebrew_CI_AS) and all I get is gibberish.
Instread of 'israel' I am receiving '׳™׳©׳¨׳׳'.
My script :
...
1
vote
1answer
45 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
612 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
65 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
37 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
76 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
25 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
30 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
74 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
313 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
90 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
36 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
87 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
21 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
31 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
119 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
279 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
656 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
548 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
232 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
35 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
123 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
254 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
24 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 ...
0
votes
0answers
53 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 ...
3
votes
1answer
118 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 ...
-3
votes
1answer
833 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
1answer
390 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
vote
0answers
414 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://docs.microsoft....
0
votes
1answer
469 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 ...
3
votes
0answers
1k 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 ...
2
votes
0answers
427 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 ...
5
votes
1answer
5k views
BULK INSERT getting “Cannot bulk load because … Access is denied” on a file I have access to
I'm trying to run a BULK INSERT but I'm getting an access denied error. So far I've tried the following:
I can open the file from my machine
I'm logged into the SQL Server (tried v2008 R2 & ...
3
votes
3answers
1k views
How to retrieve a large text from inside a table?
for my own records I have a table in one of my servers where I like to save my activities and scripts
the table definition is:
IF OBJECT_ID('[dbo].[activity]') IS NOT NULL
DROP TABLE [dbo].[...
5
votes
2answers
3k views
SQL Server Bulk Insert properly interprets some Unicode characters but not others?
For some reason the MS SQL Server 2016 bulk insert misinterprets/translates Unicode characters:
C9 (É) into 2B (+)
A1 (¡) into ED (í)
A0 ( ) into E1 (á)
AE (®) into AB («)
CB (Ë) into 2D (-)
D1 (...
0
votes
0answers
96 views
Insert into …Select from - Still running but no longer inserting rows
We are transferring data into a new bigint table as we are running out of INTs in the current version.
To avoid a large file growth of the logs we are using the following statement on batches and ...
3
votes
1answer
732 views
Multiple inserts of batch insert over the wire?
I am generating about 500 records.
What I want to do is INSERT them all at the same time because:
The server is located elsewhere and I would rather send one big payload over.
This seems like less ...
0
votes
1answer
267 views
What is a staging table and how do you protect data loss when transferring the data?
What is a staging table? What is it used for? Are there best practices that should be applied? I'm working in Oracle right now. Are staging tables platform dependent? I'm performing a bulk copy with ...
1
vote
0answers
663 views
Incorrect syntax near 'BULK' for bulk INSERT in Azure Data Studio
I am trying to bulk insert a large CSV file delimited by tabs. I am on a Mac, and executing queries on Azure Data Studio (formerly SQL Operations Studio), since SSMS is not available for Macs. Here is ...
21
votes
4answers
13k views
Why is BULK INSERT Considered Dangerous?
I would like to understand why cyber-security teams in general (more than one organization I've dealt with) is dead set against granting BULK INSERT (e.g. TSQL) rights to applications and database ...
3
votes
1answer
2k views
PostgreSQL: Why is CREATE TABLE AS faster than CREATE … INSERT INTO?
Below are the two different syntaxs for the same thing.
With a COPY TABLE AS SELECT (CTAS).
CREATE TABLE main
AS
SELECT *
FROM other;
As separate statements with CREATE TABLE and INSERT INTO
...
1
vote
1answer
1k views
SQL server bulk insert fail code 5 [SQLSTATE 42000] (Error 4861)
Following error occurs on running sql job.
Cannot bulk load because the file could not be opened. Operating system error code 5(Access is denied.). [SQLSTATE 42000] (Error 4861). The step failed.
...
2
votes
1answer
119 views
Reducing Memory Pressure When Performing Parallel Staging Table Loads
I'm attempting to migrate several very large tables in my data warehouse to new partition scheme (the older partition scheme was based on a RANGE LEFT function, that wasn't regularly split, and the ...
2
votes
1answer
2k views
Postgres COPY, is it possible to load multiple files into the same table?
Is it possible to execute several \copy commands in parallel against the same table? I know something similar exists with pg_restore, but that splits the work up amongst tables.
I'm working with ...
5
votes
2answers
4k views
SSMS - Can not bulk load because the file could not be opened
We have a user who get this error message every time bulk load using network location it throws error below.
"Cannot bulk load because the file could not be opened. Operating system error code 3(...
1
vote
0answers
235 views
Increase Bulk load
We have a SSIS package which do bulk insert in batch. But from couple of days the duration of the job which runs the package has been increased. Its running for 4-5 hours which usually used to get ...
1
vote
2answers
1k views
Performance of a big INSERT…SELECT with multiple JOIN
I'm having trouble with a big INSERT...SELECT in my DB. For context, the targeted table already have around a million entries and my SELECT query return approximately 2 millions. On a production ...
0
votes
1answer
53 views
Transfer of data (lots) from NAS based MariaDB to Postgres at AWS
I have a bunch of remote autonomous data harvesting systems running, each collects approx 28,800 records over an 8 hour run. The remote systems are Rasp Pi based, running Python, logging to MariaDB ...
1
vote
0answers
274 views
bulk-insert import fails, can't handle some forward slashes in data
Table like so:
[Hotelid] [int] NULL,
[CountryCode] [nvarchar](10) NULL,
[CountryName] [nvarchar](100) NULL,
[State] [nvarchar](100) NULL,
[CityName] [nvarchar](100) NULL,
[DisplayName] [nvarchar](100)...
4
votes
2answers
9k views
BCP woes: Unexpected EOF encountered in BCP data-file
I have a table defined as such:
CREATE TABLE [dbo].[IpMetadata](
[StartIp] [bigint] NOT NULL,
[EndIp] [bigint] NOT NULL,
[CountryCode] [char](10) NOT NULL,
[ProxyType] [varchar](50) ...
