Questions tagged [insert]
A core statement in SQL that does what it says on the tin: inserts data into a table.
609
questions
0
votes
2answers
20 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
40 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
26 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
22 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),
...
0
votes
0answers
25 views
Smearing values in SQL server that inserts new rows
I was reading this article: https://www.red-gate.com/simple-talk/sql/t-sql-programming/filling-in-missing-values-using-the-t-sql-window-frame/ . This shows how to smear data using quirky update, and ...
4
votes
1answer
62 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
78 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
39 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
1k 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
26 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 ...
0
votes
1answer
16 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
0answers
29 views
Function prefixed with 0x
I have a table created with
Create table log (
ip VARBINARY(16)
);
I can insert into the table with Insert into log values (INET6_ATON("127.0.0.1")).
When I use phpMyAdmin 4.5.4.1 in the "...
0
votes
1answer
28 views
Unable to Insert Casted XML Data into New Column in Temporary Table
Question
How can I insert CAST(event_data as XML) data into a newly added column in a temporary table with the following statement:
INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(...
0
votes
0answers
41 views
mongod log ms meaning
I cannot understand from reading the documentation what is the meaning of the ms in the mongod.log at the end of the following line:
2019-10-30T21:09:41.647Z I COMMAND [co48] command Db.noa ...
0
votes
1answer
36 views
Insert ID from previous insert POSTGRES
I am trying to daisy chain ids through 3 tables. I need to insert a line, take the inserted id and insert that into the next insert, which then takes that new id and inserts it into the final table. ...
2
votes
2answers
94 views
After upgrade from MySQL 5.5 to 5.7 queries are more frequently encountering deadlock
Recently we migrated our production DB to Amazon RDS with version upgrade from 5.5 to 5.7 using AWS DMS service. After that, we are frequently getting deadlock issues for our insert...on duplicate key ...
2
votes
1answer
45 views
Duplicate Inserted Row into the new table when running trigger [closed]
I create 2 tables like this:
CREATE TABLE task
(
task_name TEXT NOT NULL,
duration INT,
UNIQUE(task_name)
);
CREATE TABLE temptable
(
task_name TEXT NOT NULL,
duration INT NULL,
);
Then I ...
0
votes
1answer
28 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
1answer
75 views
SSMS - Openrowset maximum file size
Apologies if this has been answered elsewhere but I couldn't find anything here or online.
Does openrowset have a file size limit? I'm trying to write a stored procedure, part of which checks if ...
0
votes
1answer
35 views
Strategy for making Postgres support multiple versions of client code
I'm about to embark on an overhaul of our code for pushing to Postgres, and want to solicit some feedback before going on.
The setup here is that we've got a lot of software deployed with a non-...
0
votes
0answers
15 views
LEFT JOIN on AFTER INSERT Trigger in MariaDB or MySQL?
Is there any simple way to access records related to a new one that is causing a trigger?
I am allocating expenses for vehicle use, by creating a general journal entry via AFTER INSERT Trigger when a ...
-2
votes
1answer
48 views
INSTEAD OF INSERT does not display changes
This is my table:
CREATE TABLE [MyTable]
(
[GroupId] INT NOT NULL,
[ItemId] INT NOT NULL,
[Data] INT NOT NULL,
PRIMARY KEY CLUSTERED ([GroupId], [...
2
votes
2answers
290 views
MariaDB Optimization - insert and select
I have 2-3 php files.
I am sending sms to users by sending parameter in these php files. I'm checking the 3rd and 4th table before sending sms so select. If there is an id record, I do not insert. If ...
1
vote
1answer
26 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
75 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
110 views
Wrong Output(Null values) for a column
I am unable to get data from bnrcno column while using the below query.
select 'insert into table values('||id||','||bnrcno||','||chargetype||','||domno||','||coa_id||','||liability_gl||','||...
0
votes
0answers
29 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
52 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
33 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
34 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
32 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
79 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
46 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
99 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
51 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
31 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
51 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
39 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
92 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
64 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
21 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
68 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
36 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
1k 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
25 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
267 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
109 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
45 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
547 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 &...
