Questions tagged [insert]

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

INSERT is one of the major keywords in SQL and has the function of inserting data into a table. It can optionally specify a list of columns to load into and a set of literal values (VALUES) or the results of a select statement.

The syntax is of the form:

INSERT INTO [destination table] ([optional column list])
VALUES ([value list])

INSERT INTO [destination table] ([optional column list])
SELECT (select statement)

On some systems (sql-server for example), the INTO is optional.

INSERT statements can be used to add multiple rows to a table at a time, and is preferable when loading bulk data instead of a single INSERT statement per row added. To do so, separate the ([value list]) with a comma:

INSERT INTO [destination table] ([optional column list])
VALUES ([value list]), [([value list]), ([value list]), ([value list])...]

796 questions

359

votes

4 answers

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 from items. Is there any short SQL-syntax for doing…

postgresql insert

asked May 27 '11 at 08:33

Jonas

31,495
27
59
64

votes

3 answers

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 NOT NULL UNIQUE PRIMARY KEY, description…

postgresql foreign-key postgresql-9.1 insert

asked Jul 16 '13 at 04:07

Stéphane

4,945
4
17
12

votes

1 answer

Optimal way to ignore duplicate inserts?

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 (tbl_column_1, tbl_column_2) VALUES ( SELECT …

postgresql constraint insert unique-constraint duplication

asked Dec 15 '12 at 22:25

Dave Jarvis

votes

2 answers

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].[DestinationDB].[dbo].[Table] SELECT a, b, c, ... FROM…

sql-server sql-server-2005 linked-server insert

asked Jul 12 '13 at 21:51

Guillermo Gutiérrez

votes

2 answers

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 per month). I have around 3.4 billion rows for…

sql-server sql-server-2012 performance insert query-performance

asked Feb 19 '14 at 14:28

nojetlag

2,847
8
32
39

votes

3 answers

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, COL_C FROM STAGING_TABLE My question is - does it…

sql-server performance insert clustered-index

asked Oct 28 '11 at 13:05

GWR

2,687
8
32
42

votes

5 answers

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 splits in the clustered index when a row is…

sql-server insert sql-server-2000 database-tuning

asked Jan 10 '11 at 20:48

BradC

9,702
8
46
84

votes

2 answers

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 inserted row. The data that needs to be inserted has…

insert output-clause

asked Nov 17 '17 at 11:57

Louis Somers

votes

2 answers

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 inserted values? 1 -> name1 2 -> name2 3 -> name3

postgresql insert order-by postgresql-9.4

asked Mar 20 '15 at 16:45

Sergey

votes

4 answers

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. I have opted to use a trigger for this, as…

sql-server trigger insert limits row

asked Jun 21 '16 at 08:25

Dib

votes

1 answer

Benefits of using WITH TABLOCK on an INSERT

Under some circumstances, doing an INSERT INTO (WITH TABLOCK) will be faster due to minimal logging. Those circumstances include having the database in the BULK_LOGGED recovery model. Is there any other potential performance benefit to…

sql-server sql-server-2012 performance insert

asked Feb 27 '17 at 19:29

Mark Freeman

2,130
5
27
50

votes

1 answer

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 NOT NULL, CONSTRAINT PK_Target PRIMARY KEY…

sql-server sql-server-2012 insert identity bulk-insert

asked Nov 18 '16 at 20:03

ErikE

4,235
4
27
39

votes

1 answer

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 a foreign key. Each FinancialInstitution needs…

sql-server insert output-clause

asked Apr 09 '14 at 15:53

Yugo Amaryl

votes

2 answers

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 there are 24780 rows in the "context" table How can…

postgresql select insert auto-increment

asked May 22 '14 at 09:20

user3631472

votes

1 answer

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) DEFAULT '' NOT NULL, PRIMARY KEY ("id") ); Later…

postgresql database-design insert auto-increment sequence

asked Mar 12 '14 at 20:31

Valentin Despa

2 3

…

53 54 Next