Questions tagged [insert]
A core statement in SQL that does what it says on the tin: inserts data into a table.
765
questions
0
votes
0
answers
9
views
How to create a list of increasing unique identifiers?
I'm creating a list of Connections objects (names of machines with IP address and IP port), but I'm having an issue with the Id field.
Normally this is the way I would work:
INSERT INTO [CONNECTIONS] (...
- 463
-2
votes
1
answer
18
views
PSQL avoid insert failing because a trigger failed
I want to put a trigger on a table, the code itself could work sometimes, while sometimes it might fail unexpectedly.
So I need the data to get inserted on the table regardless if one of its triggers (...
1
vote
1
answer
38
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 ...
- 254
2
votes
2
answers
33
views
For each row create a linked row in another table with auto-generated ID
In Postgres, I have an existing table:
things
thing_id
thing_name
destination_id
10
thing 10
null
15
thing 15
null
For each row in that table, I want to add rows to two new related tables one of ...
- 157
0
votes
0
answers
46
views
How to optimize insertion 10+ million data to PostgreSQL
I have a ticketing_system table and I try the insertion of 10+ million rows. So this cost 50 seconds. But I need almost a max of 10-15 seconds.
CREATE TABLE ticketing_system (
ticket_id UUID not ...
0
votes
0
answers
34
views
How to prevent insertion of row which differs only in primary key from existing row?
If my table contains the following
+----+-----+------------+-----------+
| id | uid | date | operation |
+----+-----+------------+-----------+
| 1 | abc | 2022-09-21 | create |
| 2 | xyz | ...
- 103
0
votes
1
answer
17
views
Mariadb: Sometimes inserts made during mysqldump are stuck forever in Updating state, and then service mariadb restart never finishes - how to debug?
I have a simple innodb Mariadb database used for logging (only inserts with insert ignore into a single large table).
I back up the database nightly with mysqldump --no-create-info --hex-blob --...
- 101
0
votes
1
answer
28
views
Simplify duplicate INSERT commands placing NULL for values that are not found
I have many trigger functions written in PL/pgSQL that follow the below logic:
SELECT my_name
INTO l_my_name
FROM data
WHERE data.my_number = NEW.test_num;
IF NOT FOUND THEN
INSERT INTO test_event
...
0
votes
1
answer
150
views
Oracle: INSERT if not exist
My question is if there's an atomic insert-if-not-exist statement in Oracle Database?
This is not part of ANSI SQL so each database vendor has his own way of doing this, if supported.
To qualify the ...
- 113
0
votes
1
answer
90
views
Cannot Insert it into in my table for Special characters when ANSI_WARNINGS is Off even with nvarchar
I want to insert a data row into my table as name: DSKWOR00 , but I get this error :
String or binary data would be truncated.
so I turned ANSI_WARNINGS OFF and it inserted the row but the result ...
1
vote
1
answer
30
views
How do you write ordered multi-inserts wih CTEs?
With the table like this:
CREATE TABLE test_1 (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
CREATE TABLE test_2 (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
CREATE TABLE ...
- 102
0
votes
1
answer
41
views
slave on logical replication does not get update real time
I successfully started logical replication on a testing environment. I generate insert on the master for some 60K records using procedure. While the insert process is running, i monitor the slave ...
- 41
0
votes
1
answer
20
views
Postgres FK referencing composite PK INSERT VIOLATION
I am trying to insert into a table (mdata) which has a compost foreign key referencing to a primary keys in another table (measurement), unfortunately I have this error
ERROR: insert or update on ...
- 101
0
votes
2
answers
38
views
MariaDB: can a SELECT query block other queries?
If I used a stored procedure having only Select queries in mariadb (NO Update OR Insert). Does this Select locks other queries (Select, Update or Insert) using the same tables?
- 11
0
votes
0
answers
67
views
Writing a ledger with high concurrency
I'm trying to achieve a high level of writes on a single account for a ledger, however, if there is a high level of concurrency, things start to slow down significantly.
My current setup is basically ...
- 1
1
vote
1
answer
146
views
single insert ignore on a table with no auto-increment
For this table, in mysql 5.7,
CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL,
`val` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
The table doesn't have ...
- 113
0
votes
1
answer
32
views
In Oracle SQL 19c, how can I INSERT a new record into a supertype and its related subtypes in one statement?
I'm designing a property management database that will hold - OWNER, CUSTOMER, and STAFF information.
As these use many of the same attributes, I intend to create tables for PERSON, ADDRESS and ...
- 1
0
votes
0
answers
48
views
Copying a record using Trigger in the same table
I have a table issues with following fields.
Whenever a new record is inserted in the issues table, I want that inserted record to be copied (as a backup) in the same table(issues). I have created a ...
1
vote
1
answer
74
views
Is it possible to reference an inserted column within the same INSERT?
This query gives the error:
create table qwe (a bigint, completed bit varying(100000));
CREATE TABLE
insert into qwe (a, completed) values (1000, repeat('0', a));
ERROR: column "a" does ...
- 222
0
votes
0
answers
36
views
A INSERT SQL is MySQL use memory too much, and mysql oom
when MySQL is running a insert data, the count of number is about 20w, and is about 100M but the memory useage is about more than 4G
*************************** 1. row ***************************
...
- 1
2
votes
1
answer
75
views
Multiple Computed, Dependent Columns on INSERT in SQL Server?
I'm relatively new to database programming and am looking for some some theory/best practices and feedback on an issue involving multiple dependent, cascading, computed columns for an OLTP SQL Server/...
- 21
0
votes
1
answer
2k
views
INSERT array elements from JSON object as table rows
I need to insert below JSON value into a table which will have two rows as id 1708,1 and 1708,2:
{
"id":1708,
"types":[1,2]
}
Below is what I have tried:
insert into table(1708,
...
0
votes
1
answer
44
views
attribute 51 of type users has wrong type
I'm trying to import data to a pg table from a csv file. The error I get is:
$ psql -U postgres -c "COPY users (first_name) FROM '/users.csv' (FORMAT csv)"
ERROR: attribute 51 of type users ...
- 309
0
votes
1
answer
43
views
Why does PG claim that the INSERT query was successful when it was not actually?
I have this in a loop:
INSERT INTO table (...) VALUES (...) ON CONFLICT DO NOTHING;
Even if there is a conflict (duplicate record) and the ON CONFLICT DO NOTHING rule activates, it seems to return a &...
- 27
0
votes
1
answer
32
views
How do I add to a table and then use the id for another query?
I have three tables, which I create with these queries:
create table parents(
rowid serial primary key,
display_name varchar,
unique(display_name)
);
create table clients(
rowid ...
- 113
0
votes
1
answer
185
views
Upsert on a table that has multiple fields and jsonb column using WITH statement
So I was trying to do an upsert based on a query result:
/*
Querying from this table:
id | arrive_date | arrive_location | thing_type | thing_count
*/
CREATE TABLE IF NOT EXISTS public....
- 49
0
votes
0
answers
87
views
Does time cost of insert depend on the collection's size in MongoDB?
I've noticed that the inserts on my MongoDB are getting slower as the collection grows. The collection doesn't have any indexes.
I wasn't able to find anything about this topic in the official ...
- 281
0
votes
1
answer
182
views
How create a row-level policy for purposes of using a new_user_id as a fk entry to another table
I have row-level security enabled on my users table. The table is part of a core schema. I have the following policy that provides access by the api user:
create policy "Api(public) can view ...
- 113
1
vote
1
answer
244
views
How to insert various records from multiple tables with a condition
I am creating a relational database and I have 2 tables currently ( Staff and Student)
Staff Looks Like this
StaffID
Class
TotalPhonesConfiscated
1
S201
1
2
S202
0
3
S203
1
Student Looks like ...
- 13
0
votes
1
answer
36
views
Stuck at inserting records into MariaDB conditionally
I know about insert ignore. That's of no use, because MariaDB does not consider NULL to be part of the uniqude index.
Therefore I have to use if ... else.
But this code does not run:
select *
from ...
- 1,297
0
votes
1
answer
191
views
Using OPENJSON, can't get results to GROUP BY
I'm passing a JSON string to a stored procedure in order to insert warehouse inventory.
Sometimes, there will be multiple line items with the same product, going to the same shelf, on the same pallet (...
- 15
0
votes
3
answers
431
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 ...
0
votes
1
answer
3k
views
ON CONFLICT DO UPDATE command cannot affect row a second time when trying to pass additional columns from a CTE in postgresql
Based on the SO: https://dba.stackexchange.com/a/46477 answer provided, I have been extending the Function with UPSERT in Postgres 9.5 or later for my own purposes to insert data into multiple ...
- 113
1
vote
2
answers
359
views
Lock specific select query until all insert transactions are commited
I have an insert-only table in my database that contains time-series data.
When my system starts, I need to populate an in-memory state with the last-month data (each row contains data for one minute) ...
- 125
0
votes
2
answers
106
views
How to resolve INSERT contention in Azure SQL Database?
I'm working with an Azure SQL Database that has a very hot logging table. Any time a row in any other table is inserted, updated, or deleted, the web application writes a row to this logging table. ...
- 2,078
0
votes
1
answer
179
views
Insert Into Table performance questions
I am writing a stored procedure, main purpose of it will be the insertion into table
Procedure will be signed by certificate, and sending email notifications
Procedure will accept user-defined table-...
- 4,910
0
votes
1
answer
12
views
Insert select from ignoring function errors
I'm changing database structure of a very large existing database using some performance related improvements. Once created the new architecture, I'm moving data from old structure to the new one. To ...
- 1
0
votes
1
answer
197
views
How to implement insert, update, delete with a many-to-many relationship for tags?
I am creating an application to manage some ebooks and I would like to have tagging functionality.
The best db design I have found is to be the many-to-many relationship. I know how to set up my ...
- 25
0
votes
1
answer
207
views
SQL Server select into vs. insert into performance with embedded cross apply openjson
Note: updated 11/15/2021 to address comment feedback.
It seems SQL Server 2016 has some odd little corners when it comes to performance.
Version:
Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13....
- 21
0
votes
1
answer
42
views
how to insert redundant data
I am inserting data, which ipaddress_client column and analysis_route column should be written with the same data from PHP input (redundant) following the total row of the geomjson&id_esb column. ...
- 1
0
votes
0
answers
85
views
MariaDB veryslow Inserts Query/Import via Dbeaver
I have a MariaDB server that via DBeaver is getting very slows insert times.
Tables with 10.000 lines only is getting imported in 2minutes
The DB Manager is trying to import CSVs, some with only 80mb ...
3
votes
1
answer
445
views
MySQL/MariaDB: can a SELECT query block an INSERT query?
Let's suppose that I have a query in the form:
INSERT INTO tableA (...) select ... from tableB;
Now, is it possible that this query, while executing, blocks plain INSERT statements to tableB? ...
- 684
0
votes
1
answer
56
views
Why insert statements get slow when having too many indexes?
I am having a slowness in my insert statements when working with MongoDB, and when I did a quick investigation, I though this might happened because the table has 11 indexes. Plus, I know that when I ...
0
votes
0
answers
20
views
Avoid duplicated insert in PostgreSQL [duplicate]
I'm struggling to handle concurrent insert in my table on PostgreSQL.
I have the table monitoring_services with the following structure:
create table monitoring_services(
id integer default nextval('...
- 1
0
votes
1
answer
273
views
MYSQL Error 1064 on INSERT INTO with CTE [closed]
I am trying to write a stored procedure to return a calculated value from a dataset by writing the result to a table. When I run this code on MySQL 8.0.32, I get an error 1064 Syntax error on the ...
- 1
0
votes
2
answers
350
views
UPSERT unless the date is the same
There's a table
CREATE TABLE IF NOT EXISTS table00 (
userid int8 PRIMARY KEY,
save date NOT NULL,
value int4
);
where I want to upsert values IFF the date is not the same ...
- 123
0
votes
1
answer
191
views
INSERT a row at most once per day
I have a Postgres table which (for simplicity) looks like:
CREATE TABLE IF NOT EXISTS tt_series (
i serial8 PRIMARY KEY,
userid int8 NOT NULL,
date int4 NOT NULL,
followers ...
- 123
0
votes
1
answer
173
views
Sybase ASE 15.7 procedure cache fail on INSERT
Sybase executes SELECT query with 50k rows in 10 seconds.
If execute INSERT INTO ... SELECT ... FROM ... construction it executes 2 minutes
But if we increase retrieved rows number in 2 times - 100k ...
- 401
1
vote
1
answer
313
views
Insert values to table only if first Insert was successful
Is there a way I could check the status of an insert query whether it was successful or not. I want to write to another table only if the first insertion was successful.
Below is the table I want to ...
- 125
0
votes
2
answers
656
views
insert on table being unnaturally slow
I have an "Basic" pricing tier Azure SQL database with a table of 7 columns, an ID column of int that is the clustered index and primary key, one datetime2(0) column, 3 varchar(100) columns ...
- 167