Multiple SQL insert with shared selection

Question

I need to insert data into a table with the following template

INSERT INTO tablename (a, b) VALUES ( (123, (SELECT foo FROM someothertable WHERE some_condition)), (456, (SELECT foo FROM someothertable WHERE some_condition)), (789, (SELECT foo FROM someothertable WHERE some_condition)), ... 

All inserted rows have the same value for column b , and I want to exclude it. I could manually execute the subquery and paste the value, but that would break the encapsulation in the set of scripts that I am writing.

Can I do this in pure SQL in the same query?

Answer 1

 INSERT INTO tablename (a, b) SELECT X.bar, S.foo FROM someothertable S CROSS JOIN (SELECT 123 AS bar UNION ALL SELECT 456 UNION ALL SELECT 789) X WHERE some_condition 

Answer 2

Declare a variable with this value and use it for insertions.

Assuming foo is `varchar (10), it would be something like this:

 declare @toInsert varchar(10) select @toInsert = foo FROM someothertable WHERE some_condition INSERT INTO tablename (a, b) VALUES ( (123, @toInsert), (456, @toInsert), (789, @toInsert), 

Answer 3

 INSERT INTO tablename (a, b) SELECT a, b FROM ( SELECT 123 AS a UNION SELECT 456 UNION ... SELECT 789 ) AS aa CROSS JOIN ( SELECT foo AS b FROM someothertable WHERE some_condition ) AS bb 

Answer 4

that's what I meant:

 create table tableName ( a varchar(50), b varchar(50)) create table someothertable ( keyToTableName varchar(50), someOtherA varchar(50)) insert into someothertable values('a', 'otherA') insert into someothertable values('b', 'otherB') insert into tableName select 'a', someOtherA from someothertable where keyToTableName='a' UNION select 'b', someOtherA from someothertable where keyToTableName='b' 

Answer 5

Here are two ways, both of which allow you not to repeat the subtitle, but not strictly "in one request":

1) Use a temporary variable

 SET @b = SELECT foo FROM someothertable WHERE somecondition; INSERT INTO tablename(a, b) VALUES ( (1, @b), (2, @b), ... 

2) Use insert to set column a , then use update to set column b . This can be put into a temporary table.

 CREATE TEMPORARY TABLE tmp LIKE tablename; insert into tmp(a) values (1),(2),... update tmp set b = select foo from someothertable where some_condition; insert into tablename(a,b) select * from tmp;