Questions tagged [insert]
A core statement in SQL that does what it says on the tin: inserts data into a table.
834
questions
0
votes
1
answer
49
views
How to INSERT a new record into 2 tables with FKEY inheritance?
Inheritance has been setup with FKEY instead of INHERITS due to FKEY limitation.
My question: How to INSERT a new record into bonds table?
CREATE TABLE IF NOT EXISTS instruments (
id SERIAL,
...
- 1
0
votes
1
answer
75
views
Can the order of concurrent inserts be guaranteed with some column
I have some services, which can execute insert to insert new elements into a table of my database (Postgresql). So in the view of the database, many inserts are executed concurrently.
My question is ...
- 123
0
votes
1
answer
37
views
Postgres trigger check if insert affected column
I have a table where some columns have to be "protected", meaning they should not be written to by most users. I want to enforce this with a trigger function (as there is additional logic to ...
- 1
4
votes
3
answers
153
views
Prevent inserting duplicate entries under READ COMMITTED Isolation
We have a SQL Server table called dbo.Dim_Target. Multiple threads try to INSERT new data into this table in parallel. Each parallel thread first checks for matching data and then only inserts new ...
- 687
0
votes
0
answers
74
views
Insert operations in MariaDB causing deadlocks
I created the following table in MariaDB:
CREATE TABLE IF NOT EXISTS test_table (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
uuid VARCHAR(1024) NOT NULL DEFAULT '',
CONSTRAINT unique_name UNIQUE (...
0
votes
1
answer
75
views
PostgreSQL: Approach for aggregating records and upsert vs delete and insert
We have a file processing service that extracts some device configuration information from daily files and stores it into PostgreSQL tables.
We have two layers of tables,
The loading tables that ...
- 143
0
votes
0
answers
156
views
Random long writes (waiting for handler commit)
I have some INSERT/UPDATE queries who takes a long time randomly.
I try to launch the same INSERT several times with profiling enable.
And I remark that the duration is always in waiting for handler ...
- 1
0
votes
0
answers
14
views
Variable time execution for INSERT/UPDATE
I do an INSERT/UPDATE in a table who has around 1+ million rows in MySQL 8.0. I don't know why I have different execution times for the same data to insert.
For example, I try to insert exactly the ...
- 1
1
vote
1
answer
838
views
How to have Postgres return id when ON CONFLICT DO NOTHING
Suppose we have the following table:
CREATE TABLE names(
id SERIAL NOT NULL,
CONSTRAINT names__pk PRIMARY KEY(id),
name TEXT NOT NULL,
CONSTRAINT names__name__unq UNIQUE(name)
);
INSERT INTO names(...
- 113
1
vote
3
answers
165
views
Decreasing execution time of insert and deletes in SQL Server
I'm looking for some advice on reducing the execution time for inserts and deletes on a custom database setup that is used for logging data from a remote location.
Basically I have a custom program ...
- 41
3
votes
1
answer
585
views
"#1292 - Incorrect datetime value" when time is within one hour
I'm running into a bizarre error with a timestamp column in a MySQL DB. When inserting the date/time value, it fails with a #1292 error if the time is between 02:00:00 and 02:59:59. Here is a sample ...
- 141
1
vote
1
answer
86
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
)
...
- 37
0
votes
2
answers
76
views
(PostgreSQL) Insert a tuple containing specific value in a column linked to a sequence
I started writing this post as a question but, while reproducing the error, I found the solution in the PostgreSQL documentation. So I'll share the solution here for the community.
A PostgreSQL 14 ...
0
votes
0
answers
146
views
How to get last executed statement on a particular table in Oracle?
Trying to identify what ghost process is inserting records into a particular table. Is there a way to get the exact query?
-2
votes
2
answers
97
views
Replace "INSERT" by "UPDATE"
I'm working with some tables in an SQL-Server database, and I'm doing some crucial modifications. In order not to loose everything when I mess up, I decided to do a "Generate script" (data ...
- 579
1
vote
1
answer
82
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 = ...
- 23
0
votes
0
answers
110
views
How to insert data into temporary table from the table having encrypted columns in sql server
I am trying to insert data into a temporary table(#table1) from the table(table2) having encrypted column in it.
I am getting below error while trying to insert data into temporary table from the ...
- 11
0
votes
1
answer
445
views
question about using Insert where not exists instead of if not exists then
I have a procedure that insert data to table where the data doesn't exists.
right now it's done in two steps :
check if the data exists already,
if not, insert it
i want to change it to do INSERT ...
- 47
0
votes
2
answers
118
views
SQL Server inserts fails silently
I have a stored procedure that inserts some rows into a table. Sometimes it seems to fail (can't find the new records) although I know the statements did execute. The code I use is bellow. After ...
- 25
0
votes
1
answer
70
views
Move row of a table to an inherited table in PostgreSQL
I have tables created as such:
BEGIN;
CREATE TABLE tag (
id SERIAL,
name VARCHAR(255) UNIQUE NOT NULL,
description VARCHAR(511) NOT NULL DEFAULT '',
PRIMARY KEY(id)
)...
- 101
0
votes
0
answers
117
views
Mariadb Triggers after multiple insert, update one table one one time
CREATE TRIGGER after_composers_insert AFTER INSERT ON composers
FOR EACH ROW UPDATE listings SET composer_name = NEW.composer_name
WHERE composer_id = NEW.composer_id;
CREATE TRIGGER ...
-1
votes
2
answers
71
views
SQL breaking my query where there is NULL value
Here is my sql statement and the error I am facing:
SQL> INSERT INTO COUNTRY(COUNTRY_CODE)
select unique(country_code) from AIRPORTDEMODATA where country_code IS NOT NULL;
2 INSERT INTO COUNTRY(...
0
votes
1
answer
42
views
Is commit of first insert necessary if second insert has constraint depedent on first insert?
Using MariaDB I am running an insert on one table and then trying to run another insert on second table. Due to a constraint the second insert should only succeed if the first one was successful.
If I ...
- 103
0
votes
1
answer
253
views
Auto-increment [id] field in a table in an SQL-Server database
I have different tables, containing an id field. That field is typically defined as id (PK, int, not null).
Normally, when adding an entry to a table, I add the value of the id field myself, but I'm ...
- 579
0
votes
1
answer
259
views
In PostgreSQL, what is the `index_predicate` for in `INSERT`?
The docs on INSERT say:
If an index_predicate is specified, it must, as a further requirement for inference, satisfy arbiter indexes. Note that this means a non-partial unique index (a unique index ...
- 925
1
vote
1
answer
156
views
Lazily Evaluate Insert Arguments If Constraint Violated
create table a (
b text not null unique,
c text not null
);
-- Sleeps for 1 second as expected.
insert into a (b, c) values ('d', pg_sleep(1));
-- Sleeps for 1 second even though record is ...
- 113
0
votes
1
answer
45
views
Delete records from table that are not in the file being inserted
I have a Snaplogic pipeline that queries a web application every night and loads the data into a SQL Server table. I use the 'Update' snap to add new records or add any changes. The goal is that the ...
- 281
1
vote
1
answer
679
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, ...
- 175
1
vote
1
answer
135
views
insert trigger to auto-set key fields for composite primary key
Basically the next step for Error "UNIQUE constraint failed" with composite primary key:
I created a trigger after insert to set SEQ to the highest SEQ plus one if it's less than one.
...
- 125
1
vote
1
answer
193
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 ...
- 21
1
vote
0
answers
166
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 ...
- 11
0
votes
1
answer
409
views
What is the most straightforward way to create subtypes and supertypes at the same time?
I hope this isn't too basic a question, but I am hoping that the DB engineers here will help me out. Do relational DBs usually have a straightforward way of doing the following?:
Create a supertable ...
0
votes
1
answer
270
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
...
- 1
0
votes
1
answer
47
views
Compositing ordered SQL tables without Joins
I'm currently exploring some weird ways of doing things and was wondering if anyone has any experience with the following methodology.
Background
I have a handful of NF3 tables, and NF1 source data. ...
- 117
0
votes
1
answer
126
views
Migrate columns from table A to table B, and update A with a reference to B
I have a table A with some columns that need to be migrated to a new table B. But I also need to create a column in table A to reference the id's from the new columns in the table B. I've tried doing ...
- 1
0
votes
1
answer
415
views
SQL Server: Inserting empty value (zero-arity INSERT) [duplicate]
I made test table:
create table tmp (id BIGINT NOT NULL IDENTITY PRIMARY KEY)
Now I want to insert a value:
insert into tmp () values();
and it complains:
SQL Error [102] [S0001]: Incorrect syntax ...
- 509
0
votes
1
answer
87
views
MySQL gives Unknown column error when forward engineering database from diagram
I have a MySQL EER diagram the I forward engineer into a database. I also use the forward engineer feature to generate insert statements for data automatically. After entering about 120 rows of data I ...
2
votes
1
answer
80
views
Inefficiency of index in SELECT requests after large INSERT requests on myisam tables on MariaDB 10.6
This is my first post on this website. Please don’t hesitate to indicate me if there is any problem in my post.
I have a new issue when migrating from MariaDB 10.1 to MariaDB 10.6 on indexes with ...
- 21
0
votes
0
answers
133
views
Problem with inserting a new row - unknown column name reported missing
I'm new to SQL Anywhere. I have a SQL Anywhere 11 database that was not designed by me. I'm trying to insert a new row in a table with let's say 3 columns. When I execute the sql statement
insert into ...
0
votes
1
answer
1k
views
Insert rows into a (postgres) table where the first col is a static value and the second col is the result of a SELECT DISTINCT from another table
I haven't had to use SQL in years and I'm clearly rusty.
I have a situation where I'm trying to insert multiple rows into table A.
There are two columns of data to be inserted, the first is a "...
- 3
0
votes
0
answers
65
views
How do I automatically insert a row into parent table when a row is created in a child table?
I am working on a MySQL database that has to treat organizations and people as interchangeable in some instances and not others. Because of this, I created a table called LegalEntity as a superclass. ...
2
votes
2
answers
2k
views
PostgreSQL: Insert dynamic columns and values from jsonb argument
This question relates to Update dynamic column names using keys from jsonb argument and my code is based on the answer provided there by https://dba.stackexchange.com/users/3684/erwin-brandstetter
I'm ...
0
votes
1
answer
1k
views
SET BIGINT fieldname value to 0 automatically if inserting empty string
How do I do something like :
INSERT INTO tablename SET customer_id = '' WHERE <condition>;
customer_id is BIGINT NULL Default NULL
But when I execute SET customer_id = '' it gives
Incorrect ...
- 279
1
vote
2
answers
2k
views
How can I insert into a Postgres table with a unique constraint without waiting on concurrent transactions?
I have a table like this in a Postgres database:
CREATE TABLE tabwithunique (
id SERIAL PRIMARY KEY,
key BIGINT NOT NULL UNIQUE,
value BIGINT NOT NULL
);
I want to design a statement that ...
- 121
1
vote
2
answers
417
views
MariaDB: Large INSERT SELECT query failed without generating an error
I have a large complicated neuroscience dataset that I am attempting to organize in a database for concurrent and consistent access with my lab. This is my first foray beyond much smaller experiments ...
- 23
1
vote
2
answers
1k
views
Insert values and add values to foreign key
I have two tables:
CREATE TABLE est (
est_id serial PRIMARY KEY
, est_nom varchar(70)
);
CREATE TABLE bm (
id serial PRIMARY KEY
, est_nom int
, nom varchar(70)
, CONSTRAINT FK_bm_est FOREIGN KEY(...
1
vote
2
answers
280
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 (
...
- 306
0
votes
1
answer
149
views
Insert Statement on large MySQL table does not terminate
I have a MySQL database with several tables.
One table (InnoDB) is quite large (about 490 million rows).
The table consists of 4 columns:
id: int(11), auto_increment
term: varchar(255)
docId: int(11)
...
- 3
-1
votes
1
answer
7k
views
Stored procedure to Insert data into table with select statement and argument parameter [duplicate]
I am creating a stored procedure which will populate my table with data passed from procedure argument and from other table.
I have a table with three columns (cart_id, product, sold_quantity). Now, I ...
- 239
0
votes
2
answers
944
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 ...
- 11