Questions tagged [insert]
A core statement in SQL that does what it says on the tin: inserts data into a table.
749
questions
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 ...
- 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 ***************************
...
- 1
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/...
- 21
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,
...
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 ...
- 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 &...
- 27
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 ...
- 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....
- 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 ...
- 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 ...
- 113
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 ...
- 13
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 ...
- 1,267
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 (...
- 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 ...
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 ...
- 113
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) ...
- 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. ...
- 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-...
- 4,408
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 ...
- 1
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 ...
- 25
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....
- 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. ...
- 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 ...
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? ...
- 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 ...
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('...
- 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 ...
- 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 ...
- 113
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 ...
- 113
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 ...
- 371
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
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, '...
- 11
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 ...
- 245
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,
...
- 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 ...
- 111
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 ...
- 123
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) ...
- 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" ...
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,...
- 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 ...
- 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 ...
- 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 ...
- 337
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 ...
- 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 ...
- 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 ...
- 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 ...
- 49