Skip to main content
New
Stack Overflow Jobs powered by Indeed: A job site that puts thousands of tech jobs at your fingertips (U.S. only). Search jobs

Questions tagged [bulk-insert]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-1 votes
1 answer
44 views

SQL Bulk Insert Saves extra character

i use this code for Bulk Insert BULK INSERT [Table] FROM 'D:\phones.txt' WITH ( ROWTERMINATOR = '0x0a' ) My data has only one column of 10 characters For example | 1234567890 | | 1234567891 | ...
ioxoi's user avatar
  • 9
0 votes
0 answers
41 views

Catching errors in bulk insert

I am currently adding a new feature to my ASP.NET website, the goal is to upload a CSV file and have it processed by a SQL Server stored procedure. To perform the bulk insert, I am using this command ...
Helmi KAMEL's user avatar
1 vote
1 answer
73 views

mysql bulk insert with constraints

I created a table like this, CREATE TABLE Leaderboard( userId bigint not null, matchId bigint not null, score mediumint not null, country CHAR(10), tournamentId int not null ) ...
umarkaa's user avatar
  • 37
0 votes
1 answer
46 views

Why is Ignore Duplicate Keys not allowed on a Unique Key?

If I understand this answer correctly the difference between an Index with Is Unqiue on and a Unique Key is mainly cosmetic. In the MSSQL interface when I switch to a Unique Key the Ignore Duplicate ...
Dirk Boer's user avatar
  • 365
1 vote
1 answer
39 views

Moving records between tables in CockroachDB is taking too long

I have a requirement where I have to move records from table original_table to table new_table for the last N days. Below is a small sample of my basic script that does this. while True: result = ...
Brunaldo's user avatar
0 votes
0 answers
185 views

Best way to bulk create with foreign key

This might have been asked but I have not found a sufficient satisfactory answer yet. Use case is that users can create a (temporary) network on a map via clicks and then press save to finish creating....
sebastian.do's user avatar
8 votes
5 answers
1k views

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. ...
J. Mini's user avatar
  • 411
0 votes
1 answer
106 views

Is there a stronger guarantee of minimal logging when running INSERT INTO [...] WITH (TABLOCK) on a heap than on a B-tree?

The documentation lists the following requirements for bulk imports with minimal logging The recovery model of the database is set to simple or bulk-logged. The target table is an empty or non-empty ...
J. Mini's user avatar
  • 411
0 votes
1 answer
222 views

PostgreSQL: does the order of rows in a bulk insert control the order of `FOR EACH ROW` triggers?

I have a trigger like BEFORE INSERT ON table_a FOR EACH ROW EXECUTE FUNCTION upsert_into_table_b(). If I execute a bulk insert like INSERT INTO table_a ([columns]) VALUES ([row 1 values]), ([row 2 ...
Nathan Long's user avatar
0 votes
1 answer
217 views

Using OUTPUT INSERTED.id to UPDATE (i.e. not insert new record) existing rows

1.) @MULTIPLE_RESULTS_TABLEVAR has fields (x, y, OrdersTableID) and values: [a,b,Null], [c,d,Null], [e,f,Null] 2.) Goal is to bulk insert @MULTIPLE_RESULTS_TABLEVAR data into an OrdersTable having ...
M S's user avatar
  • 21
1 vote
1 answer
400 views

How to handle deadlocks while multiple batch inserts?

I need suggestions/ideas of handling a issue with deadlocks. I'm having multiple batch inserts commands to same table: insert into ip (id, p_id, m_type, m_id, sha256, path) VALUES ($1, $2, $3, $4, $5, ...
KaliTheGreat's user avatar
1 vote
1 answer
150 views

SQL Server stored proc locking, serialization

Stored proc in SQL Server INSERT INTO table1 value sets A , B Value set A has as one of its parameters: (SELECT TOP 1 cashbalance - 1 FROM table1 WHERE userid = __ ORDER BY createdUTC, ID) Value set B ...
M S's user avatar
  • 21
0 votes
1 answer
213 views

MSSQL - Insert Into Select number from global count table and update the global count for each row then inserted

Not sure I've articulated that well in the title, so here goes... I have three tables Table1 ID NewNames 10 some row data 11 some more row data Table2 ID OldNames 1 my old data 2 and more data ...
Les's user avatar
  • 1
1 vote
2 answers
236 views

Is it safe to use lastval/currval for multiple inserts?

