Questions tagged [insert]

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

Filter by
Sorted by
Tagged with
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 ...
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 ...
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 --...
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 ...
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?
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 ...
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 *************************** ...
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/...
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 &...
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 ...
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 ...
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 ...
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 ...
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-...
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 ...
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 ...
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 ...
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 ...
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 ...
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

1
2 3 4 5
16