Skip to main content
New
Stack Overflow Jobs powered by Indeed: A job site that puts thousands of tech jobs at your fingertips (U.S. only). Search jobs

Questions tagged [insert]

A core statement in SQL that does what it says on the tin: inserts data into a table.

Filter by
Sorted by
Tagged with
400 votes
4 answers
928k views

How to insert values into a table from a select query in PostgreSQL?

I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int). Now I want to insert a row into items_ver ...
Jonas's user avatar
  • 33.2k
99 votes
3 answers
477k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
Stéphane's user avatar
  • 5,565
36 votes
1 answer
104k views

Optimal way to ignore duplicate inserts? [duplicate]

Background This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code: -- Ignores duplicates. INSERT INTO db_table (...
Dave Jarvis's user avatar
33 votes
3 answers
80k views

Efficient INSERT INTO a Table With Clustered Index

I have a SQL statement that inserts rows into a table with a clustered index on the column TRACKING_NUMBER. E.G.: INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC) SELECT TRACKING_NUMBER, COL_B, ...
GWR's user avatar
  • 2,807
33 votes
2 answers
65k views

Which one is more efficient: select from linked server or insert into linked server?

Suppose I have to export data from one server to another (through linked servers). Which statement will be more efficient? Executing in source server: INSERT INTO [DestinationLinkedServer].[...
Guillermo Gutiérrez's user avatar
32 votes
2 answers
145k views

What is the fastest way to insert large numbers of rows?

I have a database where I load files into a staging table, from this staging table i have 1-2 joins to resolve some foreign keys and then insert this rows into the final table (which has one partition ...
nojetlag's user avatar
  • 2,877
31 votes
2 answers
31k views

Using source columns in OUTPUT INTO clause of an INSERT statement (SQL Server)

I am writing a batch processing insert statement and would like to use a temp table to keep track of inserted ID's instead of looping through the items myself and calling SCOPE_IDENTITY() for each ...
Louis Somers's user avatar
31 votes
2 answers
29k views

Does Postgres preserve insertion order of records?

For example when I'm using query which returns record ids INSERT INTO projects(name) VALUES (name1), (name2), (name3) returning id; Which produce output: 1 2 3 Will this ids point to corresponding ...
Sergey's user avatar
  • 413
29 votes
5 answers
83k views

How can I tell WHY an insert on a certain table is slow?

I know that an INSERT on a SQL table can be slow for any number of reasons: Existence of INSERT TRIGGERs on the table Lots of enforced constraints that have to be checked (usually foreign keys) Page ...
BradC's user avatar
  • 9,964
28 votes
1 answer
94k views

Benefits of using WITH TABLOCK on an INSERT

Under some circumstances, doing an INSERT INTO <tablename> (WITH TABLOCK) will be faster due to minimal logging. Those circumstances include having the database in the BULK_LOGGED recovery model....
Mark Freeman's user avatar
  • 2,181
26 votes
4 answers
38k views

How to limit maximum number of rows in a table to just 1

I have a configuration table in my SQL Server database and this table should only ever have one row. To help future developers understand this I'd like to prevent more than one row of data being added....
Dib's user avatar
  • 447
26 votes
1 answer
5k views

Is it safe to rely on the order of an INSERT's OUTPUT clause?

Given this table: CREATE TABLE dbo.Target ( TargetId int identity(1, 1) NOT NULL, Color varchar(20) NOT NULL, Action varchar(10) NOT NULL, -- of course this should be normalized Code int ...
ErikE's user avatar
  • 4,305
24 votes
1 answer
18k views

Insert with OUTPUT correlated to sub query table

I am modifying the structure of a database. The content of several columns of the table FinancialInstitution has to be transferred into the table Person. FinancialInstitution is linked to Person with ...
Yugo Amaryl's user avatar
22 votes
3 answers
158k views

Postgres: How to insert row with autoincrement id

There is a Table "context". There is an autoincrement id "context_id". I am using sequence to retrieve the next value. SELECT nextval('context_context_id_seq') The result is: 1, 2, 3,...20.... But ...
user3631472's user avatar
20 votes
2 answers
59k views

On duplicate key do nothing

I am inserting into the following table using LuaSQL with PtokaX API. CREATE TABLE `requests` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ctg` VARCHAR(15) NOT NULL, `msg` VARCHAR(...
hjpotter92's user avatar
20 votes
1 answer
113k views

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

I have a table which is created this way: -- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) ...
Valentin Despa's user avatar
18 votes
2 answers
4k 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 ...
HoneyBadger's user avatar
18 votes
1 answer
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' ...
Paul White's user avatar
  • 85.5k
16 votes
4 answers
12k views

Progress of SELECT INTO statement

Our ETL flow has a long-running SELECT INTO-statement, that's creating a table on the fly, and populating it with several hundred million records. The statement looks something like SELECT ... INTO ...
Dan's user avatar
  • 397
16 votes
3 answers
50k views

Copy from one MySQL table to another MySQL table of same database

I have around 40 million rows in a MySQL table and I want to copy this table to another table in same database. What is the most efficient way of doing this? How much time will it take (approx.)?
Devashish Dixit's user avatar
15 votes
2 answers
107k views

How to cast to int array in Postgresql?

I would like to cast from ARGV[] which is text to int array in PostgreSQL where I marked the pseudocode by TODO in the code. Code in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (...
Léo Léopold Hertz 준영's user avatar
15 votes
2 answers
49k views

Postgresql 10 There is no unique or exclusion constraint matching the ON CONFLICT specification

I currently have a table which looks like this: CREATE TABLE "PDPC".collection ( col_no bigint NOT NULL DEFAULT nextval('"PDPC".collection_col_no_seq'::regclass), q1 character varying(10000) ...
Ray's user avatar
  • 151
14 votes
3 answers
29k views

How to perform conditional insert based on row count?

I'm using Postgres 9.3, and I need to prevent inserts into a table based on a count of specific rows already in the table. Here's the table: Table "public....
Rob Johansen's user avatar
14 votes
1 answer
24k views

postgresql insert from select query, plus static values

Referring to How to insert values into a table from a select query in PostgreSQL?, I would like to INSERT into a table rows from another, specified by a SELECT DISTINCT, plus some static values, ...
Julian Rubisch's user avatar
13 votes
3 answers
31k views

How to insert 10000 new rows?

Presumably there is a straight forward and easy solution. I'm wanting to create 10000 new rows - that are numbered sequentially with no data per row (except sequentially numbered id). I have used: ...
Andrew's user avatar
  • 131
12 votes
1 answer
43k views

Optimize PostgreSQL for a lot of INSERTS and bytea updates

What we have (software): PostrgeSQL 9.3 with base configuration (no changes in postgresql.conf) Windows 7 64 bit Hardware: Intel Core i7-3770 3.9 Ghz 32 Gb RAM WDC WD10EZRX-00L4HBAta Drive (1000Gb, ...
Andremoniy's user avatar
12 votes
2 answers
4k views

How does IDENTITY_INSERT affect concurrency?

I am trying to help a customer with a 3rd party SAP add-on that has a posting glitch and has gone out-of-support. Under certain circumstances, it archives and incomplete post from the posting queue ...
Metaphor's user avatar
  • 906
11 votes
6 answers
35k views

Mysql: Insert performance INNODB vs MYISAM

I am inserting into a simple small table with 5 attributes and 1000 rows. I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds. I changed the engine to MYISAM, then the ...
Uday's user avatar
  • 814
11 votes
5 answers
56k views

Insert into select into multiple related tables using INSERT_IDENTITY

Okay setting the scene. I have three tables, (Table1, Table2 and DataTable) and I want to insert into Table1 and Table2 using DataTable as source. So for every row in DataTable I want a row in Table1 ...
m4rc's user avatar
  • 235
11 votes
2 answers
19k views

How to insert an IP-address into an inet column in PostgreSQL?

I would like to insert an IP-address into a column that has type inet. In what format can I insert the data? is it only binary or is there any way I can insert from text e.g. "192.168.1.082"? Are ...
Jonas's user avatar
  • 33.2k
11 votes
3 answers
399 views

Poor cardinality estimate disqualifies INSERT from minimal logging?

Why is the second INSERT statement ~5x slower than the first? From the amount of log data generated, I think that the second is not qualifying for minimal logging. However, the documentation in the ...
Geoff Patterson's user avatar
11 votes
4 answers
62k views

How can I store a pdf in PostgreSQL

I have to store .pdf files in a table. I have a table, state, with columns: id_state, name, pdffile (bytea) I want to store the pdf files in the pdffile column. How can I do this?
Giuliocas's user avatar
  • 111
11 votes
3 answers
18k views

How to insert values into a table with default values from a select query in PostgreSQL?

Is it possible to INSERT values into a PostgreSQL table from a SELECT statement and to use DEFAULT values for the columns that are null? In my case, the SELECT statement is selecting from JSON. In ...
Jay Cummins's user avatar
11 votes
3 answers
14k views

Insert Speeds for large batches

In my application, my INSERTs seem to be taking a major chunk of the time. I have a large number of objects in memory (~40-50,000) which i want to insert into a table. Lets take a sample table ...
Aditya's user avatar
  • 211
10 votes
1 answer
94k views

How to extract the last inserted row in SQL Server?

When an insert statement is executed one or more rows is inserted into the table, is there any way to extract the last inserted row in SQL Server?
user2493976's user avatar
10 votes
2 answers
3k views

Possible to have nested inserts in Postgres 8.4?

I'm trying to import data into an inventory type database. Here's what I have for the tables (truncated for brevity): create type hostrole as enum ('Physical', 'Virtual', 'Hypervisor', 'Other'); ...
John's user avatar
  • 242
10 votes
4 answers
18k views

InnoDB insertion faster

I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think ...
inohiro's user avatar
  • 345
10 votes
2 answers
24k views

How can I configure MySQL Innodb to handle 1000s of inserts per hour?

I have a very high traffic website where it is possible that 1000s of new records are inserted every hour. This one error is crippling the site: PDOException: SQLSTATE[40001]: Serialization failure: ...
user658182's user avatar
10 votes
3 answers
10k views

join value in a RETURNING which is not inserted

I'm doing an insert from a query which joins two tables, then I want the new ID from the inserted row, and a field from the original row which is not involved in the insert. Is it possible? I get a "...
avances123's user avatar
10 votes
1 answer
70k views

Insert into table select * from table vs bulk insert

I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ? If no, Is there any way exclude index while inserting records. Process is inserting ...
Virul Patel's user avatar
10 votes
2 answers
19k views

How to use Unique key via combinations of table fields?

Take a look at the following sqlfiddle: http://sqlfiddle.com/#!2/dacb5/1 CREATE TABLE contacts ( id int auto_increment primary key, name varchar(20), network_id int, ...
Jake Wilson's user avatar
  • 2,427
10 votes
1 answer
30k views

INSERT from a SELECT combined with a single value

I have this query that returns several rows of item_id: select item_id from properties where name like 'body'; And I have the second one that returns 1 row of tag_id: select id from tags ...
mamesaye's user avatar
  • 375
10 votes
2 answers
25k views

How do I maintain high INSERT-performance on PostgreSQL

I'm working on a project which is parsing data from measurement files into a Posgres 9.3.5 database. At the core is a table (partitioned by month) which contains a row for each measurement point: ...
jamesmc86's user avatar
  • 223
9 votes
2 answers
61k 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 ...
Drizzt's user avatar
  • 91
9 votes
4 answers
24k views

How can I insert if key not exist with PostgreSQL?

I have a table: CREATE TABLE mytable (id SERIAL, name VARCHAR(10) PRIMARY KEY) Now I want to add names to this table, but only if they not exist in the table already, and in both cases return the id....
Jonas's user avatar
  • 33.2k
9 votes
3 answers
14k views

Inserts in MySQL 8 are slower than Inserts in MySQL 5.7

I've started to use MySQL 8 (8.0.12), and I'm realizing that the "inserts" are slower in this version than MySQL 5.6 or MySQL 5.7. It doesn't matter if the table is MyISAM or InnoDB, with just one "...
Juan Carlos Poveda Escobar's user avatar
9 votes
1 answer
3k views

unable to insert row at id, but row does not exist

Here is the weird problem i am facing. I am trying to enter data using following query insert into product_product (id, product_tmpl_id, make_equip, model_equip, name_template, serial_num_equip, ...
Saghir A. Khatri's user avatar
8 votes
2 answers
103k views

Change IDENTITY_INSERT to ON in SQL server [duplicate]

How can I insert an ID into an identity column in SQL Server? I'll make a special item into my table and I will quickly detect that item by the ID. So because all other ID's are bigger or equal to one,...
H. Pauwelyn's user avatar
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 (`...
Relax's user avatar
  • 189
8 votes
2 answers
5k views

SQL Server avoid repeating the collation type multiple times in a query

I have a query which selects rows from a source database (DatabaseA), and inserts them into a target database (Database B). The collation type differs between the databases and they cannot be changed. ...
Laurence Frost's user avatar

1
2 3 4 5
17