Questions tagged [insert]
A core statement in SQL that does what it says on the tin: inserts data into a table.
822
questions
0
votes
1
answer
367
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 ...
1
vote
1
answer
2k
views
Insert multiple rows into a table with id from other table if not exists insert to other table
I have done similar task where I can insert a row into a table if data doesn't exists:
WITH
user_exists AS (
Select id from users where username='%s'
),
user_new ...
0
votes
1
answer
529
views
SELECT Every Parent Table Record and INSET Multiple Record in Child Table Against the Parent_Id
Suppose I have two tables name company and screensavers as given below:
**Company:**
id (pk auto inc)
name (varchar)
**ScereenSavers:**
id (pk auto inc)
name (varchar)
path (varchar)
company_id (int)...
0
votes
2
answers
22
views
(PostgreSQL) Insert a tuple containing specific value in a column linked to a sequence
I started writing this post as a question but, while reproducing the error, I found the solution in the PostgreSQL documentation. So I'll share the solution here for the community.
A PostgreSQL 14 ...
0
votes
0
answers
31
views
How to get last executed statement on a particular table in Oracle?
Trying to identify what ghost process is inserting records into a particular table. Is there a way to get the exact query?
1
vote
1
answer
2k
views
MySQL events with TRUNCATE and INSERT
Can I create event that would first TRUNCATE table and then make INSERT?
I know how to TRUNCATE and INSERT in separate events but need to do it immediately one after the other. I just don't know the ...
0
votes
1
answer
4k
views
how mongo db does locking for updates/inserts?
I have many data for each user that with write heavy and many for each separate users that there is not any relation between user's data; so there is two options for me to store them:
1. keep all user'...
-2
votes
2
answers
56
views
Replace "INSERT" by "UPDATE"
I'm working with some tables in an SQL-Server database, and I'm doing some crucial modifications. In order not to loose everything when I mess up, I decided to do a "Generate script" (data ...
0
votes
1
answer
379
views
Postgresql: Trigger is not working at times
I have a PostgreSQL trigger that is not firing sometimes, even though the status is always shown as "enabled".
My trigger code is as follows:
CREATE OR REPLACE FUNCTION audit_src_exhibit() ...
1
vote
1
answer
34
views
Moving records between tables in CockroachDB is taking too long
I have a requirement where I have to move records from table original_table to table new_table for the last N days.
Below is a small sample of my basic script that does this.
while True:
result = ...
0
votes
1
answer
749
views
Lock Escalation happening while inserting few million records into a table in production
I want to insert few million records into a table in my production database.
I am not updating any existing rows.I am just doing
INSERT INTO TAB1
SELECT
COL1,COL2,COL3
FROM TAB2
This table has an ...
2
votes
1
answer
2k
views
mySQL trigger to prevent insert won't work
The problem has been discussed before but the answers won't work in my case.
I have a table in my database which contains a numeric column called "value".
I would like to prevent an insert if the ...
1
vote
1
answer
3k
views
How to insert multiple values and handle conflict at the same time
I have a table where the primary key is a combination of two columns.
When inserting multiple values at a time (batching) conflicts might happen since in the
list of values passed there might be ...
0
votes
2
answers
868
views
Insert records from one table to other with default serial
I have two tables with same structure, first column gid as serial, and many other columns after that. I want to insert selected rows from one table to the other. Without serial it is really easy:
...
0
votes
0
answers
34
views
How to insert data into temporary table from the table having encrypted columns in sql server
I am trying to insert data into a temporary table(#table1) from the table(table2) having encrypted column in it.
I am getting below error while trying to insert data into temporary table from the ...
0
votes
1
answer
135
views
question about using Insert where not exists instead of if not exists then
I have a procedure that insert data to table where the data doesn't exists.
right now it's done in two steps :
check if the data exists already,
if not, insert it
i want to change it to do INSERT ...
0
votes
1
answer
1k
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 ...
0
votes
1
answer
418
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
3k
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
332
views
How to efficently handle large amounts of insert statements
I have a file containing a large amount of insert statements roughly 30 GB worth of them. The insert statements themselves are rather simple and contain 5 different values. I now have the requirement ...
0
votes
1
answer
848
views
SET BIGINT fieldname value to 0 automatically if inserting empty string
How do I do something like :
INSERT INTO tablename SET customer_id = '' WHERE <condition>;
customer_id is BIGINT NULL Default NULL
But when I execute SET customer_id = '' it gives
Incorrect ...
0
votes
1
answer
656
views
Failed to insert records into table in different server using sql server agent
My idea is to insert records from a table into another one in different server using T-SQL.
I've made maintenance plan along with its job and found below error.
Executed as user: NT SERVICE\...
0
votes
2
answers
90
views
SQL Server inserts fails silently
I have a stored procedure that inserts some rows into a table. Sometimes it seems to fail (can't find the new records) although I know the statements did execute. The code I use is bellow. After ...
0
votes
1
answer
359
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 ...
0
votes
1
answer
48
views
Move row of a table to an inherited table in PostgreSQL
I have tables created as such:
BEGIN;
CREATE TABLE tag (
id SERIAL,
name VARCHAR(255) UNIQUE NOT NULL,
description VARCHAR(511) NOT NULL DEFAULT '',
PRIMARY KEY(id)
)...
1
vote
2
answers
31k
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
2
answers
3k
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 ...
5
votes
1
answer
1k
views
Persistent inserts in a UDF even if the function aborts
I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an ...
8
votes
3
answers
42k
views
INSERT... ON DUPLICATE KEY UPDATE not working as I expect
I have a table called "Example"
CREATE TABLE IF NOT EXISTS `example` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
PRIMARY KEY (`...
2
votes
2
answers
15k
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
0
answers
46
views
Mariadb Triggers after multiple insert, update one table one one time
CREATE TRIGGER after_composers_insert AFTER INSERT ON composers
FOR EACH ROW UPDATE listings SET composer_name = NEW.composer_name
WHERE composer_id = NEW.composer_id;
CREATE TRIGGER ...
1
vote
1
answer
1k
views
MySQL 5.6 Bug Amazon RDS with replica. AFTER INSERT TRIGGER is not working. Solutions?
I have MasterDB and ReplicaDB in Amazon RDS environment. I need to move 1 table (MYTable) from replica to other external DB (ExternalDB).
My solution (do not work) was to add a AFTER TRIGGER ...
0
votes
1
answer
180
views
Auto-increment [id] field in a table in an SQL-Server database
I have different tables, containing an id field. That field is typically defined as id (PK, int, not null).
Normally, when adding an entry to a table, I add the value of the id field myself, but I'm ...
0
votes
1
answer
285
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 ...
0
votes
1
answer
35
views
Is commit of first insert necessary if second insert has constraint depedent on first insert?
Using MariaDB I am running an insert on one table and then trying to run another insert on second table. Due to a constraint the second insert should only succeed if the first one was successful.
If I ...
-1
votes
2
answers
64
views
SQL breaking my query where there is NULL value
Here is my sql statement and the error I am facing:
SQL> INSERT INTO COUNTRY(COUNTRY_CODE)
select unique(country_code) from AIRPORTDEMODATA where country_code IS NOT NULL;
2 INSERT INTO COUNTRY(...
0
votes
1
answer
116
views
In PostgreSQL, what is the `index_predicate` for in `INSERT`?
The docs on INSERT say:
If an index_predicate is specified, it must, as a further requirement for inference, satisfy arbiter indexes. Note that this means a non-partial unique index (a unique index ...
1
vote
1
answer
141
views
Lazily Evaluate Insert Arguments If Constraint Violated
create table a (
b text not null unique,
c text not null
);
-- Sleeps for 1 second as expected.
insert into a (b, c) values ('d', pg_sleep(1));
-- Sleeps for 1 second even though record is ...
0
votes
1
answer
27
views
Delete records from table that are not in the file being inserted
I have a Snaplogic pipeline that queries a web application every night and loads the data into a SQL Server table. I use the 'Update' snap to add new records or add any changes. The goal is that the ...
1
vote
1
answer
319
views
How to handle deadlocks while multiple batch inserts?
I need suggestions/ideas of handling a issue with deadlocks.
I'm having multiple batch inserts commands to same table:
insert into ip (id, p_id, m_type, m_id, sha256, path) VALUES ($1, $2, $3, $4, $5, ...
1
vote
1
answer
72
views
insert trigger to auto-set key fields for composite primary key
Basically the next step for Error "UNIQUE constraint failed" with composite primary key:
I created a trigger after insert to set SEQ to the highest SEQ plus one if it's less than one.
...
1
vote
1
answer
145
views
SQL Server stored proc locking, serialization
Stored proc in SQL Server INSERT INTO table1 value sets A , B
Value set A has as one of its parameters: (SELECT TOP 1 cashbalance - 1 FROM table1 WHERE userid = __ ORDER BY createdUTC, ID)
Value set B ...
1
vote
0
answers
135
views
Copy rows and change corresponding values
I have 4 tables
Tables are used to store product attributes and its values in different languages
I'm trying to copy attribute (copy in same table to new row and increment/change some values).
First ...
0
votes
1
answer
198
views
What is the most straightforward way to create subtypes and supertypes at the same time?
I hope this isn't too basic a question, but I am hoping that the DB engineers here will help me out. Do relational DBs usually have a straightforward way of doing the following?:
Create a supertable ...
3
votes
4
answers
30k
views
Insert values from a record variable into a table
I am developing a user-defined function that takes two arguments:
create or replace function gesio(
events_table_in regclass,
events_table_out regclass)
returns void as $$ ... $$
...
0
votes
1
answer
198
views
MSSQL - Insert Into Select number from global count table and update the global count for each row then inserted
Not sure I've articulated that well in the title, so here goes...
I have three tables
Table1
ID
NewNames
10
some row data
11
some more row data
Table2
ID
OldNames
1
my old data
2
and more data
...
0
votes
1
answer
42
views
Compositing ordered SQL tables without Joins
I'm currently exploring some weird ways of doing things and was wondering if anyone has any experience with the following methodology.
Background
I have a handful of NF3 tables, and NF1 source data. ...
9
votes
2
answers
60k
views
Sql server table insert performance optimization
Setting
In a datawarehouse, I am joining a fact table to 20 dimensions. The fact table has 32 million rows and 30 columns. This is a temporary staging table so I don't have to deal with other users ...
3
votes
1
answer
13k
views
How to ignore duplicates during bulk inserts?
In Postgres 9.3.5, I'm importing records from an external source where duplicates are VERY rare, but they do happen. Given a readings table with a unique compound key on (real_time_device_id, ...
0
votes
1
answer
85
views
Migrate columns from table A to table B, and update A with a reference to B
I have a table A with some columns that need to be migrated to a new table B. But I also need to create a column in table A to reference the id's from the new columns in the table B. I've tried doing ...