oracle 将多行插入表中,只更改一个值
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/19227699/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert multiple rows into a table with only one value changing
提问by Kaidjin
Let's say I have a table with the following columns:
假设我有一个包含以下列的表格:
field 1 | field 2 | field3 | field4
字段 1 | 领域 2 | 字段3 | 字段 4
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.
我想在这个表中插入多行,但每一行的 field1、field2 和 field3 的值都是相同的。只有 field4 的值会改变。
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 ?
实际上,'field4' 列是字符串类型,在我编写查询时知道不同的值。没有必要从表格或任何东西中获取它们(尽管如果可能,我对可以做到的解决方案感兴趣)这是可行的还是我必须单独编写每个插入?
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.
谢谢。
采纳答案by Alex Poole
You could use a variation on Nicholas Krasnov's answer with a case
statement to set the string values:
您可以使用 Nicholas Krasnov 的答案的变体和case
语句来设置字符串值:
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
Adding more rows/values would just require a change to the level
limit and extra when
clauses to match. (like this). You could also have an else
with a warning in case you get a mismatch in the numbers. There's no special significance to which string value goes with which level
value, incidentally.
添加更多行/值只需要更改level
限制和when
匹配的额外子句。(像这样)。如果else
数字不匹配,您也可以使用警告。level
顺便说一句,哪个字符串值与哪个值对应并没有特别的意义。
回答by Nick Krasnov
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.
完成此操作的最简单方法是利用语句connect by
子句select
生成所需数量的合成行。
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
假设field1
tofield3
是varchar2
数据类型,field4
is是数字数据类型,正如insert
你提供的数据和语句示例所暗示的,那么你可以编写如下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
and so on as values of the fiedl4
column, you could change the above insert
statement as follows:
如果你想从字面上看到value one
,value two
等等作为fiedl4
列的值,你可以改变上面的insert
语句如下:
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:
如果你想field4
用绝对随机生成的字符串文字填充你可以专门使用dbms_random
包和string()
函数:
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
回答by sgeddes
Here's one way to do it -- but probably cleaner and easier to write multiple insert statements:
这是一种方法 - 但可能更干净,更容易编写多个插入语句:
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);