Insert multiple rows into a table with only one value changing

Question

Let's say I have a table with the following columns:

field 1 | field 2 | field3 | field4

I want to insert multiple rows in this table, but the values of field1, field2 and field3 are identical for each row. Only the value of field4 will change.

Obviously I could insert each row separately but the resulting query would be a bit ugly, and I'm wondering if there is a more efficient / elegant way to do it.

I thought of something like this for example:

insert into my_table (field1, field2, field3, field4) values (foo, bar, baz, ('value one','value two','value three','value four'))

And the result would be:

field1 | field2 | field3 | field4
foo    | bar    | baz    | value one
foo    | bar    | baz    | value two
foo    | bar    | baz    | value four
foo    | bar    | baz    | value five

In practice, the 'field4' column is a string type, and the different values are known when I write the query. There's no need to get them from a table or anything (although if it's possible, i'm interested in a solution that can do it) Is this posible or will I have to write each insert separately ?

EDIT: I've changed the question to be more clear about the data type of the changing column (general textual data) and where does the data come from. Sorry for those who have already answered without these information.

Thanks.

Answer 1

The simplest way to accomplish this would be taking advantage of the connect by clause of select statement to generate as many synthetic rows as you need.

Suppose field1 to field3 are of varchar2 data type and the field4 is of number data type, as the sample of data and insert statement you have provided imply, then you could write the following insert statement

Insert into your_table_name(field1, field2, field3, field4)
   select 'foo'
        , 'bar'   /* static string literals */
        , 'baz'
        , level  /* starts at 1 and will be increased by 1 with each iteration */
    from dual
 connect by level <= 5  /* regulator of number of rows */ 

Result:

FIELD1      FIELD2      FIELD3      FIELD4
----------- ----------- ----------- ----------
foo         bar         baz                  1
foo         bar         baz                  2
foo         bar         baz                  3
foo         bar         baz                  4
foo         bar         baz                  5

Edit:

If you want to literally see value one, value two and so on as values of the fiedl4 column, you could change the above insert statement as follows:

Insert into your_table_name(field1, field2, field3, field4)
   select 'foo'
        , 'bar'          
        , 'baz'
        , concat('value ', to_char(to_date(level, 'J'), 'jsp'))          
    from dual
 connect by level <= 5  

Result:

 FIELD1 FIELD2 FIELD3 FIELD4
------ ------ ------ -------------
foo    bar    baz    value one
foo    bar    baz    value two
foo    bar    baz    value three
foo    bar    baz    value four
foo    bar    baz    value five

If you want to populate the field4 with absolutely random generated string literal you can use dbms_random package and string() function specifically:

Insert into your_table_name(field1, field2, field3, field4)
  select 'foo'
       , 'bar'          
       , 'baz'
       , dbms_random.string('l', 7)       
   from dual
connect by level <= 5 

Result:

FIELD1 FIELD2 FIELD3 FIELD4
------ ------ ------ --------
foo    bar    baz    dbtcenz
foo    bar    baz    njykkdy
foo    bar    baz    bcvgabo
foo    bar    baz    ghxcavn
foo    bar    baz    solhgmm