Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more

Questions tagged [insert]

A core statement in SQL that does what it says on the tin: inserts data into a table.

136 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
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 ...
jkim's user avatar
  • 51
5 votes
1 answer
1k views

Measure Performance for INSERT, UPDATE and DELETE commands on DB2

I have a DB2 database with a table for daily transactions, which is cleared to a history table at the end of the day, every day. I insert rows to this table via java-JDBC INSERT commands and they ...
Roberto Linares's user avatar
3 votes
0 answers
74 views

On the INSERT statement in H2, what does `DIRECT` mean?

I noticed on the grammar documentation for INSERT in the H2 Database Engine, the optional word DIRECT. The documentation says: When using DIRECT, then the results from the query are directly ...
Basil Bourque's user avatar
3 votes
0 answers
2k views

INSERT-SELECT Statement occasionally fails silently and works upon re-run

We have a batch import process that BULK INSERTs raw data into staging tables, does some data mapping, and then pushes it to another database for validation. The procedure where my issue occurs is ...
Haris Khan's user avatar
3 votes
0 answers
2k views

How do I generate a Postgres dump with the column names in my INSERT statement?

I’m trying to export data from my Postgres 9.5 instance on my Mac to my Postgres 9.5 db on an Ubuntu Linux box. I’m using this command to export pg_dump -U username --format plain --section data --...
Dave's user avatar
  • 703
3 votes
0 answers
765 views

Table queries locked during long insert SQL server

Everyday runs a sql job that loads data from bulk files and load them in auxiliary tables, to adapt and then copy to the destination table where applications read (and only read) the data. I am ...
Rodrigo Rivera's user avatar
3 votes
0 answers
502 views

Combined UUID Index + AI PK Insert Performance

