Questions tagged [insert]
A core statement in SQL that does what it says on the tin: inserts data into a table.
0
votes
1answer
11 views
“INSERT INTO res_partner () VALUES ()” failure (autoincrement in postgres)
This is DDL for the table:
CREATE TABLE public.res_partner (
id serial NOT NULL,
CONSTRAINT res_partner_pkey PRIMARY KEY (id)
);
This is SQL:
insert into public.res_partner () values ();
...
0
votes
1answer
19 views
Insertion join on two tables [closed]
I'm new to php and mysql, and I created a database where I inserted 02 tables:
role (# id_role, slug, name, level)
and
users (id_user (pk), name, first name, login, password, role_id (fk)).
I ...
1
vote
1answer
62 views
SQL function which returns true/false depending on success of insert query
I want to write a SQL function which returns true if a certain INSERT inserted a row and false if no row was inserted. In this use case, we know that at most one row can be inserted.
I came up with ...
16
votes
1answer
1k views
Single-row INSERT…SELECT much slower than separate SELECT
Given the following heap table with 400 rows numbered from 1 to 400:
DROP TABLE IF EXISTS dbo.N;
GO
SELECT
SV.number
INTO dbo.N
FROM master.dbo.spt_values AS SV
WHERE
SV.[type] = N'P'
...
17
votes
2answers
1k views
Disk space full during insert, what happens?
Today I discovered the harddrive which stores my databases was full. This has happened before, usually the cause is quite evident. Usually there is a bad query, which causes huge spills to tempdb ...
0
votes
0answers
18 views
How to insert into a MySQL table column, known its name as string?
I want to insert into a MySQL table (child table) column a certain value(int value). I know that the column name is:
'parent_data_ID'
But that column name is got from the following command:
SELECT ...
0
votes
3answers
52 views
MySQL insert into different databases tables based on PK value
Lets assume i have two databases, both having a table 'user_feedback', sharding data between the two databases. My goal is to insert all records where Primary key Id%2=0 goes to db1.user_feedback, and ...
0
votes
1answer
22 views
Pass parameter to trigger dynamically
I have a table on a mysql 5.7 db, containing say athletes with their mean, max, avg times in a specific sport.
I have another table that lists some calculated statistics based on those values.
I ...
0
votes
2answers
39 views
Delete data before INSERT
I have this table which is used to generate second table with the result:
INSERT INTO payment_transactions_daily_facts (count, volume, date, year, month, week, day, transaction_type, contract_id, ...
0
votes
2answers
33 views
How to insert into a table daily automatically in postgresql?
I'm trying to create a log of an inventory that runs daily
Basically on my log table i want to sum how many units have status1, how many units have status2, etc for every day
is there a way to fill ...
0
votes
1answer
32 views
Insert statement fails silently when a FK constaint can't be statically evaluated
I ran into an interesting issue which I'm not entirely sure how to address.
My tables:
CREATE TABLE entity (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
properties jsonb
);
CREATE ...
2
votes
1answer
151 views
Merge/Intersection between table and dataset - How to achieve?
Consider the following table:
Id Hash
----------- ----------------------------------
1 0x31F777F0804D301936411E3ECD760859
2 0xD64A593F3E9ACC972158D522A4289EA0
(Id is an ...
0
votes
0answers
29 views
There are some inserted records on galera cluster that does not exist!
We are using a galera cluster with 3 node and 2 database on it which they are related to each other by some applications.
The problem is, some records are inserted into table and galera generate id ...
0
votes
1answer
59 views
Very high (99%) insertion cost in estimated execution plan
I have four tables with 500,000 to 4,000,000 rows. The script joins them and insert into a temporary table with some where clause. It is taking long. The execution plan shows 99% cost only for the ...
0
votes
0answers
45 views
Occasional slow insertion speed because of FK trigger
I have two tables in master-child relationship (several children per master).
In one use-case a master with all of its children needs to be copied as new master. For this I'm using two select+insert ...
0
votes
1answer
296 views
Postgresql 10 There is no unique or exclusion constraint matching the ON CONFLICT specification
I currently have a table which looks like this:
CREATE TABLE "PDPC".collection
(
col_no bigint NOT NULL DEFAULT nextval('"PDPC".collection_col_no_seq'::regclass),
q1 character varying(10000) ...
6
votes
1answer
160 views
Error 666 on clustered primary key
I am receiving error 666:
the maximum system-generated unique value for a duplicate group was
exceeded.
I am experiencing this on an Azure SQL Managed Instance.
However, I shouldn't be getting ...
3
votes
0answers
19 views
On the INSERT statement in H2, what does `DIRECT` mean?
I noticed on the grammar documentation for INSERT in the H2 Database Engine, the optional word DIRECT. The documentation says:
When using DIRECT, then the results from the query are directly ...
0
votes
0answers
53 views
Slow insert on very large tables, (even in transactions)
I have a table with about 50 million rows, the table itself is fairly simple
CREATE TABLE Word (id integer PRIMARY KEY, word TEXT UNIQUE)
I then do an insert
INSERT OR IGNORE INTO Word (word) ...
0
votes
1answer
36 views
Map auto increment ids on multi-row insert
I have the following table with an AUTO_INCREMENT column.
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
Usually I load multiple ...
0
votes
1answer
37 views
Inserting into table depending on conditions [closed]
I'm trying to insert data into table but depending on the content it is supposed to be done different ways. My question is whether there exists such function that will work like follows:
WHEN ...
0
votes
2answers
240 views
INSERT AFTER trigger failing with multiple rows
I have a trigger (AFTER INSERT) that has been working in single inserts, and I believed it would work for multiples but its not. I researched it and am currently stuck as what I'm reading implies it ...
1
vote
2answers
98 views
Insert sometimes takes more than 2 seconds in table with +6 million records
I have a table in my database that acts like a log to record accesses to some selected endpoints of my application.
That table contains:
id (primary key)
The http method (GET, POST, PUT, etc)
The ...
0
votes
1answer
29 views
How to insert data in table with constraint on column? - in parallel queries
I have an Oracle database with a table with a unique field in it:
create table t(n number, CONSTRAINT n_unq UNIQUE (n));
Then from a session-1 someone is trying to insert some data:
insert into t(n)...
0
votes
1answer
46 views
What is the best way to check if a value in a table has changed when writing to a database?
Let me provide you with an example. Let's say I have an html_tag with two columns, tag_type and tag_value
For example, let's say I have a record with the value "link" under thetag_type field and "...
2
votes
2answers
76 views
Insert/update data into relational database tables only after administrator approval - how to approach it?
I'm building a relational database that holds 21 tables, which is connected to my website application through PHP.
The website has search functionality which allows users to search this database. ...
0
votes
1answer
20 views
Need some insights on 'index_page' variables in Innodb_Metrics Table
I was experimenting with page-split behaviour for sequencial vs random ordered inserts in Mysql,Innodb.
For sequencial Inserts I got:
index_page_splits: 3199
index_page_merge_attempts: 0
...
-2
votes
2answers
365 views
How to UPDATE along with INSERT INTO SELECT [closed]
I added some entries from table2 to table1 as
INSERT INTO table1 (title) SELECT title from table2 WHERE ...
how can I UPDATE table2 for the SELECTed or INSERTed entries as
UPDATE table2 SET status='...
0
votes
1answer
28 views
How to accomplish such insert-or-update strategy?
Programming stored procedures isn't my strongest point when it comes to programming, but I'm trying to get better.
I've stumbled upon a business requirement that's kind of hard for me and I was ...
0
votes
1answer
24 views
In which context does a procedure run when called from another context?
Envisage:
use databaseA;
go
exec databaseB.dbo.spProcedure();
go
Does the procedure execute against databaseA - the context from which it is called, or databaseB - the context in which it exists?
...
0
votes
1answer
28 views
Get result from select exists, execute insert query based on the response of it
I have to insert unique value for a row but has to put check on 3 columns collectively ((unique value(col1 && col2 && col3) == true)
I found a solution here it is
query = select ...
1
vote
1answer
265 views
Insert Trigger to a Linked Server error
I created an insert trigger in a table that will fire when an insert/update/delete is made. The trigger is supposed to send information about the query that fired it, but every time it fires, the ...
1
vote
1answer
58 views
How to perform multiple inserts into postgresql database in one query
I have an orders, recipient and items tables.
I want to insert data into each of these tables based on the response from the previous query.
insert into recipient(name, address) values(name, address)...
0
votes
1answer
37 views
INSERT INTO statement with select
I am trying to insert a new record into a table, but only if another record of the same table meets some conditions. Sadly it does not work I alwys get the following error: ERROR: duplicate key value ...
0
votes
2answers
56 views
ORACLE Table insertion
I want to insert data into below table, but I got an error. How do I solve this?
CREATE OR REPLACE TYPE TEST_TYP FORCE IS OBJECT ("id" VARCHAR(5000 NULL) NOT FINAL;
CREATE OR REPLACE TYPE TEST_TAB ...
0
votes
1answer
52 views
How to use the result of an insert as foreign key in another insert?
I have a temporary table with lots of data. I want to populate two persistent tables with excerpts of that data, preferably in one go.
Problem: after insert on tableA, I want to reuse the generated ...
0
votes
1answer
55 views
MySQL: Trying to “normalize” data using MySQL to help
I inherited a spreadsheet with 1600+ records, which I simply inserted into a single table (mapping) in MySQL.
I'd like to use the power of MySQL to help me do some "normalization", à la the MySQL ...
0
votes
1answer
571 views
How can you insert data into a ARRAY column on a Snowflake table using SQL?
I am having difficultly finding documentation on how to insert data into an ARRAY column type using SQL on a Snowflake table.
Snowflake Documentation: https://docs.snowflake.net/manuals/sql-reference/...
0
votes
2answers
24 views
How do I end function when X
so my function looks like this:
create or replace function insert_stuff(a1 text, a2 text, a3 int)
returns table (id_i int, id_j int) as
$$ begin return query
with
i1(id1) as select insert_here(a1,a2)...
2
votes
1answer
375 views
Error while inserting into array of timestamps
Im using postgres 10.1 and the Datestyle config is the following
DateStyle
-----------
ISO, MDY
Im having this error
ERROR: date/time field value out of range: "1535673858" Hint: Perhaps
you ...
5
votes
2answers
3k views
Inserts in MySQL 8 are slower than Inserts in MySQL 5.7
I've started to use MySQL 8 (8.0.12), and I'm realizing that the "inserts" are slower in this version than MySQL 5.6 or MySQL 5.7. It doesn't matter if the table is MyISAM or InnoDB, with just one "...
0
votes
2answers
186 views
MySQL 5.1 - long running select locking the table and not allowing inserts
Platform - MySQL 5.1, MyISAM engine
We reached max on int (id, auto increment) in our Prod system. Changed the type to BigInt. Even since this change was made, long running selects are locking the ...
2
votes
0answers
106 views
SQL Server: INSERT-SELECT Statement occasionally fails silently and works upon re-run
We have a batch import process that BULK INSERTs raw data into staging tables, does some data mapping, and then pushes it to another database for validation.
The procedure where my issue occurs is ...
3
votes
0answers
63 views
Where is Newid(), Rand(), and Sysutcdatetime() new value per query vs. row behavior documented? [duplicate]
Within a single query, all current time functions like sysutcdatetime() return a single point in time that is re-used throughout the query for each instance (e.g. of sysutcdatetime()) no matter how ...
0
votes
3answers
740 views
Insert Values Into Table, Putting Duplicates In Another Table
So I posted this question yesterday. Some of the responses I got were helpful, however it seems my issue is a bit more complex than I originally thought.
After doing some looking the reason I was ...
1
vote
2answers
611 views
Insert into Table ignoring duplicate values
I am having a brain fart on figuring this out. I have the following two Tables:
Table: parts
part_id INT IDENTITY(1,1) NOT NULL,
part_number VARCHAR(50) UNIQUE NOT NULL,
...
1
vote
1answer
73 views
Innodb updates records with delay
I have two tables in my database like these:
CREATE TABLE `main` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` int(11) NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB ...
0
votes
1answer
26 views
unable to add data to table because related record is required
I have an access database with my work that I am trying to insert into but I keep getting.
'You cannot add or change a record because a related record is required in table 'Projects'.'
I'm running ...
1
vote
1answer
39 views
What data is duplicated when MySQL/MariaDB BLOB columns are copied?
Let table_1 be created as follows:
CREATE TABLE table_1 (
id INT AUTO_INCREMENT PRIMARY KEY,
some_blob BLOB
);
Let table_2 be created as follows:
CREATE TABLE table_2 (
id INT ...
0
votes
1answer
1k views
Insert into DB2 table a result from temp table
Is there a way to insert a temp table results to DB2 table, for example:
WITH TEMP1 AS (
SELECT CUSTOMER_ID
FROM T_CUSTOMER WHERE CUSTOMER_ID BETWEEN 100 TO 300
),
TEMP2 AS (
SELECT CUTOMER_ID, ...