Lets say I have those tables: CREATE TABLE buildings ( id_building serial4 NOT NULL, street text NULL, CONSTRAINT buildings_pkey PRIMARY KEY (id_building) ); CREATE TABLE families ( ...
Sotis's user avatar
  • 306
1 vote
1 answer
716 views

BULK INSERT from DFS shared folder using Kerberos

I am trying to enable my users to BULK INSERT / OPENROWSET() a CSV file that is stored on our DFS/cifs network shares. I have MS SQL Server 2016 Standard Edition running as a domain account and that ...
JoeNahmias's user avatar
0 votes
2 answers
797 views

PostgreSQL, INSERT INTO SELECT DISTINCT generates more rows?

I have a large PostgreSQL table (NOT indexed) with duplicates so I ran the following query to eventually end up with a unique rows table: INSERT INTO newTable(Field1, Field2, Field3) SELECT DISTINCT ...
Anvil's user avatar
  • 11
1 vote
1 answer
371 views

Open Row insert- SQL

When we run a SSIS package to import data from excel , we get an initial error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the ...
Lakshmi R's user avatar
  • 119
1 vote
2 answers
134 views

Optimising small mysql DB for large inserts

So, an AWS micro instance with around 300mb given to mysql and rest for apache with a live site on it. Everything runs fine in regards to the operation of this setup (didnt really test for ddos but ...
sash's user avatar
  • 19
0 votes
0 answers
105 views

Check Existence Before Creating - Select, Insert, on conflict return

I've the following code that creates mapbox vector tiles and inserts them for storage. In the final code each xyz tile will be unique. Before the below code I would like to check the storage table to ...
Tom Shelley's user avatar
0 votes
1 answer
34 views

Multiple Query Insert and Return

I have the following code which creates a Mapbox Vector Tile(MVT). At the moment it creates, stores and returns to the user. I would like however to create multiple in one query however i'm getting ...
Tom Shelley's user avatar
1 vote
1 answer
1k views

How to perform updates on newly created rows within the same transaction?

Fiddle link: https://dbfiddle.uk/EOE627Oa Tables CREATE TABLE accounts ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, login text ...
Biller Builder's user avatar
1 vote
1 answer
4k views

How to insert multiple values and handle conflict at the same time

I have a table where the primary key is a combination of two columns. When inserting multiple values at a time (batching) conflicts might happen since in the list of values passed there might be ...
Fouad's user avatar
  • 113
1 vote
1 answer
343 views

How to perform dummy multi-inserts with generate_series()?

Given the tables like this: https://dbfiddle.uk/Z8hOhnYG CREATE TABLE accounts ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ); CREATE TABLE profiles ( id bigint GENERATED ALWAYS AS ...
Biller Builder's user avatar
0 votes
0 answers
419 views

How to write 10M inserts/minute without COPY, in tables 300M rows or more?

What kind of setup do I need to have to be able to insert 10M records/minute, and maintain/query tables of 300M rows? My data is two varchar(50) fields that are indexed, and one is unique. No other ...
Darko's user avatar
  • 101
1 vote
0 answers
50 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 ...
Aun Zaidi's user avatar
3 votes
0 answers
7k 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....
étale-cohomology's user avatar
0 votes
1 answer
943 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 ...
Kaushik Ganesan's user avatar
0 votes
3 answers
2k 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 ...
Kaushik Ganesan's user avatar
0 votes
1 answer
1k 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 ...
gavenkoa's user avatar
  • 509
0 votes
0 answers
23 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 ...
Martyns's user avatar
  • 101
2 votes
1 answer
147 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 ...
Mike S's user avatar
  • 135
2 votes
1 answer
1k 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 ...
josteinb's user avatar
  • 123
0 votes
1 answer
97 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,...
Tolga's user avatar
  • 9
1 vote
1 answer
488 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 -- ...
Capacytron's user avatar
2 votes
1 answer
230 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 ...
variable's user avatar
  • 3,634
0 votes
1 answer
621 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 ...
xhr489's user avatar
  • 807
0 votes
2 answers
191 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 ...
TJS's user avatar
  • 1
1 vote
1 answer
100 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 ); ...
Surya's user avatar
  • 131
1 vote
2 answers
911 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. ...
owl's user avatar
  • 304
1 vote
1 answer
198 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(...
Alex A.'s user avatar
  • 163
0 votes
1 answer
2k 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 ...
Luc's user avatar
  • 1
2 votes
1 answer
3k 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 ...
ny_coder_dude's user avatar
2 votes
3 answers
4k 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 ( ...
Wilmar's user avatar
  • 173
1 vote
0 answers
547 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....
Ben's user avatar
  • 11
1 vote
1 answer
4k 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,...
Wilmar's user avatar
  • 173
2 votes
2 answers
1k 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, ...
Eric's user avatar
  • 253
0 votes
1 answer
88 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 ...
Darzen's user avatar
  • 101
0 votes
1 answer
695 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 ...
Dorian's user avatar
  • 103
2 votes
1 answer
4k 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 ...
Bob Horn's user avatar
  • 153
0 votes
2 answers
1k 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` ...
febry's user avatar
  • 47

1
2 3 4 5