Insert Combine (Value and Select)

insert combine (value and select)

Just add a constant into the SELECT list

INSERT INTO MyTable
            (ColA,
             ColB,
             ColC)
SELECT 1,
       colBB,
       colCC
FROM   MyTable2  

Combine INSERT INTO and SELECT for multiple VALUES

BTW, you can do it like below:

DECLARE @Id INT
SET @Id = (SELECT id FROM colors WHERE color = 'yellow')
INSERT INTO fruits (fruit, colorId)
VALUES ('banana', @Id),
   ('lemon',  @Id);

MySQL - Combining INSERT, VALUES, and SELECT?

Makes sense and is called INSERT SELECT. This is an example query for the uniqueDeviceId 123, Score 5 and Difficulty 'easy':

INSERT INTO scores (score, difficulty, playerid)
  SELECT 5, 'easy', id
  FROM player WHERE uniqueDeviceId = 123;

Combine INSERT INTO and SELECT on MySQL

Place your select query below the insert query and remove values():

INSERT INTO players (uid)
SELECT users.uid 
FROM users 
WHERE users.uid NOT IN (select uid from players);  

EDIT Based On Comments
An easy way would be to use INSERT IGNORE - This will basically insert all the uid that are not in the players's table and ignore the rest:

INSERT IGNORE INTO players (uid)
SELECT users.uid 
FROM users

INSERT INTO (SELECT & VALUES) TOGETHER

Just return the literal value from the SELECT statement; add an expression to the SELECT list. For example:

INSERT INTO db_example.tab_example (id,name,surname,group)
SELECT ID
     , first_name
     , last_name
     , '1' AS group
  FROM db_contacts.tab_mygroup;

---

FOLLOWUP

Q: can I SELECT first_name and last_name in the same column using the AS function? or I need another function?

A: If you want to combine the values in the first_name and last_name into a single column, you could concatenate them using an expression, and use that expression in the SELECT list, e.g

CONCAT(last_name,', ',first_name')

or

CONCAT(first_name,' ',last_name)

The AS keyword won't have any effect in the context of an INSERT ... SELECT, but assigning an alias to that expression that matches the name of the column the expression is being inserted into does serve as an aid for the future reader.

INSERT INTO db_example.tab_example (id,name,surname,group,full_name)
SELECT ID
     , first_name
     , last_name
     , '1' AS group
     , CONCAT(first_name,' ',last_name) AS full_name
  FROM db_contacts.tab_mygroup

Insert Into... Merge... Select (SQL Server)

The trick is to populate the table with the MERGE statement instead of an INSERT...SELECT. That allowes you to use values from both inserted and source data in the output clause:

MERGE INTO Table3 USING
(
    SELECT null as col2, 
           110 as col3, 
           Table1.ID as col4, 
           Table2.Column2 as col5,
           Table2.Id as col6
    FROM Table1
    JOIN Table1Table2Link on Table1.ID=Table1Table2Link.Column1
    JOIN Table2 on Table1Table2Link.Column2=Table2.ID
) AS s ON 1 = 0 -- Always not matched
WHEN NOT MATCHED THEN
INSERT (Column2, Column3, Column4, Column5)
VALUES (s.col2, s.col3, s.col4, s.col5)
OUTPUT Inserted.ID, s.col6
INTO @MyTableVar (insertedId, Table2Id); 

how to combine a WITH statement and an INSERT INTO in SQL

For Oracle, you want the syntax:

insert into temp (quarter)
with bla (year) as (
  select 2021 as year from dual
)
select 10*year + 1 as quarter from bla;

And your CREATE TABLE statement should be:

create table temp (quarter decimal(5));

db<>fiddle here

Combine INSERT with SELECT statement with custom values

You can use the INSERT INTO...SELECT construct, with constant string values for the meta_key and meta_value columns:

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID, 'price_input_currency', 'usd'
FROM wp_posts AS posts
RIGHT JOIN wp_postmeta
    ON posts.ID = post_id
WHERE post_type = 'post' AND
    meta_value LIKE '%$%' AND meta_key = 'price_range'

---

Related Topics