Questions tagged [insert]
A core statement in SQL that does what it says on the tin: inserts data into a table.
683
questions
1
vote
1answer
7 views
SQLite3 'OR IGNORE' leaves empty primary key IDs
I use 'INSERT OR IGNORE INTO' in my main table which works fine but leaves empty primary key IDs when records are ignored.
This leads to big jumps in my primary key numbering because I re-scan part of ...
0
votes
0answers
14 views
Analyzing performance of an insert int postgres
I have a statement which is inserting a bunch of rows (doesn't really matter what or where for this question) into a Postgres DB and it isn't as fast as I'd like. I can run an explain query to see ...
0
votes
2answers
23 views
Hint: There is a column named “pma_timestamp” in table “table_a”, but it cannot be referenced from this part of the query [closed]
I want to use a user defined function so that while inserting values in column i want to insert last_day of the month in one column by referencing other column in same table.
my code is-
insert into ...
0
votes
1answer
33 views
Prevent unique constraint violations caused by concurrent inserts
I have a situation where I need to insert a unique record into the database:
SELECT user FROM users WHERE id = 'some_randomly_generated_id'
If user does not exist, INSERT INTO users WHERE id = '...
0
votes
1answer
14 views
PostgreSQL update table then insert on another table
I have a User table and a Log table. I need to update a field on the User table and insert a new entry on the Log table recording the update. I have written the statement for the update:
UPDATE ...
0
votes
3answers
16 views
Load data from a Table that has the same structure as Table1 with the only diferrence of an ID not null column?
So, I know that if you want to load data into a table that does not have the same structure as the data, well you have to do it in the way of column by column, the thing that in this situation, I have ...
0
votes
1answer
52 views
How to ensure no duplicate records are added when using multiple Node JS processes
We are working on an appointment scheduler. We have a table Appointment that stores an appointment with starttime, endtime, assigneeId and eventId.
We are using Bookshelf.js as an ORM with Node JS. ...
0
votes
1answer
68 views
SQL CTE Expression with Cross Join - Can it be simplified?
I am looking to simplify the below SQL Query. Currently it's taking 44 mins to run.
The main issue seems to be the WHERE [Versions] = (SELECT max([Versions]) from BPC_CTE) statement.
What I'd like to ...
0
votes
1answer
22 views
MySQL Insert/Update across 3 tables with 1m+ rows
To start with, I know nothing of database design, so I apologise if this seems obvious to others. I have been researching up to 3NF over the last few weeks, and I think I have a layout that works.
I ...
0
votes
1answer
43 views
“SQL command not properly ended” with trigger
Problem
No matter what I do, I can not get my trigger to work. It seems to always complain about SQL command not properly ended, ignoring SQL statement and so on.
Research
I have spent hours looking ...
0
votes
2answers
23 views
Can we WITH x AS ( … ) INSERT INTO y(k) SELECT * FROM x in MySQL?
We can do INSERT INTO SELECT, but what if I have a bunch of WITH AS statements (about 27ish) for the SELECT part? My current solution is do the query then scan using programming language (Golang for ...
0
votes
0answers
25 views
Trigger to enforce unique constraint on text column (Geometry) to resolve INSERT conflicts
I have a simple table with a geometry column like so:
CREATE TABLE geomtable
(
gid serial,
geom geometry,
trip_count integer
);
I want a unique constraint on the geometry column, such that ...
0
votes
1answer
54 views
Why can't I make this insert statement a procedure?
The insert statement works, but when I try to make it a procedure it doesn't compile.
CREATE OR REPLACE PROCEDURE INSERT_INTO_CPP AS
BEGIN
INSERT INTO cpp /*+ ignore_row_on_dupkey_index ( cpp ( ...
0
votes
1answer
32 views
How do I close log files that are open in OracleServiceORCL after inputting data into Oracle?
I inserted data into a table called cell_local_stage1. With this procedure:
CREATE TABLE cell_local_stage1
(
construct_id NUMBER(10),
n_term VARCHAR2 (50),
enz_name VARCHAR2 (50),...
0
votes
1answer
48 views
Problem Insert Data to connected tables postgresql
I have 2 existing tables: ‘projects’ and ‘units’.The two tables are connected through the project id. In the table 'projects' the column is called ‘id’ and generates every time a new project is ...
0
votes
1answer
24 views
Why am I getting ERROR: column “GLOBE-BE-154” does not exist
I create a Table in a Database and looks as following
test1=# SELECT * FROM stunden_Plan;
stunde | montag | dienstag | mittwoch | donnerstag | freitag
--------+--------+----------+----------+--------...
0
votes
1answer
37 views
Is my theory on INSERT deadlocks correct?
I originally had this (as three separate PG queries, from my applications):
INSERT INTO table1 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
INSERT INTO table2 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
...
0
votes
0answers
60 views
How to insert variable number of rows inside an SQL Function with multiple queries?
My original idea was to use a transaction, since we have three statements and I want all or none. The transaction block looks something like below:
BEGIN;
INSERT INTO analysis_record (user_id,...
0
votes
2answers
24 views
how to insert big data in mariadb with execution time less than max_statement_time value
i have some table with this structure.
CREATE TABLE `trcdmppdetail` (
`mpp_code` varchar(50) NOT NULL,
`company_code` varchar(50) NOT NULL,
`mppdetail_date` datetime(3) NOT NULL,
`position_id` ...
0
votes
0answers
48 views
Making a trigger to update FK based on columns in another table and an external table
I'm inserting data into a database and I want the FKs to be populated automatically based on other information that links each FK to its corresponding PK.
Here's my plan:
Create an external table of ...
2
votes
1answer
88 views
Oracle: Inserting into table error - either duplicate column name or invalid datatype
I'm trying to insert data from an external table.
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
(construct_id,
n_term ,
enz_name,
...
0
votes
2answers
27 views
Transfer rows into a new table, forcing to generate unique primary keys till its insertable
Lets say we have the following scenario...
+-----------------+-------------+---------------+
| Table | PK | Additional |
+-----------------+-------------+---------------+
| ...
0
votes
1answer
80 views
Inserting into table: either invalid datatype or duplicate column name error. (But the datatype is valid and there's no duplicate) [duplicate]
Spoiler: solution to this is here. the solution was really simple.
I'm trying to insert data from an external table.
Here's the code I'm trying to use:
INSERT /*+ ignore_row_on_dupkey_index ( consruct ...
0
votes
1answer
34 views
Why am i getting “missing right parenthesis” error when i try to LOG ERRORS when loading from an external table?
I've successfully created an error logging table
BEGIN
DBMS_ERRLOG.create_error_log(
dml_table_name => 'enzyme',
skip_unsupported => TRUE);
END;
/
desc ERR$_ENZYME;
Name ...
0
votes
0answers
12 views
PostgreSQL rule to delete record before insertion, if the record has changed
I'm not sure it's possible to do this with a rule, but what I want to do is, when you insert a record:
If it doesn't exist, just insert it as usual.
If it exists, but each field is equal to what you'...
0
votes
1answer
33 views
INSERT .. ON CONFLICT DO UPDATE SET error
When I use the following query :
cur.execute("""INSERT INTO TABLE
(SELECT ID_tmp,First_seen_tmp,Last_seen_tmp,Duration_tmp
FROM tmp_table)
ON CONFLICT (ID)
DO
UPDATE SET Last_seen=...
4
votes
3answers
125 views
(Deadlock) Select Query Causes Insert Query to be Blocking in Transaction
Database: MySQL
I have a running app with concurrency transactions in it. I noticed that there are so many errors occur related to deadlock.
The error says
SQLSTATE[40001]: Serialization failure: 1213 ...
6
votes
2answers
2k views
Why is “INSERT INTO table;” a syntax error?
I have this:
CREATE TABLE people
(
id bigserial,
timestamp timestamptz DEFAULT now() NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE "...
0
votes
1answer
16 views
SQLite constraint violation - How to insert data to this schema?
First of all, sorry for the question but I was hours searching for a solution and the only things that I got are Java exceptions. I'm developing a little web crawler for movies, so I created this two ...
0
votes
0answers
26 views
insert or update with summary subquery
i have a problem with follwing query
INSERT IGNORE INTO cartellino_anno (iddipendente, mese, ordinarie,effettive,assenze,straordinari,ritardo,anticipo,richiestaassenza,richiestastraordinario,...
0
votes
1answer
36 views
Is it possible to make a trigger that does not allow INSERT?
I'm currently learning how to implement sqlite commands through C code and I've been trying to figure out the best way to ensure that no data can be inserted to a table (which will only happen during ...
1
vote
2answers
37 views
How can I use LOAD DATA LOCAL INFILE for specific columns?
I have a directory of text files, as the filename is the id, and the content text for this table:
CREATE TABLE texts
(
ID int(11) unsigned,
Added date,
Text MEDIUMTEXT,
PRIMARY KEY(ID)
) ENGINE=InnoDB ...
0
votes
1answer
26 views
MySQL: After inserting data into a table, the table remains empty
i'm trying to run this simple query:
insert into diff_set_values(value)
select value
from diff_sets
group by value;
but after it is finished, there is no data in the destination table ...
0
votes
2answers
40 views
What is wrong with this mysql INSERT statement?
I have table:
Book:
- id:(int) primary key, autoincrement, not null,
- title:(varchar) not null,
- price: (decimal) not null
and
Artical:
- id:(int) primary key, autoincrement, not null,
- title:(...
0
votes
0answers
29 views
SQL Trigger fails retrieve new record in second query
Any advice on this will be appreciated
This script created a table with new records.
My script is failing to assign the "Inserted" ID to the query but manually assigning a value works
(a) ...
0
votes
2answers
43 views
How to insert records using select statement?
Hi I am working on one project, here what I am trying is, I want to insert records what I get using select statements into temp table. but it is giving error to me, can any one help me to solve this ?
...
0
votes
1answer
25 views
How to prevent different connections treating bytea in same query differently?
I find that identical postgresql queries issued by two different clients are handled differently. Specifically, bytea values are inserted differently.
A query that demonstrated the behaviour is this:
...
0
votes
2answers
20 views
Insert Only first Occurence in Oracle table
i have a table
CREATE TABLE PEOPLE
(
NAME VARCHAR(64 BYTE)PRIMARY KEY NOT NULL,
TIME TIMESTAMP(6),
NICKNAME VARCHAR2(16 BYTE),
STATE VARCHAR2(50 BYTE),
CITY VARCHAR(128 BYTE)
)
I want to insert in ...
0
votes
1answer
34 views
HeidiSQL rounds down double values upon insert…select
I have the following table:
CREATE TEMPORARY TABLE `new_aps` (
`MinuteStart` datetime NOT NULL,
`ProxyListID` bigint(20) NOT NULL,
`SuccessCount` double(22,0) NOT NULL,
`UnknownCount` double(...
0
votes
2answers
546 views
Using Python to insert JSON into PostgreSQL [closed]
I have the following table:
create table json_table (
p_id int primary key,
first_name varchar(20),
last_name varchar(20),
p_attribute json,
quote_content text
)
Now I basically ...
0
votes
0answers
11 views
mysql queries in 2 times (select) + 1 time (Insert) verifications handle problems for db to dont create double inserts
I want to make an 2 way select verification for all users in that kind of code version:
select * from table_A where other_user_id='B'
if (1) -> update set response_A='1'
if (0) -> select * from ...
0
votes
1answer
26 views
MS SQL Management Studio - Script of INSERT statements leaves empty chars for shorter than limit NVARCHAR?
I have just observed in MS Management Studio that if we want to get the script for table's inserted values the output includes the unused free data space which is in excess for the e.g. NVARCHAR data ...
0
votes
1answer
22 views
How to insert json into a table and have certain columns remain json [closed]
I want to insert data into a table where the first level keys are inserted as values and not as json. Although column "p_attribute" needs to store the nested json. For example, in case of Jane Doe, I ...
0
votes
1answer
31 views
only last row is inserting in table when having “instead of insert” trigger on that table [duplicate]
I have this trigger
CREATE TRIGGER [dbo].[intrigger] ON [dbo].[test]
instead of insert
AS
begin
declare @name nvarchar (50),@city nvarchar (11),
@tdate datetime , @fid int,@fopd datetime;
select @...
0
votes
1answer
22 views
Getting ORA-00947: not enough values error while inserting date into table
I have created a table
create table Times (
departure date not null,
arrival date not null,
)
and I need to insert date and time in both columns
insert into Times(departure, arrival)
...
0
votes
0answers
7 views
Store Procedure to Copy One Column value of one table into another table row
I have two table tblDailyCheckIn and tblWeeklyTimeSheet. I need to copy working hours for every employee from tblDailyCheckIn and insert into Sunday, Monday, Tuesday, Wednesday etc of ...
0
votes
1answer
70 views
PostgreSQL: Disk usage and hardware utilization and performance
In my company we bought a new server
AMD EPYC 7262 - 8 core
64 GB RAM
Intel SSD DC P4510 (2 TB) - Dedicated only for the database
When I benchmarked disk, I got to speed up to 2GB/s for writing ...
0
votes
2answers
20 views
mysql - insert multiple values sharing same foreign key
I have the following tables:
create table countries (
country_id tinyint unsigned,
iso_alpha3_code varchar(4)
);
create table provinces (
province_id bigint unsigned,
province_name ...
0
votes
1answer
125 views
Unknown column in field list
Please help i dont know what i'm doing wrong.
CREATE DEFINER=`root`@`localhost` PROCEDURE `setState`(IN `statename` VARCHAR(100), IN `countryID` INT(11))
MODIFIES SQL DATA
BEGIN
SET @...
1
vote
2answers
264 views
Error Code: 1136. Column count doesn't match value count at row 1
I'm struggling here. This is my code
CREATE DEFINER=`****`@*****` PROCEDURE `setCountry`(
IN `countryname` VARCHAR(100) CHARSET utf8mb4,
IN `nationality` VARCHAR(100) CHARSET utf8mb4,
IN ...
