Questions tagged [insert]
A core statement in SQL that does what it says on the tin: inserts data into a table.
588
questions
0
votes
0answers
13 views
Inserting result of UDF into Hive table [on hold]
In Hive, I have a UDF which is created as follows -
create temporary function exampleUDF as 'com.hive.udf.exampleUDF'
The UDF returns a string and takes no parameter.
When I run a select statement on ...
1
vote
1answer
25 views
How to INSERT all entries to a map table
I have a map table as
CREATE TABLE keymap
(
col1 int(11) unsigned NOT NULL,
col2 int(11) unsigned NOT NULL,
col3 int(11) unsigned NOT NULL,
FOREIGN KEY(col1) REFERENCES table1(col1) ON DELETE CASCADE,...
1
vote
2answers
71 views
how to quickly insert data as select
In an Oracle DB have about 200M rows (about 20GB data) that I would like to insert into another table by
insert into foo select * from bar where some_unimportant=condition;
How do I do that the ...
-2
votes
2answers
65 views
Generating insert statements for a table without creating any temporary table
I am able to generate insert statements for any table using this query:
select 'insert into table values('||id||','||bnrcno||','||chargetype||','||domno||','||coa_id||','||liability_gl||','||...
0
votes
0answers
28 views
Trigger to insert new row into second table causes “Connection is busy with results for another command”
I have created this trigger to insert new row into table @AL_ORDR_TRACKER after new row is inserted in table ORDR.
CREATE TRIGGER dbo.trgAfterInsertORDR ON dbo.ORDR
AFTER INSERT
AS
BEGIN
INSERT INTO ...
0
votes
1answer
46 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
1answer
24 views
Generate unique random string to be used as value for a field inside newly created rows in PostgreSQL
I have this statement which generate a random string:
SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '')
FROM ...
2
votes
1answer
30 views
MySQL: INSERT INTO SELECT issues
Consider the following tables:
=> table1
| aid |
| --- |
| a1 |
| a1 |
| a3 |
| a2 |
| ... |
=> table2
| aid | count | va | vb |
| --- | ----- | ----- | ----- |
| a1 | c1 | va1 ...
0
votes
1answer
30 views
Why do some write sometimes go faster than expected for MEDIUMTEXT
We are running a MySQL Innodb database, our server has 4gb memory. We are trying to improve the speed at which a 800KB XML File is stored in a MEDIUMTEXT column. Currently, we have this query
...
0
votes
0answers
18 views
Transactional Replication : Issues with Identity Column in Subscriber
I have an Transactional replication set up form Server A to Server B.
I have a table which has an Identity column as a primary key.
In my publisher ,for the table tb1,the the identity column,Id1 was ...
0
votes
2answers
43 views
how to use the same timestamp along several tables?
Lets say a have 2 tables and both have a datetime column.
On table A, on datetime column, lets say, ts1, is non null, default is CURRENT_TIMESTAMP and on update CURRENT_TIMESTAMP;
The other table, B,...
0
votes
1answer
57 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 ...
0
votes
2answers
37 views
How can I obtain the last inserted row with INSERT … SELECT in PostgreSQL?
I'm trying to batch-copy data in a specific order from one table to another in PostgreSQL 12-beta2. The table is not using sequences, but contains an composite unique Primary Key (user_id, object_id).
...
-2
votes
1answer
30 views
Is there a mechanism in SQL that automatically generates a unique alphanumeric identifier for each row?
I need a unique identifier for objects in tables other than the autoincremented id and it would be helpful if it is generated by SQL after insert to take the load off of the web server
0
votes
1answer
35 views
Insert values with leading zeros
How do I insert values with leading zeros in it?
Example.
db2 "insert into db2inst1.asd_currency
(currency_code, currency_name, currency_decimal_count, currency_code_desc)
values ('007', 'afghani'...
0
votes
0answers
33 views
how to insert an if check in an insert
I have an after insert at the statment level, which must insert the positive or negative result in an attribute based on the count of the values of other attributes. But control structures cannot be ...
0
votes
1answer
30 views
WHILE Loop for data migration/merge with multiple fields for matching
Ok so I have two identical databases in two parts of the world. I need to merge everything into the one I will call North (the other being South). North data trumps South data. I started thinking ...
-1
votes
2answers
61 views
When is an Insert committed?
If I highlight/select both the statements below, without a GO stmt between them, will the Insert be committed once it shows 'Rows Affected 1234' in results pane?
If so, can I then cancel the Rebuild ...
1
vote
1answer
20 views
Which part of a DB bulk insert takes the most time?
One of the most common tip with regards to DB writing is to group your inserts and execute them as a single transaction. My question is, which part of the [begin transaction] - insert - [commit ...
-1
votes
1answer
63 views
What are the numbers in the output of an INSERT statement, and why is the first one zero? [closed]
When you INSERT, you'll get back something like
# CREATE TABLE foo ( a int );
# INSERT INTO foo VALUES (1),(2);
INSERT 0 2
What is the first and second number after the INSERT (the 0 and the 2)?
-1
votes
1answer
28 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
1answer
402 views
Error: There is a column named in table “*SELECT*”, but it cannot be referenced from this part of the query
Assume we have a simple table,
CREATE TABLE foo ( a int PRIMARY KEY, b int );
I'm wanting to write a complex query inside and refer to that query
INSERT INTO foo
SELECT 5 AS z
ON CONFLICT (a)
DO ...
0
votes
0answers
23 views
When does autocommit take place when many rows are inserted as a single statement?
I have some problems with performance of insertion of about 50000 rows into a table. As an optimization it's advised to start a transaction explicitly to avoid committing on each row. However, I ...
1
vote
1answer
104 views
How to specify column positions in insert statements generated by pg_dump?
I need to add a column to a specific position in a table. I'm doing this because I'm merging tables from two different databases:
DB1.schema1.table1 has these columns:
name_pk | date | type
while ...
0
votes
1answer
54 views
INSERT INTO from 2 unrelated tables
I need to INSERT INTO a new table (FinalTable) from 2 unrelated tables, but values from Table1 will need to be repeat for every value pulled from Table2.
Example:
Table1 Table2 ...
0
votes
0answers
41 views
Slow insert (trigger/indexes) processing
Whenever an insert is done in a table these tasks are executed:
One Trigger (see down).
Three indexes: one checks that a primary key is unique and the rest makes sure that two columns don't have ...
2
votes
1answer
338 views
Why am I receiving the identity insert error even after explicitly setting IDENTITY_INSERT ON and using a column list? [closed]
My code:
SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON
INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn
SELECT ColumnWithIdentity, ...
0
votes
0answers
25 views
How to effeciently save this kind of text into database?
I have this kind of text and want to save it to database:
I. Master Text I <<<Parent
I.A. Sub Master Text I &...
0
votes
3answers
802 views
How to copy data into same table based on existing data in sql?
It is possible to copy a set of rows based on Date column and insert in same table with different date?
For example : I have 5 rows with Date column value '201839'
I need to copy those 5 rows in '...
0
votes
0answers
110 views
INSERT INTO SELECT for a large table
I have a large table (200 million rows, 40GB). I tried to create a new table out of it by INSERT INTO SELECT. The process took 5 hours and consumed over 60GB of RAM. The size of the ibd file of the ...
0
votes
1answer
23 views
Multiple inserts using select row and variable combination with one SQL statement
I'm working with SQL 2008 R2 and have a need to find a specific row in a table that has three fields and then insert multiple rows into the same table with each insert using two values from the found ...
1
vote
1answer
44 views
how to prevent UPDATE of existing rows/record but allow INSERT using PostgreSQL trigger?
SOLVED
Edit: Trigger function and trigger are working fine with no errors, problem was with my database itself, dropped the database, created new, it's working now.
How do I prevent UPDATE on any ...
1
vote
1answer
38 views
InfluxDB batch insert only inserts the last line
I'm trying to insert some lines into a InfluxDB instance (running on Windows 7 x64) from both a .txt and a .csv file, but it's only inserting the last line on the file.
I'm currently using the ...
-1
votes
1answer
49 views
Replace NULLs with value
Here is my temporary table:
CREATE TABLE #test123(
c1 float,
c2 float,
c3 float,
c4 float,
c5 float
);
INSERT #test123(c1,c2,c3,c4,c5)
VALUES (2,3,NULL,1,2),
(2,NULL,NULL,...
0
votes
0answers
28 views
Insert performance rapidly slowing down [duplicate]
I have a source table with about 500,000 not normalized data and insert it into 4 different tables related by foreign key constraints.
The first couple of thousand rows are inserted at a speed of ...
0
votes
1answer
19 views
Create table with constraint based on another column
Say a database has the following tables:
movie(id, title, rent_period)
customer(id, name)
I would like to create the following table:
CREATE TABLE rental (
movie_id int,
customer_id int,
...
1
vote
2answers
210 views
Very slow simple INSERT in transaction
I have I problematic query on my SQL Server where I could not find the problem. I have two simple tables, one for the "Headers" and one for the "Items" (each with an PK column, total of five fields ...
0
votes
1answer
28 views
select number that isn't in the table
So I have a table named reserveringen. inside the table there are (Reserveringsnummer(auto_increment and Unique), Voorstellingsnummer, Stoelnummer).
I also have a table named voorstelling
in that ...
0
votes
1answer
158 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 ();
...
1
vote
1answer
325 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 ...
18
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
2k 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
20 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
78 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
149 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
155 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
71 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
40 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
156 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
32 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 ...
