How to insert values into a table from the result of a SELECT statement SQL

Question

I've got two tables, one called PLAYERS with this structure:

PLAYERS (name, team, goals, cod)

and another one called NEW_PLAYERS with this structure:

NEW_PLAYERS (name, team, goals)

I want to insert values from NEW_PLAYERS into PLAYERS setting the value 1 for the register cod of all of NEW_PLAYERS.

I thought this code could work but it doesn't.

INSERT INTO PLAYERS (name, team, goals, cod) 
VALUES ((SELECT name, team, goals FROM NEW_PLAYERS), 1);

Does anyone have a better option?

See this [very recent question & answer](https://stackoverflow.com/a/71325876/15332650) — Stu, Mar 02 '22 at 19:12

score 3 · Accepted Answer · answered Mar 02 '22 at 19:12

3

You were almost there. Try this.

INSERT INTO PLAYERS(name,team,goals,cod) 
SELECT name,team,goals,1 FROM NEW_PLAYERS

answered Mar 02 '22 at 19:12

score 0 · Answer 2 · answered Mar 02 '22 at 19:13

0

use INSERT ... SELECT not INSERT ... VALUES

INSERT INTO PLAYERS(name,team,goals,cod)
SELECT n.name
     , n.team
     , n.goals
     , 1 AS cod
  FROM NEW_PLAYERS n
 ORDER BY 1,2,3

answered Mar 02 '22 at 19:13

spencer7593

2 Answers2