I posted this in stackoverflow but realised it probably belongs here instead. Imagine the following table: CREATE TABLE `orders` ( `ORDERID` int(11) unsigned NOT NULL AUTO_INCREMENT, `DATE`...
nickdnk's user avatar
  • 223
3 votes
0 answers
2k views

How to insert values into Bridge Table in star schema?

I am modelling my database with respect to star schema with bridge tables. My master Table looks like below. I need to convert this format to star schema. MasterTable StudentNumber | Subject1 | ...
Nagej's user avatar
  • 31
3 votes
0 answers
2k views

Mysql slow writes

i have a strange problem with mysql. My selects goes fast. My insert/create/delete/drop/update go very slow. I have done some tests on 2 machines. My old machine and my new machine. Old is worst ...
Daniele Ravizza's user avatar
3 votes
0 answers
1k views

MySQL: Improve performance for one row insert into table with unique constraint

I have a table with ~ 40 mil records, with a unique index on the url column, of type varchar(255). Now the insert speed is about 30/s, is this expected? how could I improve it? I can't use bulk ...
Stefan Rogin's user avatar
2 votes
0 answers
48 views

Expensive Insert Operation pause Other Insert Operation

I am continuously inserting value into 3 table having same structure Table1 (col1 bigint primary key,col2 bigint,col3 int,col4 datetime,col5 varchar(400),col6 float, col7 numeric(18,6),col8 ...
Shridhar's user avatar
  • 121
2 votes
1 answer
2k views

mySQL trigger to prevent insert won't work

The problem has been discussed before but the answers won't work in my case. I have a table in my database which contains a numeric column called "value". I would like to prevent an insert if the ...
user125766's user avatar
2 votes
0 answers
1k views

Tuning a Postgres INSERT only database

I am designing a database which will have many inserts and selects, but no updates. Are there any considerations to take (in order to have high query performance) while knowing no tuple updates will ...
Ofiris's user avatar
  • 821
2 votes
0 answers
91 views

work/cache table issues, heavy inserts with heavy reads

we have a work table that stores availability of products. This table is updated with millions of inserts every night and medium amounts of inserts throughout the day. during the updates, we delete ...
ttomsen's user avatar
  • 289
2 votes
0 answers
2k views

Understanding two types of mongodb log entries for bulk inserts

I have an app which does regular (every 2 minutes) bulk inserts to two identical instances of 2.6.4 mongodb on two servers. Both the instances are hosted on 2 Core, 14GB machine and have only one ...
VaidAbhishek's user avatar
2 votes
1 answer
75 views

Insert rows into a table from another table row?

I've got a table Schedule Id unserId Sunday Monday Tuesday Wednesday Thursday Friday Saturday 0 10 0 1 1 1 1 0 0 1 20 1 0 0 0 0 0 0 I need to insert lines from one row in table Schedule into seven ...
XMehdi01's user avatar
  • 302
1 vote
0 answers
152 views

Copy rows and change corresponding values

I have 4 tables Tables are used to store product attributes and its values in different languages I'm trying to copy attribute (copy in same table to new row and increment/change some values). First ...
Webzber's user avatar
  • 11
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
0 answers
218 views

update trigger after insert on same table

So, I'm supposed to make a trigger that applies md5 to a row after an INSERT was made on it. Here is what I came up with: delimiter $$\ create trigger pd_encrip before insert on usuario\ for each row\ ...
Jade Fernandes's user avatar
1 vote
0 answers
157 views

PL SQL code that Inserts data from a different table and gives new id values from the same table that the data is being inserted

What could I change to make this insert work and give a new ID value on each row I am inserting? Right now it's giving all the inserted rows the same ID number. Sadly, I can't use triggers and ...
Kristofers Kaprāns's user avatar
1 vote
0 answers
110 views

Auto Increment ID based on 2 columns

I have table called Logs. it has 4 columns ID, USER_ID, USER_TYPE, USER_ACTION where USER_ID is unique USER_TYPE is unique for every USER_ID and USER_ACTION can be anything 1,2,3 or A to Z. USER_ID ...
Zoheb Patel's user avatar
1 vote
0 answers
5k views

How to speed up a large INSERT INTO SELECT in Postgres

I have a simple derived table query that I am trying to execute efficiently: INSERT INTO derived_table ( userid , min_date , max_date) SELECT userid, MIN(service_date) as min_date, MAX(...
mmdanziger'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
0 answers
141 views

Postgresql stale result for insert into select

I've noticed when two processes (let's call them A and B) insert a record into the same table in Postgresql and don't commit those changes, the 2nd (later process, B) blocks until the 1st process (A) ...
orange's user avatar
  • 153
1 vote
1 answer
3k views

Insert multiple rows into a table with id from other table if not exists insert to other table

I have done similar task where I can insert a row into a table if data doesn't exists: WITH user_exists AS ( Select id from users where username='%s' ), user_new ...
undefined's user avatar
  • 151
1 vote
0 answers
41 views

Analyzing performance of an insert int postgres

I have a statement which is inserting a bunch of rows (doesn't really matter what or where for this question) into a Postgres DB and it isn't as fast as I'd like. I can run an explain query to see ...
Richard Wheeldon's user avatar
1 vote
0 answers
2k views

Transactional Replication : Issues with Identity Column in Subscriber

I have an Transactional replication set up form Server A to Server B. I have a table which has an Identity column as a primary key. In my publisher ,for the table tb1,the the identity column,Id1 was ...
user9516827's user avatar
  • 1,315
1 vote
0 answers
196 views

Slow insert on very large tables, (even in transactions)

I have a table with about 50 million rows, the table itself is fairly simple CREATE TABLE Word (id integer PRIMARY KEY, word TEXT UNIQUE) I then do an insert INSERT OR IGNORE INTO Word (word) ...
Simon Goodman's user avatar
1 vote
0 answers
89 views

Long INSERT on large table blocks INSERT on other tables

Server is MySQL 5.7 and the tables are InnoDB. The large table consists of 20+ GB of data. The large table has several indexes on it and also full_text_indexes! Use-Case explained: There are ...
powtac's user avatar
  • 161
1 vote
0 answers
367 views

Database Behavior while executing UPDATE and INSERTs on the same table

My gear: Pentaho BI Server 8.0 AS400 V7 My scenario: I have two processes that sometimes run at same time in the same table. This table has about 600M rows, so, an update can take long time to ...
ChoCho's user avatar
  • 111
1 vote
1 answer
1k views

MySQL 5.6 Bug Amazon RDS with replica. AFTER INSERT TRIGGER is not working. Solutions?

I have MasterDB and ReplicaDB in Amazon RDS environment. I need to move 1 table (MYTable) from replica to other external DB (ExternalDB). My solution (do not work) was to add a AFTER TRIGGER ...
Karel's user avatar
  • 11
1 vote
0 answers
19 views

AWS database low Insert/s count

I just started getting into AWS and until now I used my computer as a DB instance(both SSD and Ramdisk). On my computer I get 4.000 to 10.000 inserts/sec of random generated data. I modified the same ...
staynless's user avatar
1 vote
0 answers
93 views

How to populate a 'map' table from words of a text?

Having the database structure that follows: CREATE TABLE articles ( article_id int(11) unsigned NOT NULL AUTO_INCREMENT, article text, PRIMARY KEY(article_id) ) ENGINE=InnoDB; CREATE TABLE words ( ...
Googlebot's user avatar
  • 4,531
1 vote
0 answers
382 views

PostgreSQL - Violate foreign key on purpose

I have an app 1.0 that uses a database called let's say foo of at least 150 tables filled with data. I have upgraded the app to 3.0 and now I also have a database called foo2 which has almost the same ...
Philippe Champigny's user avatar
1 vote
0 answers
124 views

Error for some tables while inserting when default database is not 'master' in Sybase

I am not sure whether I should post this here or in Java's related site but because the error is a bit technical and Java developers may not know about it I thought this is the right place. So the ...
xbmono's user avatar
  • 111
1 vote
1 answer
2k views

MySQL events with TRUNCATE and INSERT

Can I create event that would first TRUNCATE table and then make INSERT? I know how to TRUNCATE and INSERT in separate events but need to do it immediately one after the other. I just don't know the ...
CroDexter's user avatar
1 vote
0 answers
958 views

DB2 : Dynamic Result Sets, Duplication in Sending output to File

What I am trying to do here is to Generate insert scripts for selected tables in my database in DB2 10.5 LUW. I have modified a procedure available here as below. CREATE PROCEDURE Create_Inserts ( ...
rrrohanb's user avatar
  • 184
1 vote
0 answers
582 views

Unable to insert explicit value in table due to IDENTITY_INSERT is ON Error

I am trying to insert explicit values in one of my Real time Audit Production database. There are more than 80 columns, I have just taken here 5 columns. I am writing syntax is as below: use ...
Md Haidar Ali Khan's user avatar
1 vote
0 answers
1k views

Improve postgreql insert performance when compared to Oracle. Low memory utilization by Postgresql threads

I am trying to improve the performance of Postgresql (version 9.2.3) inserts for a simple table with 1 bigint, 1 varchar, 1 float and 2 time stamps. A simple replication of my JDBC program is ...
Maddy's user avatar
  • 19
1 vote
0 answers
715 views

Can I simulate a sequence in pre-2012 SQL Server, with separate identity column, in function style?

A database on SQL Server 2005/2008/R2 is currently using an identity column in a separate table as primary key source. The reason is that the application, with NHibernate as ORM, needs to know the PK ...
Erik Hart's user avatar
  • 510
1 vote
0 answers
808 views

Better way to create an Events table?

I working on a medium sized PostgreSQL database with some schemas, each with some tables. In almost every schema there are tables that represent event tables, where something that has "happened to" ...
Bam's user avatar
  • 569
1 vote
0 answers
55 views

Did I Properly Diagnose That It's Not A SQL Server Issue?

We have a job that populates about 450,000 rows nightly in an update/insert job via a stored proc nightly. The .Net app pulls in data from a noSQL DB via an API call then pushes it out via a stored ...
Ali Razeghi - AWS's user avatar
1 vote
0 answers
393 views

Parallel insert in db2

I am trying to import from 8 files and insert into a single table in db2. I am running 8 .sql files that will do the task, but only one of them actually seem to be running and inserting into table. ...
Rangesh Ananthalwar's user avatar
1 vote
0 answers
4k views

Mongodb extremely slow with bulk inserts and showing erratic behavior

I have been massively frustrated by the erratic behaviour of Mongodb's bulk inserts. I'm trying to write 20K documents in a bulk-insert query. Sometimes the inserts take as low as 5 seconds and ...
VaidAbhishek's user avatar
1 vote
0 answers
64 views

How to append records from one complete table into a partly populated differently formatted table?

I have two tables of interest linked in a MS Access 2007 database. The master table called EMPL_MASTER (from which I only have read access, I cannot make changes) updates every time a system ...
pheidlauf's user avatar
  • 131
1 vote
0 answers
361 views

RETURNING syntax with multiple columns

Again, another question related to the answer of the one there: Insert with OUTPUT correlated to sub query table So I ended up with this query, written for SQL Server: CREATE TABLE tempIDs ( ...
Yugo Amaryl's user avatar
1 vote
0 answers
887 views

MySQL InnoDB slow inserts

I'm currently struggling with an MySQL Community Server 5.5.27 when inserting large numbers of rows into a table. We're running a 24 core Intel Xeon machine with an SSD software raid array. I did some ...
ralexandru's user avatar
1 vote
0 answers
130 views

Any drawback to lots of creating and renaming tables at InnoDB?

I'm trying to optimize an important cronjob for my program, and what it basically does is generate a long list of user ids with their score points. I have a table called user_ranks and it will bulk ...
Martin's user avatar
  • 121
1 vote
0 answers
746 views

When to split a large table

I run the SQL Anywhere DBMS, and we have a table of about 10 columns(normal text, timestamps etc). However, in my use-case, every single day, the table gets 2160000 new rows. It's also crucial that ...
Henrik Alstad's user avatar
1 vote
1 answer
463 views

Mutating Error , How to solve it

CREATE OR REPLACE TRIGGER LOAN_RP AFTER INSERT OR UPDATE ON FUND_DISBURSE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE V_FA FUND_DISBURSE.FUND_AMT%TYPE; V_RC NUMBER (10); ...
K_islam's user avatar
  • 11