Questions tagged [insert]
A core statement in SQL that does what it says on the tin: inserts data into a table.
645
questions
0
votes
2answers
34 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
9 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
20 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
19 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
28 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
55 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
16 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 ...
-1
votes
0answers
11 views
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values … in sql server [duplicate]
in sql server
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values
how can i make unlimited allowed number
0
votes
1answer
29 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
42 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 ...
0
votes
0answers
8 views
change db column default from NULL to <null>
When inserting rows in table, a default value is a NULL. Is their a way to make
"<null>"
such that,
+--------------+-------------+------+-----+-------------------+----------------+
| Field ...
1
vote
1answer
21 views
How to check other table for value during insert
In PostgreSQL is it possible to check another table's data to see if there is data that fits a certain condition during an INSERT statement?
I have attempted to use WHERE NOT EXISTS as I want to not ...
0
votes
1answer
36 views
Why doesn't my trigger put data into my table?
When I execute my trigger, it says it executed commands, but there is still no data in the tAttackPlan where it is supposed to insert data into. Can not figure out why.
SET ANSI_NULLS ON
GO
SET ...
0
votes
0answers
19 views
Generate integer on SQL insert with unique constraint to value in another table
I have two tables:
Assuming a user already exists, when I insert a vehicle I want the unique_number field to auto-populate with a value between 00000-99999. It only has to be unique based on the ...
2
votes
1answer
69 views
SQL Server-Table has many AFTER INSERT triggers that insert into the table itself
I purposely created this example to represent my problem.
I created table like this:
CREATE TABLE a
(
id INT
)
I then created 2 AFTER TRIGGER like this:
CREATE TRIGGER insert_a
ON a AFTER INSERT
...
1
vote
2answers
17 views
Insert to a table using function with default arguments
I'm trying to write a function that makes an insert to the table defined below:
DROP TABLE IF EXISTS pl_table;
CREATE TABLE pl_table
(
id SERIAL PRIMARY KEY,
mandatory VARCHAR NOT ...
0
votes
1answer
27 views
Trying to understand how to use postgresql generate_series() for this operation
First of all, im coming from this question, where I was recommended to use generate_series() , but after some adjustments in my db, I dont really understand how to create such sentence
This is the old ...
2
votes
1answer
36 views
ROLLBACK make data in INSERTED table is removed in AFTER INSERT TRIGGER
I give an example to show my problem. I create a table as the following:
CREATE TABLE a
(
id INT
)
I create a AFTER INSERT trigger to don't allow to insert id=1 into table "a"
CREATE TRIGGER [dbo]...
0
votes
2answers
30 views
How to catch all the errors happening during the execution of an SQL file containing multiple INSERT INTO statements to insert multiple rows?
I've got a sql file TW_popul.sql containing multiple INSERT statement:
INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
VALUES (32353357, 8.67, 0, 'com....
0
votes
1answer
24 views
Populate a table that should have an X amount of rows with default values with a big insert query? Or can it done somehow by default?
I have a table lets say calendar
Table Calendar --> idCal (int) , numberOfFields (int)
And each Calendar has asigned a number of fields
Table Field --> idField(int), textField(text), idCal (...
0
votes
1answer
15 views
MySQL trigger: if record exists just update
There is a MySQL database with the following table reviews(reviewer, product, score, title, comment, creation_date, last_modified).
It is needed a trigger before insertion to check if the user has ...
5
votes
1answer
137 views
Include non-inserted rows as result of INSERT INTO … RETURNING
I'm using PostgreSQL 11.
I want to conditionally insert values into a table while having the result of the insertion include a null for each row of the input which did not result in an insertion.
For ...
0
votes
0answers
22 views
Filling a database (from a temporary database) with a Many to Many relation
I have a temporary table that I fill in using a CSV file.
CREATE TEMP TABLE temp_products(
product_marketplace_name varchar,
product_link varchar,
product_name varchar,
...
0
votes
0answers
18 views
Problems Add/Update Big Data on PostgresSQL
I want to add large amounts of data to the table.
Before adding, I check whether the data exists in the table or not.
I am dealing with the following:
Example:
Table users
id | name | address
.....
0
votes
1answer
44 views
SQL Server column type = Date not accepting dates as varchar in insert statemant
In T-SQL, I am trying to insert values in a date table, US & French dates. When table is created with columns as DATE, the insert does not work, it errors.
When I switch to VARCHAR(12) instead of ...
-1
votes
1answer
76 views
PL/SQL returning Procedure value and putting it in an insert clause [closed]
I'm doing an assignment and the last question states that I call the procedure from question 2 and function from question 3 and then put them in an insert clause(question 4).
Write a PL/SQL Code ...
1
vote
1answer
45 views
If I kill a looping INSERT query, will it rollback all inserts since the loop started or only the last iteration?
Do all inserts or only the current one rollback if the following loop query was killed in the middle of looping?
DECLARE @LoopId INT = 1;
WHILE (@LoopId < 100)
BEGIN
INSERT INTO Table2
...
1
vote
1answer
42 views
Uniquely Append to Array on Conflict PostgreSQL
I've got two columns in PostgreSQL, hostname and ip. Hostname is the primary key and ip is an array of IPs.
I want to be able to insert IPs for a give hostname, on conflict I want to append to the ...
0
votes
2answers
51 views
How to speed up an insertion from a huge table with postgres?
I have 5 tables in my database with respectively a size of 70Gb, 500Mb, 400 Mb, 110Mb and 20 Mb.
I want to create a new table that contains all columns of all tables, so I tried 2 queries, the first ...
2
votes
2answers
130 views
Can I use OUTPUT from an UPDATE in an INSERT?
I want to add a record to my app's "SystemSettings" table and set the PK value using the value from an UPDATE. The PK value comes from the "TS_LASTIDS" table which contains the maximum PK value for ...
0
votes
2answers
27 views
How do I write a query that inserts values while AND gets values from another table?
I have a table (Tbl2) that needs to be populated with values from another table (Tbl1).
Tbl1 has columns: id (uuid), name, address, gender.
Tbl2 has columns: id (uuid), org, name, address, gender, ...
5
votes
2answers
1k views
Table size grows indefinitely after deleting and then inserting the same data
I create a table and populate it with data:
create table tab
as
select 'value' || level val
from dual
connect by level <= 10000;
If I then delete all rows and insert the same data:
delete from ...
0
votes
2answers
92 views
What happens if you lose the connection during a huge insert?
What happens when you lose your connection with the DB during a huge insert. Will part of the data be inserted? Will nothing be inserted? What is the reason for this?
The example statement I am ...
0
votes
0answers
22 views
Insert select - using selected value in ON DUPLICATE KEY UPDATE
In the following query, oversimplified from my actual scenario but close enough:
INSERT INTO foo(bar_id, baz)
SELECT
id,
5 AS new_baz
FROM
bar
ON DUPLICATE KEY UPDATE baz=new_baz
I get ...
0
votes
1answer
38 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
1answer
38 views
Can a trigger with an “insert into” statement cause Intext Exclusive locks for small insert operations?
We have a trigger that runs AFTER an insert in a table (Nodes) that creates entries in another table (NodeClosures) that selects entries (6 on average) from the NodeClosures table and inserts them as ...
0
votes
1answer
310 views
INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in single query
I've come across people recommending using ON DUPLICATE KEY UPDATE to UPDATE multiple rows using a single query. Unfortunately no one seems to be using clean SQL (defining an obvious primary key of id ...
0
votes
0answers
8 views
OrientDB: rounding of numerical values
I am wondering if there is a way to prevent OrientDB console/studio from rounding the numeric values ?
I ran a small test using OrientDB 3.0.27
CREATE CLASS test EXTENDS V
INSERT INTO test SET value=...
0
votes
0answers
32 views
Multiple sub query in insert statement for postgreSQL
I am trying to insert into table with multiple queries. And its not working, here is my work.
insert into post_topics_ta (id,post_id,topic_id,confidence_score)
SELECT * FROM post_topics WHERE ...
0
votes
2answers
22 views
Count violated unique constraint caused by INSERT statement
I would like to add rows from table2 into table1 and see how many conflicts of the type "duplicate key value violates unique constraint" result from this in PostgreSQL. pkey is a primary key, so there ...
1
vote
2answers
58 views
Optimize INSERT performance with a UNIQUE constraint on md5(JSONB_COLUMN::text)
I have the following table and indices defined:
CREATE TABLE IF NOT EXISTS data (
id serial,
job_id bigint NOT NULL,
payload jsonb NOT NULL,
tags jsonb NULL
);
CREATE UNIQUE INDEX IF ...
0
votes
1answer
27 views
Ge the value of id in master table and supply it to other tables
I have 5 tables, of which one is master table and other 4 are the tables with have the foreign key of the id of the master table
I have to create a procedure which INSERT's data in all the tables ...
0
votes
0answers
64 views
Postgres. INSERT INTO table in stored procedure with array of composite type objects as procedure arguments
I have two composite types:
CREATE TYPE truck.truck_event_type AS
(
event_global_key VARCHAR(100),
event_type VARCHAR(50),
datetime_utc TIMESTAMP(4),
...
4
votes
1answer
258 views
Insert multiple rows into a table with only an IDENTITY column [duplicate]
I have a table called dbo.Groups defined like this:
CREATE TABLE dbo.Groups
(
GroupID int NOT NULL IDENTITY (1,1) PRIMARY KEY
);
The table really consists of just the one IDENTITY column.
...
-2
votes
1answer
98 views
Creating new table with multiple values in a column [closed]
I have created a table with only one column
Create Table RLID_MARK
(
RLID Int )
I want to Add multiple values in Column RLID using
insert into RLID_MARK (RLID)
values( '43230335' , '20936450' , ...
0
votes
2answers
104 views
How can I insert a record into a table that depends on a foreign key linking to a previous table?
Following on from this question. I have a dictionary file written in XML. I've written a program in VB.NET to parse this file and store all the data in Class objects, and now I want to write a ...
4
votes
2answers
2k views
What principles should I follow when entering data into a complex relational database?
I'm new to Databases, so forgive me if this is a basic question.
I'm attempting to parse a complex open source dictionary, and output the data into an Access database, which is equally complex:
I ...
0
votes
1answer
30 views
BEFORE INSERT validation trigger for GPS coordinates
I have a MySQL database table that contains zipcodes and their corresponding lat/lng values.
I have another table that contains business locations (including lat/lng)
Prior to inserting new records ...
