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
1
vote
2 answers

PostgreSQL won't accept INSERT with VALUES and FROM?

I am trying to obtain a value obtained from a SELECT, and then INSERT that value, together with other values, into another table: WITH data AS (SELECT name FROM programmes WHERE id = $1) INSERT INTO purchases (name, other_details,…
Bernard
  • 5,209
  • 1
  • 34
  • 64
1
vote
1 answer

insert into one table from another table using join update or insert SQL server

I currently have two tables as seen in the pictures below. The first table is called bottle: The second table is called cases: I am trying to insert into the bottles table where case_id = 0 the values seen in the cases table (ie case_id=100, 100,…
farris
  • 11
  • 1
  • 2
1
vote
1 answer

Combine INSERT with SELECT statement with custom values

I need to clean up my MySQL database and make a lot of new records based on the current state. I need to insert this: INSERT INTO wp_postmeta (post_id, meta_key, meta_value) VALUES ($ID, "price_input_currency", "usd") For each $ID I get from this…
mesqueeb
  • 5,277
  • 5
  • 44
  • 77
1
vote
0 answers

sqlalchemy insert-select from gives error when select clause has zero result

Below is the mwe (assuming session and engine): metadata=MetaData() class node(Base): __tablename__ = 'nodes_tbl' pid = Column(Integer) __table_args__ = ( PrimaryKeyConstraint('pid'), {}, ) for i in…
Kabira K
  • 1,916
  • 2
  • 22
  • 38
1
vote
1 answer

Is mysql insert from subquery atomic?

If I do a query like insert into sometable b + 10 from select b from (select b from sometable order by id desc limit 1) Is this operation atomic? That is, would it be possible for an insert into sometable to change the value of b AFTER is has been…
Benubird
  • 18,551
  • 27
  • 90
  • 141
1
vote
0 answers

Add new row if it does not exists and get new id - MySQL

I have a table id(PK, AI) name(UQ) 1 mike 2 ashly 3 nash I want users are able to add new names. But if name already exists I don't want to add it again, and get the already existing name's id. If name does not…
user3301042
  • 352
  • 2
  • 14
1
vote
3 answers

MySQL insert into select

I'm trying to insert a row in to a table, that table has 3 foreign keys. To get to those keys I have "unique" filds to search. sql create table user( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, username…
CiberWizZ
  • 45
  • 5
1
vote
1 answer

insert table1 select from table2,table3 where

I am using SQL Server 2008 R2 on Windows 7, and I am trying to fill in the missing rows in a table. For background, I have a set of "parameters" which all have a (string) value. There are about 200 of these parameters a set of "context"s where I…
TimChippingtonDerrick
  • 2,042
  • 1
  • 12
  • 13
1
vote
2 answers

Selecting multiple unrelated data from two tables and insert into one table mysql

This is my scenario I have a permissions table with the following fields. id | module | permission 1 | client | add 2 | client | edit 3 | client | delete 4 | someth | edit 5 | someth | delete employee table id | status | somestatus 1 | …
swordfish
  • 4,899
  • 5
  • 33
  • 61
1
vote
0 answers

MySQL Optimize Inserts over Selects with HIGH_PRIORITY option and Concurrency

We have a dedicated MySQL log database and need it optimized for INSERTs as to not slow down the current application, so we plan to use the HIGH_PRIORITY option for the INSERT. Conversely we will only SELECT from this table to view the activity log…
jbwebtech
  • 424
  • 6
  • 11
1
vote
2 answers

MYSQL INSERT query based on SELECT query with LIMIT restrictions

SELECT query SELECT a. * , d.agent_id, COUNT( d.driver_id ) AS `noofdrivers` FROM ta_agent a, ta_drivers d WHERE a.agent_id = d.agent_id …
Karuppiah RK
  • 3,894
  • 9
  • 40
  • 80
1
vote
1 answer

TSQL Batch insert - math doesn't work

I need to insert 1.3 million of records from one table into another, and it takes really long time (over 13 min). After some research I found that it is better to do this operation in batches, so I put together something like this (actual query is…
Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
1
vote
1 answer

MySQL IF...ELSE or IF EXISTS statements

Quick question. I am currently using INSERT... SELECT statement to check a user has a basic member account before they become a coach. This code works fine: INSERT into coaches (U_Name, P_word, M_ID) SELECT members.U_Name,members.P_word,members.M_ID…
jibbajava
  • 17
  • 1
  • 2
  • 6
1
vote
1 answer

ORACLE: INSERT SELECT FROM 2 views and value from param

I'm trying to insert some fields into MYTABLE from views MYVIEW1 and MYVIEW2 and then add a value from a parameter (this is part of a stored procedure) for UPDATED_BY, SYSDATE for UPDATED_ON. How can I correctly do this with INSERT SELECT or some…
esandrkwn
  • 399
  • 1
  • 6
  • 18
1
vote
0 answers

Merging a user's object relationships to another user, with a condition, in mysql

this is my first ever S.O. question :) I am trying to "merge" a temp user to an existing user, and all their associated object relationships - one being "post_relationships" The problem is.. if both users have a relationship to the same post, a…
1 2 3
8 9