MySQL INSERT INTO ... VALUES and SELECT
MysqlSqlSelectInsertMysql Problem Overview
Is there a way to insert pre-set values and values I get from a select-query? For example:
INSERT INTO table1 VALUES ("A string", 5, [int]).
I have the value of "A string" and the number 5, but I've to find the [int] value from a select like this:
SELECT idTable2
FROM table2
WHERE ...
that gives me that id to put inside table1.
How to merge this into one statement?
Mysql Solutions
Solution 1 - Mysql
Use an insert ... select
query, and put the known values in the select
:
insert into table1
select 'A string', 5, idTable2
from table2
where ...
Solution 2 - Mysql
just use a subquery right there like:
INSERT INTO table1 VALUES ("A string", 5, (SELECT ...)).
Solution 3 - Mysql
INSERT INTO table_name1 (id, name, address, contact_number) SELECT id, name, address, contact_number FROM table_name2;
Solution 4 - Mysql
try this
INSERT INTO TABLE1 (COL1 , COL2,COL3) values
('A STRING' , 5 , (select idTable2 from Table2) )
where ...
Solution 5 - Mysql
All other answers solves the problem and my answer works the same way as the others, but just on a more didactically way (this works on MySQL... don't know other SQL servers):
INSERT INTO table1 SET
stringColumn = 'A String',
numericColumn = 5,
selectColumn = (SELECT idTable2 FROM table2 WHERE ...);
You can refer the MySQL documentation: INSERT Syntax
Solution 6 - Mysql
INSERT INTO table1
SELECT "A string", 5, idTable2
FROM table2
WHERE ...
See: http://dev.mysql.com/doc/refman/5.6/en/insert-select.html
Solution 7 - Mysql
Try the following:
INSERT INTO table1
SELECT 'A string', 5, idTable2 idTable2 FROM table2 WHERE ...
Solution 8 - Mysql
INSERT INTO table1 (col1, col2)
SELECT "a string", 5, TheNameOfTheFieldInTable2
FROM table2 where ...
Solution 9 - Mysql
Try this:
INSERT INTO table1 SELECT "A string", 5, idTable2 FROM table2 WHERE ...
Solution 10 - Mysql
INSERT INTO table1(Table2Id, KeyTypeEnumId, SortOrder, TenantId)
SELECT Id, 1, 1, TenantId
FROM table2
WHERE WebsitePageTypeEnumId = 10 AND ElementTypeEnumId = 16