Questions tagged [bulk-insert]
The bulk-insert tag has no usage guidance.
217
questions
9
votes
1
answer
23k
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 & ...
- 24.1k
6
votes
3
answers
6k
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].[...
- 15.8k
5
votes
2
answers
10k
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 (...
- 323
3
votes
1
answer
1k
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 ...
- 147
0
votes
1
answer
639
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
0
answers
2k
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 ...
- 111
21
votes
4
answers
19k
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 ...
- 313
14
votes
3
answers
29k
views
How to insert 10000 new rows?
Presumably there is a straight forward and easy solution. I'm wanting to create 10000 new rows - that are numbered sequentially with no data per row (except sequentially numbered id).
I have used:
...
- 141
5
votes
1
answer
6k
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
...
- 411
2
votes
1
answer
3k
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
1
answer
189
views
Reducing Memory Pressure When Performing Parallel Staging Table Loads [duplicate]
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 ...
- 1,933
2
votes
1
answer
6k
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 ...
- 119
6
votes
2
answers
20k
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(...
- 71
1
vote
0
answers
322
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 ...
- 180
2
votes
2
answers
2k
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 ...
- 121
0
votes
1
answer
118
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 ...
- 3
1
vote
0
answers
585
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)...
- 11
4
votes
2
answers
21k
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) ...
- 1,221
1
vote
0
answers
328
views
MySql slave thinks that the query didn't run on the master, no helpful error
We have a master-slave replication that was running perfectly until recently.
Today i've found the error:
Query caused different errors on master and slave. Error on master: message (format)='Size ...
- 11
0
votes
1
answer
2k
views
Sql server bulk insert remove text qualifier
I have a flat file having hundred thousand of records separated by special kind of character, I have used format file for removing that text qualifier. Works fine with comma separated file.
Note: I ...
1
vote
1
answer
5k
views
Bulk insert top 1000 rows from a big flat file
I have a massive flat file having hundreds of millions rows. I only want to insert the top 1000 of them. How can I do this?
1
vote
1
answer
2k
views
BULK INSERT runs fine in window, but fails inside sql job
I have two server (in the same network), Server A, is the web server, with IIS and all, Server B is the second server, the DB (MS SQL Server 2012), both Windows 2012 R2.
In the Server A, files are ...
- 183
1
vote
1
answer
516
views
Bulk insert and fast ordered search large data 3B+ records
Scenario
We want to study behavior of some similar entities that changes based on time.
Currently I'm using MS SQL Server 2016
Table Structure
CREATE TABLE [dbo].[BehaviorHistory](
[EntityId]...
- 111
8
votes
2
answers
5k
views
SQL Server avoid repeating the collation type multiple times in a query
I have a query which selects rows from a source database (DatabaseA), and inserts them into a target database (Database B). The collation type differs between the databases and they cannot be changed. ...
- 203
11
votes
2
answers
4k
views
Tablock hint triggers deadlocks
I was inserting two data sets, using minimal logging, into an empty heap table using via two Execute SQL Tasks running in parallel and with SQL of the following form.
INSERT INTO Table (TABLOCK) ...
- 137
1
vote
1
answer
7k
views
How to implement on duplicate update for SQL Server?
I'm trying insert/update data for table T1(int id,char name, float data), which has unique a index on id and name.
I'm using SQLBulkOperation(SQL_ADD) to add records to the table, and ...
- 143
5
votes
2
answers
896
views
How to keep smaller transaction logs files while rebuilding index of very large table
I need all the help that I can get to get this index rebuild successful. Especially an expert advise on transaction log management.
Background
The target database is a DW database hosting 3 big ...
- 223
1
vote
0
answers
237
views
EF vs SP performance inserting multiple rows
I know of three most common ways of doing so:
Using a relation level as Entity Framework, which as far as I know has the worst performance.
Using a bulk insert, such as INSERT INTO table1 (x,y) ...
- 430
0
votes
1
answer
1k
views
Why does LOAD DATA INFILE not insert any data?
I have this project I am working on, below is the sample of the text file I need to load into my database.
routes.txt:
"route_id","origin","destination","distance&...
- 1
14
votes
3
answers
15k
views
How does one investigate the performance of a BULK INSERT statement?
I am mainly a .NET developer using Entity Framework ORM. However, because I don't want to fail in using the ORM, I am trying to understand what happens within the data layer (database). Basically, ...
- 1,261
12
votes
2
answers
3k
views
Configure unconstrained delegation for BULK INSERT
I have a pair of Microsoft SQL Server 2016 nodes in an Always On Availability Group. I am trying to perform a BULK INSERT (using a SQL Server 2016 Management Studio query) on a file located on a ...
- 221
2
votes
3
answers
3k
views
Bulk-Insert Select performance over individual Inserts
I am using sequelize as ORM for my nodejs application.
The scenario is, I have to insert a Poll and its Options and get their objects.
Approach 1:
1 - Insert Poll:
INSERT INTO `polls` (`id`,`...
- 133
1
vote
1
answer
2k
views
BULK INSERT syntax error [closed]
While inserting bulk data in sqlserver with query
BULK INSERT Table_name
FROM 'FilePath'
WITH
( FIELDTERMINATOR ',',
ROWTERMINATOR '\n')
;
I'm getting error
"Incorrect syntax near '('."
I ...
- 19
7
votes
2
answers
7k
views
Deadlock in INSERT ... ON DUPLICATE KEY UPDATE
We have a table defined like this:
CREATE TABLE ipsum (
id char(40) NOT NULL,
source char(40) NOT NULL,
ip_address varbinary(16) NOT NULL,
port smallint(5) unsigned NOT NULL,
...
- 171
0
votes
1
answer
1k
views
PostgreSQL splitting one table into three and setting up references between them
My title is a little misleading, what I am doing is inserting data from a CSV file where each row has information corresponding to three different tables. You can imagine the schema being something ...
- 115
1
vote
1
answer
4k
views
Exception while inserting records in SQL Server (Error message id : 586)
I am getting an exception when doing a batch insert operation using a prepared statement from Java.
java.sql.BatchUpdateException: The prepared statement handle 5 is not
valid in this context. ...
- 111
2
votes
1
answer
1k
views
using sp_executesql to run bulk insert
I am trying to execute bulk insert statement using sp_executesql.
I received syntax error:
Msg 102, level 15, state 1Incorrect syntax near '\t'.
my code block are based on this link
Please ...
- 215
4
votes
1
answer
1k
views
SQL Server DB table data size query result not increasing if data count increasing
I have a query which will show the Table Name, Number of Rows and Size of the record of a Database tables in SQL Server. when I executed the query I got a result showing
TableName ...
5
votes
2
answers
5k
views
Is it possible to bulk insert data into a table that has columns encrypted with Always Encrypted?
In SSMS we are attempting to bulk insert from a csv file into a table that has a column encrypted using SQL Server 2016's Always Encrypted feature.
This is the command we're using:
INSERT INTO ...
- 679
1
vote
2
answers
1k
views
Copy Huge Table with Transformations into Another Table
I have a large table that was imported from a CSV using the import wizard. I have a query that selects from this table and replaces all of the string "code" columns with integer ids from other ...
- 703
2
votes
1
answer
1k
views
for Insert multiple rows in one single insert MySQL
I have done bulk-insertion in MySQL using multiple row insertions method by using python script. My testing csv file consists of 100,000 lines only (real one consists of millions lines).
so I perform ...
- 123
7
votes
2
answers
6k
views
MySQL - INSERT DELAYED <- deprecated What now?
I've just been looking at the MySQL doc's https://dev.mysql.com/doc/refman/5.7/en/insert-delayed.html
I was intending to use an INSERT DELAYED, but I see that this has been deprecated.
I still need ...
- 233
3
votes
2
answers
8k
views
Not displaying special characters imported from a file
The scenario is a SQL Server instance, a database which is data fed using BULK INSERT operations mainly, and some of the texts inserted contain special characters like ñ because I am working in a ...
- 191
1
vote
0
answers
171
views
MySQL Insert from Select and schema alter Busy Production without downtime
Strategy for migration of Huge MySQL (Percona 5.7) Table (InnoDB) in production.
The aim is to have minimum downtime (offline schema change would take a week or more...)
old has a 100 plus columns
...
- 111
2
votes
1
answer
2k
views
Insert more rows at a time using bcp
User is using the bcp command to load data from a Linux command prompt.
As of now it is loading 1000 rows at a time. There are 9970001000 total rows to send to SQL Server.
If he wants to insert more ...
- 37
3
votes
1
answer
4k
views
Can SQL Server execute two BULK INSERTs in parallel?
I have two heavy queries to run, and both of them take about 1 minute to run. Both queries are using the Bulk Insert command to insert data from text files to tables in the database.
For the two ...
- 703
0
votes
1
answer
3k
views
Field Terminator Problem in Format Files
I have a data file in txt format and I used to load it with Bulk Insert in the following command:
Bulk insert [table1]
FROM 'data.txt'
WITH
(
FIELDTERMINATOR ='|',
ROWTERMINATOR ='...
- 703
0
votes
1
answer
2k
views
Importing Data in Parallel in SQL Server
I have more than 100 files to import into the sql server and most of them are of 500 MB. I want to leverage the the parallel import utility of SQL server and have read a number of webpages, like the ...
- 703
26
votes
1
answer
5k
views
Is it safe to rely on the order of an INSERT's OUTPUT clause?
Given this table:
CREATE TABLE dbo.Target (
TargetId int identity(1, 1) NOT NULL,
Color varchar(20) NOT NULL,
Action varchar(10) NOT NULL, -- of course this should be normalized
Code int ...
- 4,305
1
vote
1
answer
168
views
INSTEAD OF INSERT trigger
Consider this a follow-up to BULK INSERT: Trials and Tribulations
I have the following VIEW defined and would like to insert into the multiple underlying tables when sending a BULK INSERT from a CSV.
...
- 968