Stack Overflow
Stack Overflow

Questions tagged [insert-select]

Use this tag for questions concerning INSERT, the SQL command which inserts data into a table using a SELECT query. Questions should also be tagged [sql] and, where applicable, tagged with the database engine in use.

This tag is used for questions dealing with the SQL INSERT command, where the data is inserted into a table using a SELECT query as shown below:

INSERT INTO <table name>
SELECT <column names> 
FROM <source table>
.....
130 questions
348
votes
7 answers

INSERT with SELECT

I have a query that inserts using a SELECT statement: INSERT INTO courses (name, location, gid) SELECT name, location, gid FROM courses WHERE cid = $cid Is it possible to only select "name, location" for the insert, and set gid to something else…
Kyle
  • 3,491
  • 2
  • 15
  • 4
15
votes
5 answers

MySql Insert Select uuid()

Say you have a table: `item` With fields: `id` VARCHAR( 36 ) NOT NULL ,`order` BIGINT UNSIGNED NOT NULL And: Unique(`id`) And you call: INSERT INTO `item` ( `item`.`id`,`item`.`order` ) SELECT uuid(), `item`.`order`+1 MySql will insert the same…
Richard
  • 159
  • 1
  • 1
  • 3
15
votes
5 answers

SQLITE equivalent for Oracle's ROWNUM?

I'm adding an 'index' column to a table in SQLite3 to allow the users to easily reorder the data, by renaming the old database and creating a new one in its place with the extra columns. The problem I have is that I need to give each row a unique…
Raceimaztion
  • 9,494
  • 4
  • 26
  • 41
11
votes
1 answer

insert select from on duplicate key ignore postgres

I've read from this source that you can do an insert on dupcliate key ignore in postgres, but I cannot seem to get this to work for a select from: link What I've seen you can do is: insert into tab(id, val) values(1, 'nosert'), (2, 'nosert'), (3,…
Alexander Kleinhans
  • 5,950
  • 10
  • 55
  • 111
10
votes
3 answers

INSERT INTO ... SELECT if destination column has a generated column

Have some tables: CREATE TABLE `asource` ( `id` int(10) unsigned NOT NULL DEFAULT '0' ); CREATE TABLE `adestination` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `generated` tinyint(1) GENERATED ALWAYS AS (id = 2) STORED NOT NULL ); I copy…
alik
  • 2,244
  • 3
  • 31
  • 44
6
votes
1 answer

MySQL Insert Select Doesn't Enforce NOT NULL Constraint

I'm having an issue with MySQL 5.6 InnoDb ignoring a NOT NULL foreign key when running an INSERT INTO xxx (col) SELECT .... The constraint is enforced properly when running insert statements in other formats. Foreign key checks are enabled, and…
adam
  • 113
  • 1
  • 1
  • 8
5
votes
0 answers

Spring JdbcTemplate stuck on INSERT INTO...SELECT query

Hi I am using Spring Boot 1.3.5.RELEASE. I have a situation where I need to fire INSERT INTO someTable1 (col1, col2, col3) SELECT (10346, someTable2Id, 1048) FROM someTable2. I am using Spring JdbcTemplate. String sql = "INSERT INTO someTable1(…
Sarvesh
  • 551
  • 1
  • 10
  • 25
5
votes
3 answers

Insert with multiple selects

I have a SQL request that return some ora-01427 error: single-row subquery returns more than one row INSERT INTO my_table (value0, value1, value2, value3) VALUES((SELECT MAX(value0) FROM my_table), '5', (SELECT DISTINCT(value2) FROM…
vdolez
  • 977
  • 1
  • 14
  • 33
5
votes
2 answers

How to select and insert values in mysql using stored procedures

I am new in using stored procedures. I have this query that gets values from tables. After that, I need to insert the result to another table. Here's my query: SELECT a.gender, b.purpose_abroad_as_per_recorded_travel, …
Eric Santos
  • 193
  • 1
  • 1
  • 11
5
votes
1 answer

SQL: Copy one row multiple times, changing one value each time

I have a row of data in a table: Key | Val1 | Val2 ----+------+----- 1 | A | B I would like to copy this row, but assign each new row a different key (actually a foreign key) from a list: New keys -------- 2 3 4 This list can easily be…
Malvineous
  • 25,144
  • 16
  • 116
  • 151
4
votes
1 answer

Alternative to RETURNING with INSERT...SELECT

There's this scenario which involves inserting into a table by copying some columns from another table and returning the generated key out of this insert. Using Oracle Database. Which basically by instinct result to writing this query. INSERT INTO…
arunwithasmile
  • 300
  • 4
  • 16
3
votes
3 answers

Get id from INSERT or SELECT

I've this function that inserts a row into a city table without duplicates. It returns the id of the inserted row: CREATE OR REPLACE FUNCTION public.insert_city( character varying, character varying, character varying, character varying, character…
Andrea Perdicchia
  • 2,786
  • 1
  • 20
  • 19
3
votes
1 answer

N1QL - Insert-Select

I'm trying to execute an insert-select statement in N1QL (inserting documents that their key/value are the result of a select statement) and I'm failing to understand the syntax. I tried executing: insert into tempbucket (KEY payload.id,VALUE select…
shays10
  • 509
  • 5
  • 18
3
votes
1 answer

What is the exact behaviour of an INSERT IGNORE.... SELECT.... ORDER BY... LIMIT... in MySql?

In MySql 5.6 I have a query similar to the following: INSERT IGNORE INTO TABLE_1 (field_a,field_b) SELECT field_a,field_b FROM TABLE_2 WHERE ... ORDER BY field_a LIMIT 0,10 TABLE_1 is a temporary table that is used to store some tuples and then…
clami219
  • 2,958
  • 1
  • 31
  • 45
3
votes
1 answer

HQL insert ... select with parameters

I need to execute batch insert select statement: Query query = em .createQuery( "insert into EntityA (a,b,entity_field) select t.a, t.b, :entity_field from EntityA t where ..."); query.setParameter("entity_field ",…
Mark Carmark
  • 167
  • 4
  • 11
1
2 3
…
8 9