CODEWITHSUNDEEP
sqlmysql5
spraff
spraff

Reputation: 33445

SQL multiple insert with common select-from

I have to insert data into a table with the following pattern

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 the b column and I want to factor it out. I could manually perform the subselect and paste the value in, but that will break encapsulation in the set of scripts I'm writing.

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

Upvotes: 4

Views: 115

Answers (5)

gcbenison
gcbenison

Reputation: 11963

Here are two ways, both of which avoid repeating the subselect, but which aren't strictly "in one query":

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 up column a, then use update to set column b. This could be staged to 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;

Upvotes: 0

Diego
Diego

Reputation: 36166

this is 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'

Upvotes: 0

gbn
gbn

Reputation: 432541

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

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

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

Upvotes: 1

aF.
aF.

Reputation: 66727

Declare a variable with that value and use it on the inserts.

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),

Upvotes: 2

Related Questions