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 several rows in this table, but the values ​​of field1, field2 and field3 are the same for each row. Only the value of field 4 will change.

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

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 will 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 column 'field4' is a string type, and different values ​​are known when I write a query. There is no need to get them from a table or anything (although, if possible, I am interested in a solution that can do this) Is this possible or will I have to write each insert separately?

EDIT: I changed the question to clarify the data type of the changing column (shared text data) and where the data comes from. Sorry for those who have already answered without this information.

Thanks.

Answer 1

You can use the answer option of Nicholas Krasnov using the case to set string values:

 insert into my_table(field1, field2, field3, field4) select 'foo', 'bar', 'baz', case level when 1 then 'value one' when 2 then 'value two' when 3 then 'value three' when 4 then 'value four' end from dual connect by level <= 4; select * from my_table; FIELD1 FIELD2 FIELD3 FIELD4 ------ ------ ------ -------------------- foo bar baz value one foo bar baz value two foo bar baz value three foo bar baz value four 

SQL Fiddle

Adding more rows / values ​​will only require a change in the level limit and additional when clauses. ( like this ). You can also have else with a warning if you get a mismatch in numbers. There is no particular meaning to which string value the level value refers, by the way.

Answer 2

The easiest way to do this is to use the connect by clause of the select statement to create as many synthetic strings as possible.

Suppose field1 - field3 are of data type varchar2 , and field4 is the data type of numbers, as indicated in the sample data and insert that you specified, then you can write the following insert

 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 , etc. as the values ​​of the fiedl4 column, you can modify the above insert 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 fill field4 an absolutely random generated string literal, you can use the dbms_random package and string() functions:

 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 

Answer 3

Here's one way to do this - but probably a cleaner and easier to write multiple insert statement:

 insert into my_table select 1,1,1,field from (select 1 field from dual union select 2 from dual union select 3 from dual); 

• SQL Fiddle Demo