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

It's really tough to tell what you're asking here. Can you show an example of what you're doing? And your best guess at what you're trying to do? — Josh Darnell
– Josh Darnell, Commented Jan 4, 2012 at 4:37
can I use VALUES and Select with Insert into in one statement ? — user639478
– user639478, Commented Jan 4, 2012 at 4:54

Mark Rotteveel · Accepted Answer · 2021-04-04 12:02:59Z

5

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

edited Apr 4, 2021 at 12:02

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

answered Jan 4, 2012 at 5:07

ain

22.8k3 gold badges60 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user639478 Over a year ago

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 Over a year ago

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 Over a year ago

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 Over a year ago

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

ain Over a year ago

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

|

Collectives™ on Stack Overflow

Using select and values with insert into

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related