5,905 questions
2
votes
2
answers
121
views
Pros and cons of `INSERT INTO` versus `UNION ALL`
I am working with an Oracle database in which each year's data is stored in different tables. I am not the DBA so I cannot change that. Also, I do not have the permission to consult execution plans or ...
- 553
0
votes
1
answer
117
views
Snowflake: SQL equivalent of INSERT INTO table BY NAME
The goal is to simulate SQL INSERT BY NAME behavior in Snowflake
Using the BY NAME modifier, the names of the column list of the SELECT statement are matched against the column names of the table to ...
- 181k
-4
votes
1
answer
97
views
How do I retrieve the primary key of the updated record?
I have this insert query for my MariaDB 10.6.19 database:
CREATE TABLE manufacturers (
manufacturers_id int(11) NOT NULL AUTO_INCREMENT key,
manufacturers_name varchar(32) unique NOT NULL,
...
- 433
0
votes
0
answers
88
views
If a record already exists, update current record and insert new record [duplicate]
If a value already exists in a table, how do I update the current record and add the new record as a new row?
This is the current upsert query:
UPDATE d
SET d.[AccountId] = S.[Account_Number],
...
- 43
2
votes
2
answers
108
views
Proper way to locate the position of a single newly inserted row based on order by clause immediately after insert?
After inserting a single row of data into a table (always a single row), I'd like to return to the application code the relative position of that row based on an order by clause. Thus, the application ...
- 3,226
-1
votes
2
answers
166
views
Insert or Update - Understanding difference between SQL statements [duplicate]
We have a database utility in our product suite that handles both the creation of new databases and the upgrade of existing ones.
For new tenants or customers, the creation flow is executed.
For ...
- 2,595
1
vote
0
answers
64
views
Extremely slow DB insert using Turbodbc
I have built Turbodbc 5.1.2 from source with simdutf 7.3.0, Python 3.11. When trying to insert 150,000 rows of 46 columns to a MySQL 8.0 InnoDB table, Turbodb takes about 190s, compared to 15s with my ...
- 684
-1
votes
1
answer
77
views
SQL insert from select changing two columns [closed]
I'm not a SQL expert. I'm trying to make a insert from a select, but I would like to change two columns using the data from a temporary table.
I'm trying to reuse the data from an existing register ...
- 7
1
vote
1
answer
132
views
Apache Doris INSERT INTO ... SELECT ... query is unexpectedly slow - how to troubleshoot?
I am experiencing performance issues with an
INSERT INTO target_table
SELECT ...
FROM source_table
[WHERE ...]
[GROUP BY ...]
query in my Apache Doris cluster (version 2.0.8).
The SELECT part of the ...
- 95
0
votes
2
answers
164
views
Prevent Violation of Unique Index with Multiple Concurrent Inserts
I'm using a Postgres 9.5 database with a front of Spring Boot.
On the database end I have an event_attendee table that users can insert attendees into. In the table there is a unique index on ...
- 4,028
-3
votes
1
answer
54
views
error 1046 in subquery inserting data from specific columns to another table [closed]
I am trying to copy a few fields of video information from one MariaDB table to another using a subquery, decoupling some video info from a classified ads table for later refactoring.
video:
id (int ...
0
votes
1
answer
88
views
I am struggling with parameters in VB.net when building the Insert Into SQL statement
The parameters within the Using block don't seem to align. My suspicion is the primary key is at fault, but I am using Scope_Identity.
If String.IsNullOrEmpty(InvoiceID_TextBox.Text) Then
Using ...
-4
votes
1
answer
66
views
INSERT multiple rows from SELECT returns error "This is not permitted" [closed]
I have two tables of same structure and want to insert rows from tableA into tableB.
The following example works fine:
INSERT INTO tableA (uniqueColA, colB, colC)
SELECT uniqueColA, colB, colC ...
- 3,008
0
votes
1
answer
98
views
Convert JavaScript Array to SQL Multirow Values List
Trying to load a JavaScript array into a SQL Server table. I convert the array to a string, however, its format does not immediately translate into something (I know of) to use as separate records to ...
- 1,336
-1
votes
2
answers
103
views
Sqlite3, how to make an INSERT statement inside a SELECT query?
I want to do, in exactly one sqlite3 query, an operation that :
Checks for the existence of a value in my table (call it V), written in a row indexed by A
If V exists and is equal to V_Param (...
- 9
0
votes
1
answer
100
views
Mysql rowcount always returns 1 on INSERT IGNORE statement
I am using pymysql connector to do some inserts into my database. I am trying to return whether or not a record was added or updated.
My code is
import pymysql
db = pymysql.connect(host='127.0.0.1',...
- 8,816
-1
votes
1
answer
43
views
INSERT INTO after WITH statement in MySQL [duplicate]
I have no idea why I can't use an INSERT INTO combined with an WITH statement:
drop table if exists testdb.with_insert_into;
create table testdb.with_insert_into (id int);
with test_data as
(select 3 ...
- 4,346
-1
votes
1
answer
69
views
How can I append number of datasets together in SQL commander?
I have 50 datasets. Each dataset has different records of 3 variables each. I want to compile one dataset that all records from 50 datasets for those 3 variables.
Example is
Table 1:
var1
var2
var3
...
- 21
-1
votes
2
answers
149
views
Why does my code give a syntax error on insert?
For my Delphi app with an SQLite database I am using TFDQuery, TFDConnection, TDataSource and TDBGrid components. All fields except AllKeys are VARCHAR. AllKeys is TEXT. Everything is connected ...
0
votes
0
answers
159
views
Inserting into a glue table via Athena with large number of partitions
I am trying to refactor/optimize a part of a large architecture. The part I need some advice is how to circumvent the Athena INSERT INTO 100 partitions limit.
I have a table, daily_aggregate with ...
- 162
0
votes
1
answer
81
views
Rows inserted in Oracle procedure not available until after the procedure completes
I have a recursive procedure in a package in Oracle that traverses through a network. The recursive function runs down the network from the top and then returns values back up as the recursions close. ...
1
vote
1
answer
108
views
SQL Server Deadlock Delete Insert Statement
Both a delete and insert (range) on the same table are causing a deadlock due to contention on the same primary key index. I don't understand why.
The locks taken are RangeS-S by each process and the ...
- 65
0
votes
0
answers
101
views
Airflow Error: Issues Inserting Data into Database Due to Transaction Abortion
I'm using Apache Airflow to automate the process of loading data into a PostgreSQL database. My workflow consists of multiple tasks where I fetch customer and order data via API, insert or update ...
0
votes
2
answers
86
views
Avoid file sharing lock in MS Access during bulk insert
File sharing lock count exceeded // python import to access table.
If anyone can assist with this you’ll be a life saver. I have a script in python that is attempting to automate a manual process in ...
0
votes
2
answers
169
views
SQL Oracle adding new rows to CTE
I'm trying to add addition rows to a CTE table using Oracle SQL and I can't quite get it to work. I've read up and it appears using INSERT INTO () is my best option, but I feel I am missing something ...
0
votes
0
answers
40
views
Improving the Insert command with a Sequence in Oracle DB [duplicate]
I`m trying to insert multiple lines on a table. Some attributes are being provided in the INSERT, one of them is a client code, provided by a Sequence. How to execute this in one batch. I had to go ...
- 1
0
votes
0
answers
55
views
Why is ON CONFLICT DO UPDATE not working with PostgreSQL column aliasing?
I'm trying to perform an INSERT operation with an ON CONFLICT DO UPDATE clause in PostgreSQL. My table contains column names with aliases (e.g., "ITEM_CODE(material_code)"), and I want to ...
0
votes
0
answers
144
views
DB2 SQL inserts are very slow when table has a primary key
I have a DB2 table (map_card_number) that takes 5 minutes to insert 1M rows of data when it has a primary key, compared to 11 seconds to insert the same 1M rows of data when the primary key is removed....
- 51
0
votes
1
answer
73
views
ORA-00001: uniqueness constraint violated
I have this procedure:
declare
datafix_id$c varchar2(100) := 'BSS-39038';
datafix_version$c varchar2(100) := '1.0';
description$c varchar(100) := 'BSS-39038 [CBM] Добавить ERT_SERVICE_ID на ...
- 15
0
votes
1
answer
120
views
Symfony5 : Cannot bind value with a raw sql insert query
I must use raw sql queries in my Symfony 5 application in order to improve the performance of some operations. But I cannot manage to bind my parameters to the query althoug it does not throw any ...
- 1
1
vote
1
answer
81
views
Oracle SQL Error ORA-01756 when inserting a string pattern in Jenkins, but works in IntelliJ IDEA Database Tool
I'm trying to insert a pattern string into the PATTERN_SETTING table in Oracle using the following SQL:
INSERT INTO PATTERN_SETTING (ID, NAME, PATTERN)
VALUES (30, 'Alphanumeric and symbol', '^[a-zA-...
- 21
1
vote
2
answers
109
views
SQL Insert not aggregating the same as Select statement [closed]
I have an SQL Insert statement that takes data from various other tables and out joins. The query is run daily and populates from these staging tables.
I have an issue in that where field WSL_TNA_CNT ...
0
votes
0
answers
77
views
Postgres guard column function Insert statements
I am making columns immutable using BEFORE triggers and my current function works fine to protect columns from UPDATE, but I am having some trouble with INSERT.
Basically the problem is that my ...
3
votes
1
answer
185
views
Postgres Permissions violated by before-insert trigger function
I've got a table with row level permissions enabled. I've got an insert policy for my user, and I've granted permissions for them on specific columns. I added a new column to track the id of whoever ...
- 33
4
votes
2
answers
135
views
Create rows of data into table that do not have a specific column value
I have a table that has missing information that needs to be inserted with very specific conditions. However, I am unsure on how to proceed.
I have one table that contains an overall view of ...
- 117
0
votes
2
answers
79
views
Insert a column that depends from the previous row already inserted
I have a table in PL/SQL called TABLE_1 with five columns like this:
I want to create a new table TABLE_2, which has the same data but adding a new column c5 like the following:
The column c5 has ...
- 15
0
votes
1
answer
118
views
INSERT INTO SQL Server table errors: The supplied value is not a valid instance of data type float [duplicate]
I have a Pandas df I created from reading an email attachment. When inserting into a SQL Server table I see the following error for Parameter 6 which is Call Length column:
Parameter 6 (""):...
- 85
1
vote
3
answers
129
views
Insert sql but all rows have the same id (no unique constraint)
I'm trying to make an insert where the first row is the default item and the other rows are the variants with different language (making a primary key with id and locale) and I was thinking on ...
- 188
0
votes
1
answer
50
views
Spark InsertInto Slow Renaming in AWS S3
When using insertInto in AWS s3, the data is initially written to the .spark-staging folder and then moved (fake renaming) to the actual location in batches of 10 files at a time. Moving staging data ...
- 31
0
votes
1
answer
79
views
How to Use a Single INSERT INTO Statement for Multiple Rows in Python?
I’m currently working on a Discord Python bot where I loop through a list of ForumTags and generate an INSERT INTO SQL statement for each object to insert data into a MySQL database.
However, I want ...
- 891
0
votes
0
answers
73
views
Powershell array of strings into single SQL column
I've this array of strings:
$qLastSS_T
2024-07-08 11:56:32
2024-08-08 12:56:32
2024-09-08 13:56:32
2024-10-08 13:56:32
2024-11-08 15:56:32
2024-12-08 16:56:32
and a SQL table like this:
Is it ...
- 113
0
votes
1
answer
164
views
"ambiguous column name" error from AFTER INSERT trigger
In DBeaver version 23.3.4.202402060628 I have the following SQLite3 code (for a high speed injection moulded part and package tracking machine in a factory):
-------------------------------------------...
- 1,816
0
votes
1
answer
52
views
SQL - insert rows preserving overall number of duplicate values
I have a table, say, Product (Id, Name):
Id
Name
1
'one'
2
'two'
3
'three'
I have a new version of data that is needed to update the table, with possible duplicate values.
For example:
Name
'one'
'two'...
-1
votes
1
answer
57
views
I am trying to figure out why my INSERT INTO code does not accept my Function(rental_string)
The function alone seems to be working. pgAdmin gave me the thumbs up on it, as well as the table. I tried many different ways to type the function and INSERT INTO portion. I have been working on this ...
-1
votes
1
answer
89
views
Insert with values from existing table
I need to move some values based on expiration date, but unfortunately because the tables are not one to one in number of records I receive an error message when my query is run. I need SQL to be able ...
- 13
0
votes
1
answer
89
views
Why is Teradata so slow to INSERT decimals and CLOBs with .NET?
I have code that's trying to load data in batch through the Teradata .Net provider. I'm using the TdDataAdapter approach as follows:
using (TdDataAdapter adapter = new TdDataAdapter())
{
...
- 11
1
vote
0
answers
39
views
Does it make a difference if a CTE comes before or after an insert?
insert statements in postgres can use CTEs ("with" expressions). These can come either before or after the insert itself. Is there any difference between one style vs the other?
For example, ...
- 1,147
0
votes
1
answer
86
views
INSERT INTO SELECT subquery that uses a udf so that it returns 1 value
I'm attempting to create a new table but altering some of the data from my old table using a INSERT INTO SELECT statement. I'm trying to take a field with numbers and letters and pull out the numbers ...
- 13
-1
votes
2
answers
124
views
How to use try-catch in trigger
I want to prevent inserting data into a table and return 'Error' if 'OrderDate' column is greater than '2021-01-01'. How can I do this by the use of TRIGGER and TRY...CATCH in sql-server v.2019
...
- 1
1
vote
1
answer
55
views
PostgreSQL - How to INSERT with shared values in a specific column
I am creating an Exam Management System database.
I have a table called Questions:
q_id (pk)
q_text
1
What is the name of our galaxy?
2
What planet do we live on?
And a second table called Choices:
...
- 13