Questions tagged [bulk-insert]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
0 answers
33 views

Replicating/Inserting 300M MYSQL records from 6 Remote Servers on Local using Unique Identifier

I have 6 remote servers that dial phone numbers daily. I have a total record count of over 300 million combined and now I want to procress on them. The table data looks like this using this query ...
user avatar
1 vote
0 answers
526 views

UPSERT multiple rows, do ON CONFLICT DO UPDATE SET conditional check/update row-wise

I'm trying to UPSERT multiple rows into a Postgres table (ie. INSERT ON CONFLICT DO UPDATE SET multiple rows). But I want the ON CONFLICT DO UPDATE SET conditional check/update do be done row-wise (ie....
user avatar
0 votes
1 answer
56 views

How to update a column with multiple row values for each group?

In my Postgres table, there are about 2 million rows in total and for each ticker, there are 752 rows. I have an algorithm that shows different prediction price values for each date and ticker which ...
user avatar
0 votes
2 answers
113 views

Is updating a table by delete+insert better than merging with a temp table?

I have two tables in Postgres. The first table has 120 columns (stock metrics like returns, sales etc.), and the second table has 5 columns with a one-to-many relationship and on cascade delete ...
user avatar
0 votes
1 answer
61 views

Insert from select without losing progress in the middle by Postgresql? "INSERT INTO ... SELECT ... WHERE ... AND NOT EXISTS (...)"

Is it possible to move data to the new table sourcing other tables without losing progress in the middle? I think of plpgsql that read data by chunks and commit after every N inserts... If the process ...
user avatar
  • 429
0 votes
0 answers
21 views

Replace Postgres table while keeping foreign keys in other tables [duplicate]

I have a project where I'd like to replace all the rows in a table periodically with new data. That data will 99% of the time have the previous rows with updated data and some new ones. There are ...
user avatar
  • 101
0 votes
0 answers
34 views

Can you bulk upload a file from a remote server to a Azure Managed server

I have a .NET app on a remote server that downloads and processes a file which it then saves to disk, this file is then bulk inserted by SQL and further work carried out. This works a treat when ...
user avatar
  • 123
2 votes
1 answer
71 views

Why does SQL Server Trace Flag 715 behave differently from the TABLOCK query hint?

It's my understanding, based on numerous sources (including this one: https://techcommunity.microsoft.com/t5/sql-server/migrating-sap-workloads-to-sql-server-just-got-2-5x-faster/ba-p/384910) that SQL ...
user avatar
  • 135
0 votes
0 answers
96 views

Fastest way to insert in-memory JSON data into Postgres, other than json_populate_record

We ingest a lot of data, and one of the most time-consuming tasks is inserting data that NodeJS has in JSON format into the database. Currently we're running a query like this, with the giant JSON ...
user avatar
  • 101
1 vote
1 answer
112 views

timescaledb: Bulk insert exhausts all memory

Summary I am attempting to insert data into a timescaledb hypertable in bulk. Regardless of what I try, the memory usage grows gradually until the server process is killed due to a lack of memory. I ...
user avatar
  • 113
0 votes
0 answers
50 views

Why MongoDB Shard becomes slow in data insertion over larger amount data inserted?

