Questions tagged [bulk-insert]
The bulk-insert tag has no usage guidance.
217
questions
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
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
16
votes
3
answers
50k
views
Copy from one MySQL table to another MySQL table of same database
I have around 40 million rows in a MySQL table and I want to copy this table to another table in same database. What is the most efficient way of doing this? How much time will it take (approx.)?
- 263
15
votes
4
answers
62k
views
Bulk Insert Through Network
Can someone help me with these?
BULK INSERT DATABESE01.dbo.TABLE01
FROM '\\COMPUTER01\FOLDER01\TextFile.txt'
WITH
(
FIELDTERMINATOR = ' ',
rowterminator = '\n',
tablock
)
The error shows,...
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
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
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
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
11
votes
1
answer
5k
views
Foreign keys become untrusted after bulk insert
In a SQL 2014 edition server (12.0.2430.0 - no SP1 yet)
with a database in 2012 compatibility mode (working on getting it switched to 2014...) I have a handful of foreign key objects which are ...
- 10.9k
10
votes
1
answer
69k
views
Insert into table select * from table vs bulk insert
I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ?
If no, Is there any way exclude index while inserting records. Process is inserting ...
- 171
10
votes
2
answers
27k
views
Unnest multiple arrays into rows
I have been shown a great way to bulkinsert with this example:
WITH p AS (
INSERT INTO parent_table (column_1)
SELECT $1
RETURNING id)
INSERT INTO child_table (parent_table_id, ...
user32234
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 ...
9
votes
1
answer
1k
views
Minimal Logging Conditions in SQL
I have written a script to test out the claims made on this page http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx in the table titled Summarizing Minimal Logging Conditions about ...
user2920708
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
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
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
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
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
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
6
votes
1
answer
6k
views
Is there a way to minimally log insert under Always on?
I have a primary database in FULL recovery mode which is part of Always On group. Is there a way to minimally log insert operation under FULL recovery model?
I have a process that is executed each ...
- 3,780
6
votes
3
answers
6k
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 ...
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
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
6
votes
1
answer
4k
views
MySQL Write performance bottleneck
We are trying to diagnose a MySQL Bottleneck, specifically for data writes. Basically, we have a single process that picks files from a folder and load into a MySQL table X using LOAD DATA CONCURRENT ...
- 61
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
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
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
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
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
5
votes
5
answers
14k
views
Bulk Insert - (0 row(s) affected)
I am trying to import a .CSV file into a mssql table using bulk insert (Although I am open to other methods). I have a bulk insert sql statement but it says (0 row(s) affected) when I execute the the ...
- 81
5
votes
2
answers
3k
views
Why does SQL Server 2008 R2 insert an invalid datetime when using bulk insert?
I want to insert rows with an optional datetime value (per row) using T-SQL BULK INSERT
on a SQL Server 2008 R2.
A table could look like this:
CREATE TABLE [dbo].[tbl_bulk_insert_datetime_issue] (
...
- 84
5
votes
0
answers
2k
views
Insertion into postgres database stops (hangs) after table reaches a certain size without error
We are currently trying to insert a large amount of data, about 27 million records with 200 columns, into a single table in a postgres 9.4 database. (Yes it is probably better to refactor the table ...
- 51
4
votes
5
answers
1k
views
What are the idiomatic approaches to copying a table (and all of its data) from one server to another?
I have a massive table, let's say 500,000 rows. I want to copy it (schema and data) from one server to another. This is not an upsert or any kind of update; It's a one-off straight copy and paste. ...
- 45
4
votes
2
answers
740
views
Is it possible to rebuild a dropped index?
Is it possivle to drop and index, and rebuild it, instead of disable it and rebuild?
- 7,374
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 ...
4
votes
1
answer
5k
views
Fast way to load a large amount of test data
I've trying to populate a table with a load of dummy data so I can about with optimisation etc.
I've got the following:
WHILE @RowCount < 3000000
BEGIN
SELECT @Random = ROUND(@Upper * RAND(),...
- 981
4
votes
1
answer
4k
views
Mongodb Bulk find and remove is taking long time
I'm just making my hands dirty(handson) with MongoDB. First I did bulk insert of 1 million documents.
// Bulk insert query
var bulk = db.bulktest.initializeUnorderedBulkOp();
for(var i=0;i<1000000;...
- 245
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
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,231
4
votes
1
answer
1k
views
Why SqlBulkCopy gets slower by table's row count?
I'm using SqlBulkCopy to insert a lot of data (10000/second at peek) into a database. And when the table starts empty, each bulk insertion takes like 300 milliseconds. But after a day when the records ...
- 1,417
3
votes
1
answer
4k
views
Bulk insert in multiple tables
I have two tables on which I perform some bulk inserts:
key: key_id (pk), key_name
related_key: related_key_id (pk), key_id (fk)
First INSERT:
values_data = "($$key_1$$), ($$key_2$$)"
INSERT INTO ...
- 673
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
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
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
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
2k
views
Slow inserts in oracle 12c inmemory table
We are running some tests to check the in-memory performance on a 12c (12.1.0.2.4 EE) RAC. Servers have 56GB memory and 20core CPUs.
Our plan is to have a few read-performance critical tables in ...
3
votes
1
answer
2k
views
LOAD DATA stuck at Null State MySQL
I'm stuck trying to move one table to another, due to columns now no longer having proper references/meaning to their column titles. In doing this, I've created a new table with the same number of ...
- 133
3
votes
2
answers
7k
views
How to improve insert in RDS of AWS?
I have a MySQL db.t2.small in AWS, the server isn't in my country, but I get 70ms. Now I need to insert over 7000 records in a table, but it takes a lot, over 6min. I tried many configurations:
-...
- 31
3
votes
1
answer
3k
views
SQL Bulk Insert Impersonation issue
I am running Microsoft SQL Server 2012 SP on a Windows Server 2008 R2 Standard SP1 box. The SQL Server service is running as a simple windows domain user (nothing special, no admin rights, etc.)
I am ...
- 245
2
votes
2
answers
4k
views
Import CSV in Postgres with static data in other columns
How can I COPY a CSV file in Postgres along with static data for the columns not in the CSV? In MySQL I can do LOAD DATA INFILE ... and profile the column names along with any values for the columns ...
- 203