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
1 vote
1 answer
23 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 ...
user avatar
  • 202
0 votes
0 answers
19 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 *************************** ...
user avatar
2 votes
1 answer
53 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/...
user avatar
0 votes
1 answer
178 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, ...
user avatar
0 votes
1 answer
21 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 ...
user avatar
  • 289
0 votes
1 answer
37 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 &...
user avatar
0 votes
1 answer
21 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 ...
user avatar
  • 113
0 votes
1 answer
60 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....
user avatar
  • 49
0 votes
0 answers
21 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 ...
user avatar
  • 281
0 votes
1 answer
26 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 ...
user avatar
1 vote
1 answer
21 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 ...
user avatar
0 votes
1 answer
29 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 ...
user avatar
0 votes
1 answer
73 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 (...
user avatar
  • 3
0 votes
2 answers
104 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 ...
user avatar
0 votes
1 answer
1k 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 ...
user avatar
1 vote
2 answers
63 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) ...
user avatar
  • 125
0 votes
2 answers
63 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. ...
user avatar
  • 2,008
0 votes
1 answer
109 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-...
user avatar
0 votes
1 answer
10 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 ...
user avatar
0 votes
1 answer
72 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 ...
user avatar
0 votes
1 answer
125 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....
user avatar
  • 21
0 votes
1 answer
37 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. ...
user avatar
  • 1
0 votes
0 answers
47 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 ...
user avatar
3 votes
1 answer
268 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? ...
user avatar
  • 654
0 votes
1 answer
50 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 ...
user avatar
0 votes
0 answers
19 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('...
user avatar
  • 1
0 votes
1 answer
112 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 ...
user avatar
  • 1
0 votes
2 answers
77 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 ...
user avatar
0 votes
1 answer
75 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 ...
user avatar
0 votes
1 answer
111 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 ...
user avatar
1 vote
1 answer
71 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 ...
user avatar
  • 125
0 votes
2 answers
141 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 ...
user avatar
  • 157
0 votes
1 answer
267 views

How to add a new row to a sql server table in the first position (from python) [duplicate]

I have an sql table Tab 1 in a Database. It is ordered in descent way based on the date. This table has to be updated with new records from python. In python I have another table Tab 2 similar to this ...
user avatar
  • 103
0 votes
1 answer
315 views

Insert a FOR JSON result into a table column

I have the a result of a FOR JSON expression, and I'd like to insert it into a table column. How do I do that? Here is one of my unsuccessful trials (note that the SELECT hightrees ... FOR JSON PATH ...
user avatar
  • 331
-1 votes
2 answers
229 views

INSERT VALUES TO A COLUMN WITH 2 FOREIGN KEY

I have tables "course" and "student" with n-n relationship,so I create another table name "course_student" to handle that. --Table course-- .id //primary ...
user avatar
0 votes
2 answers
63 views

Insert query in Orcale database - not showing the row in a table

I am inserting a row in the Oracle database table. The query looks like as below - INSERT INTO <schema_name>.<Table_name> (SID, LABEL, DESCR, GTI_EXPTYP, GTI_MODUS, GTI_NAME) VALUES (22, '...
user avatar
0 votes
0 answers
11 views

Can I version non-seed data scripts in SSDT project?

I have an SSDT project to version my DB schema. Now I'd like to add to it script files that insert/delete data for integration tests. These scripts are not post-deploy, they will be called before ...
user avatar
0 votes
1 answer
1k views

How to insert a row from JSON in Postgres

I have 2 tables recording company market data. A table recording stock market symbols CREATE TABLE appl.symbols ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, symbol VARCHAR(10) NOT NULL, ...
user avatar
  • 125
0 votes
0 answers
86 views

How to transfer data from one table to another with exception handling in Postgresql?

So, I have two tables, one where I import data from an excel file all in string format without considering any sorts of constraints or data type validation. And then the data is transferred to the ...
user avatar
2 votes
2 answers
2k views

How to create a postgres function that returns a value

I am trying to move some of my web app logic into postgres functions. But I am hitting some errors while creating a very basic insert function. This is the function that I am trying to create; CREATE ...
user avatar
1 vote
0 answers
46 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) ...
user avatar
  • 123
0 votes
1 answer
183 views

Insert into table with polymorphic association in database

I want to design a database where some tables have multiple foreign keys. I am looking for the best design approach. I found out that this kind of problem is called "polymorphic association" ...
user avatar
1 vote
1 answer
712 views

Constraint in ON CONFLICT clause has no associated index

I have this simple setup using PostgreSQL 13, latest dockerized: CREATE TABLE stop ( id INT UNIQUE, name TEXT, PRIMARY KEY(id) ); CREATE TABLE line ( id INT UNIQUE,...
user avatar
  • 111
1 vote
2 answers
138 views

Missing an inserted row

I am having a strange heisenbug when a set of queries in a transaction (in MariaDB 10.2.38) somehow results in missing rows afterward. I mean that there are rows inserted within the transaction, but ...
user avatar
  • 31
0 votes
1 answer
36 views

Adding new user info to current old user accounts

Hey all so I have a database that has been up and running for a few months now. I had a request to add the users First and Last name as well as their organization. This was easy enough to do for NEW ...
user avatar
  • 105
0 votes
2 answers
1k views

Avoid duplicates in PostgreSQL among the rows that are going to be inserted

I have a destination table a and a set of source tables b and c. I'm trying to do an insert to a from b and c, and additionally check if such combination of (column1, column2) do not already exists in ...
user avatar
0 votes
0 answers
23 views

MySQL scheduled events not working on newly-added data

I can get a scheduled event to perform calculations on a current table. But it won't perform calculations on any new rows that are added to the table. How can I get the event to use the current ...
user avatar
  • 101
4 votes
1 answer
186 views

INSERT duplicate rows for multiple, cascading 1:M child tables

Imagine multiple parent-child tables with 1:M relationships. I would like to "cascade"-insert duplicate rows based on root parent table row selection. Each table has IDENTITY primary key and ...
user avatar
  • 451
1 vote
2 answers
567 views

ON DUPLICATE KEY UPDATE in INSERT INTO SELECT with GROUP BY

I have the query of INSERT IGNORE INTO t2 (ID,P1,P2,Items) SELECT ID,P1,P2,SUM(Items) FROM t1 GROUP BY ID,P1,P2; Since I need to run this query frequently to inspect changes in t1, I need ...
user avatar
  • 4,169
0 votes
1 answer
48 views

Insert into table if column <> "Text" for each project

I have created two triggers to insert into fact table if a new row is inserted into dim table. One checks if Project exists already and if not, then query will be triggered and adds 12 new rows per ...
user avatar

1
2 3 4 5
15