SQL insert with select and hard-coded values

问题:

For illustration purposes, let's say I have a database Movies(Title, Director, Cost, Profits).

Now I would like to insert a new row into the Movies table based on a director found in another table and then hard coded values.

INSERT INTO Movies 
SELECT name 
  FROM Directors 
 WHERE name = 'Lucas';

Is how I understand select inserts work but what if I want to use the select as well as pass in hard coded values. So something theoretically like this:

INSERT INTO Movies 
VALUES(Star Wars,(SELECT name 
                    FROM Directors 
                   WHERE name='Lucas'), 50000, 1000000);

Is this possible?

回答1:

INSERT INTO Movies (Title, Director, Cost, Profits)
SELECT 'Star Wars', name, 50000, 1000000
FROM Directors WHERE name = 'Lucas'

回答2:

Because you can specify hardcoded values in the select statement, it's probably cleaner to use:

insert into movies (title, director, cost, profits)
   select 'Star Wars', name, 50000, 1000000 from directors where name = 'Lucas';

回答3:

Yes, this is possible. User INSERT .. SELECT syntax. See reference for more details. The hard coded values should be in your SELECT query rather than inside the VALUES. E.g.

INSERT INTO Movies
SELECT 'Star Wars', name, 50000, 1000000
FROM Directors 
WHERE name = 'Lucas';

回答4:

you have database as Movies(Title, Director, Cost, Profits). if u want to enter the values in the respective tables then first select the table and then insert values in those tables.