General We generate about 10GB of records each day. Each record is composed of four columns: id,A,B,C. The column id must be unique but will be never queried. Columns A,B,C must be indexed (or some ...
user avatar
  • 101
0 votes
1 answer
30 views

How to use multiple SELECTs within a single INSERT? [closed]

I'm currently working with PostgresSQL 13.3 and I thought I could have a file with something like this: INSERT INTO points (zone_id, element_id, position_id) VALUES (SELECT zones._id, elements._id,...
user avatar
  • 9
1 vote
1 answer
103 views

Do I need to Vacuum DB after massive insert?

I use Postgres 12.7 I did massive insert using CopyManager. Input: 320GB of tab separated data Sample row is: 2021-03-16T07:14:41.121000 588467 1900000 0 0 0 0 S 0 LITTLE_CHAR -- ...
user avatar
2 votes
1 answer
82 views

Does simple recovery model not log bulk inserts?

I have a db in full recovery model. There is a planned insert of a million rows that needs to be done next week. In order to not increase the log size, I am told there are 2 options. Option 1 is ...
user avatar
  • 2,239
0 votes
1 answer
119 views

Truncate and insert: Heap and build index or directly into Clustered table

In ETL is it better to drop the index before inserting millions of rows and right after create the index again, or to simply insert into the empty table with the indexe in place. I know that I can ...
user avatar
  • 587
0 votes
2 answers
75 views

Delegation: Bulk Insert failed when executed from SSRS dataset but success from SSMS

I've configured delegation for SQL server engine and SSRS and it works fine. SQL Server (2017) engine and SSRS installd on the same machine. Let's call it SqlSrv1. What we should attain to: User opens ...
user avatar
  • 1
1 vote
1 answer
45 views

SQL: Query to insert records into the table if there is not even one record with certain condition

I have a table train_statuses. With the schema as below: CREATE TABLE public.train_statuses ( id uuid NOT NULL, status text NOT NULL, updated_at timestamp without time zone NOT NULL ); ...
user avatar
  • 131
1 vote
2 answers
204 views

BULK INSERT does not fail when file contains commas instead of semicolons when FIRSTROW > 1

I encountered an issue where I received a CSV file that supposed to be delimited by semicolons (;) but was delimited by commas (,). Bulk insert called by sqlcmd did not fail but did not insert either. ...
user avatar
  • 304
1 vote
1 answer
87 views

INSERT with ON CONFLICT does not insert rows as expected

We have two tables: CREATE TABLE listing_master ( id SERIAL PRIMARY KEY, feed_id character varying(20) NOT NULL, feed_listing_id character varying(20) NOT NULL, updated_time timestamp(...
user avatar
0 votes
1 answer
884 views

Insert/update loop ORACLE function

I want to find the best solution for an insert/update oracle function. My task is to collect some date from multiple tables (source tables) and bring them to a single table (destination table). Source ...
user avatar
  • 1
2 votes
1 answer
710 views

Does SQL Server Support Parallel Bulk Inserts From Multiple Connections?

Does SQL Server support parallel bulk inserts into the same database table? Some clarifications for my particular use case: The table has a clustered index composed of two distinct index keys, and ...
user avatar
  • 21
1 vote
3 answers
989 views

BULK INSERT continue on PRIMARY KEY error

I get a CSV file with a several million records every few days. I need to insert them into our database. What I do is quite straightforward: BULK INSERT dbo.activity FROM 'C:\tmp\activity.csv' WITH ( ...
user avatar
  • 163
0 votes
0 answers
219 views

Bottleneck when inserting many rows into different tables

TLDR: I want to bulk insert into many initially empty tables at once, without the performance of each insert being affected too badly. I seem to be hitting a performance bottleneck I can't yet explain....
user avatar
  • 1
1 vote
1 answer
1k views

SQL Server BULK IMPORT Truncation error

I am trying to import a simple CSV file into a table on SQL Server 2017. The CSV file and table match each other in number of columns and data types. This is an extract of the CSV file: UID,...
user avatar
  • 163
2 votes
2 answers
345 views

Mysql dump as small bulk insert

In mysql (8.0), there is a table with over 10 million rows, I want to dump it as bulk insert sql. I wish each insert sql contains maybe 1k or 10k rows, so that could execute the sql file quickly, ...
user avatar
  • 179
0 votes
1 answer
26 views

Statistics not collected on Insert-Select

Based on the information here, I expected the statistics to be automatically collected during an Insert-Select operation on a new table in Oracle. However, I don't see any table-level statistics ...
user avatar
  • 101
0 votes
1 answer
317 views

sql transactional replication - slow renitialization / slow bulk insert

i recently set up sql transnational replication and i have some performance issue when i'm initializing/re-initializing it from snapshot. The biggest table have is about 8gb as snapshot file network ...
user avatar
  • 101
1 vote
1 answer
1k views

Should simultaneous bulk inserts cause deadlocks, with a PK being an identity column, in SQL Server?

When analyzing a deadlock graph, it looks like the victim is a bulk insert, and the blockers are the same bulk insert. Using a message bus, many subscribers can end up doing a bulk insert at roughly ...
user avatar
  • 143
0 votes
2 answers
269 views

how to insert big data in mariadb with execution time less than max_statement_time value

i have some table with this structure. CREATE TABLE `trcdmppdetail` ( `mpp_code` varchar(50) NOT NULL, `company_code` varchar(50) NOT NULL, `mppdetail_date` datetime(3) NOT NULL, `position_id` ...
user avatar
  • 47
1 vote
1 answer
2k views

Bulk Inserts in Postgres

This is with respect to Data Migration activity where the historical data from the client database is migrated to vendor Postgres Database. There will be millions of transactions that need to be ...
user avatar
  • 11
1 vote
1 answer
495 views

How can I check if BULK INSERT is running on a table?

A BULK INSERT command may take a long time to run. I have a program that receives multiple files and pushes them to the DB using the BULK INSERT command to a specific table. After a file is uploaded, ...
user avatar
  • 794
3 votes
2 answers
2k 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 ...
user avatar
0 votes
2 answers
98 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 ? ...
user avatar
1 vote
1 answer
56 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 ...
user avatar
  • 221
1 vote
1 answer
352 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 ...
user avatar
  • 303
1 vote
1 answer
126 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 ...
user avatar
  • 21
0 votes
0 answers
447 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 ...
user avatar
0 votes
1 answer
32 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 ...
user avatar
0 votes
1 answer
94 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. ...
user avatar
0 votes
0 answers
190 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. ...
user avatar
4 votes
1 answer
2k 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 ...
user avatar
0 votes
1 answer
92 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 ...
user avatar
0 votes
0 answers
60 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 ...
user avatar
1 vote
2 answers
4k 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 ...
user avatar
  • 91
0 votes
0 answers
1k 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 = '","' ,...
user avatar
0 votes
2 answers
3k 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 ...
user avatar
  • 378
0 votes
0 answers
40 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 ...
user avatar
2 votes
2 answers
1k 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 (...
user avatar
0 votes
2 answers
2k 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 ...
user avatar
  • 181
1 vote
1 answer
453 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 ...
user avatar

1
2 3 4 5