I want to insert from a select statement using union ALL

Question

I want to use union on a query while inserting records in a table. The insert is also having some hard coded values. Rest of the values are coming from the union. eg..

INSERT INTO my_test_one (name, sirname, Dept)
VALUES
  ((select 'name1','sirname1', Dept FROM my_test_one_backup WHERE dept = 500 
    union all
    select 'name1','sirname1', Dept FROM my_test_one_backup WHERE dept = 501 ));

Solution 1

Try:

INSERT INTO my_test_one (name, sirname, Dept) 
(select 'name1','sirname1', Dept 
   FROM my_test_one_backup 
  WHERE dept = 500      
UNION ALL
 select 'name1','sirname1', Dept 
   FROM my_test_one_backup 
  WHERE dept = 501 );

Hope it helps...

Solution 2

I think you need no VALUES here. (INSERT INTO ... ( ... ) SELECT ...)

Solution 3

Why don't you use two insert instead of using a union all?

INSERT INTO my_test_one (name, sirname, Dept)
SELECT 'name1','sirname1', Dept FROM my_test_one_backup WHERE dept = 500;

INSERT INTO my_test_one (name, sirname, Dept)
SELECT 'name1','sirname1', Dept FROM my_test_one_backup WHERE dept = 501;

or

INSERT INTO my_test_one (name, sirname, Dept)
SELECT 'name1','sirname1', Dept FROM my_test_one_backup WHERE dept in (500,501);

Comments

[aF.]

@Fred in the into my_test_one, you may test it :)

[Andriy M]

That will work if my_test_one doesn't exist. The OP, however, seems to want to insert into an existing table.

[Allan]

@Jaimie F: Parenthesis are not what made the difference here. It was removing the values keyword, which is only used when inserting static values, rather than values from a select statement.