Using select and values with insert into

Question

I'm writing a stored procedure. I know how to pass values from select into insert.

But, is it possible with INSERT INTO to use values and Select at the same time?

Insert into table_1 (f1, f2, f3, f4, f5, f6, f7, f8, f9,
      FL1, FL2, FL3)

      Select :p_f1, :v_f2, :p_f3, :p_f4,
        abs(:v_f5 * :p_f5),
        abs(:v_f6 * :p_f6),
        :v_f7, :v_f8, :v_9 from RDB$DATABASE
      UNION
      Select f_lookup_id from lookup_table where (f_res >= :v_res) And (f2_lookup_id = :p_id1)
      UNION
      Select f_lookup_id from lookup_table where (f_res >= :v_res) And (f2_lookup_id = :p_id2)
      UNION
      Select f_lookup_id from lookup_table where (f_res >= :v_res) And (f2_lookup_id = :p_id3);

Solution

I guess you want to use the result set of a SELECT statement as a input of a INSERT statement? Yes, that's possible, see FB's language reference. The part what probably causes your problem is that you don't use the VALUES keyword in that case, the statement would look like

INSERT INTO table (fields) SELECT ...

Or if you want to have both "constant values" (like in INSERT INTO ... VALUES(...) statement) and "dynamic values" (using SELECT statement as a source) in one statement then you can union them, i.e.

INSERT INTO table (fields)
     SELECT fields FROM tab_src ...
   UNION
     SELECT constants FROM RDB$DATABASE

where constants is list of values of appropriate type.

---

UPDATE

OK, I guess what you want is actually something like

Insert into table_1 (f1, f2, f3, f4, f5, f6, f7, f8, f9, FL1, FL2, FL3)
VALUES(:p_f1, :v_f2, :p_f3, :p_f4,
        abs(:v_f5 * :p_f5),
        abs(:v_f6 * :p_f6),
        :v_f7, :v_f8, :v_9,
        (Select f_lookup_id from lookup_table where (f_res >= :v_res) And (f2_lookup_id = :p_id1)),
        (Select f_lookup_id from lookup_table where (f_res >= :v_res) And (f2_lookup_id = :p_id2)),
        (Select f_lookup_id from lookup_table where (f_res >= :v_res) And (f2_lookup_id = :p_id3))
)

Comments

[user639478]

I am getting error: SQL error code = -104. Invalid command. count of column list and variable list do not match. I wrote my SQL in generic field names above in main thread - Please advise. thanks

[ain]

The error message count of column list and variable list do not match is pretty clear, isn't it? Your INSERT statement lists 12 columns and thus each "source row" must have 12 columns too, and the type of those columns must match the target column's type. But some of the SELECT statements you union only have one column! You can use NULL or some other (constant) default value instead of field name for those fields your source table doesn't have.

[user639478]

yes the msg is clear but I tried zeros in the first select only also RDB$DATABASE is something can not be concluded so thanks for the enlighten - I used zeros now like this: f_lookup_id,0,0 for first statement and 0, f_lookup_id, 0 and so on - but using UNION will not make zeros or null override real values ?

[user639478]

also using Union will return multi-rows but I need one row only I tried FIRST but still getting multi-rows

[ain]

See the updated answer, the select statements must be singelton selects (return only one row).

[user639478]

Many Thanks. this is what I need.

[user639478]

BTW I still got multi-rows so I used First 1 is that correct ?

[ain]

If youre sure taht the first record is the one you need then yes, nothing wrong with it.