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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-19 01:59:14  来源:igfitidea点击:

Insert multiple rows into a table with only one value changing

sqloracle

提问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 casestatement 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           

SQL Fiddle.

SQL小提琴

Adding more rows/values would just require a change to the levellimit and extra whenclauses to match. (like this). You could also have an elsewith a warning in case you get a mismatch in the numbers. There's no special significance to which string value goes with which levelvalue, incidentally.

添加更多行/值只需要更改level限制和when匹配的额外子句。(像这样)。如果else数字不匹配,您也可以使用警告。level顺便说一句,哪个字符串值与哪个值对应并没有特别的意义。

回答by Nick Krasnov

The simplest way to accomplish this would be taking advantage of the connect byclause of selectstatement to generate as many synthetic rows as you need.

完成此操作的最简单方法是利用语句connect by子句select生成所需数量的合成行。

Suppose field1to field3are of varchar2data type and the field4is of number data type, as the sample of data and insertstatement you have provided imply, then you could write the following insertstatement

假设field1tofield3varchar2数据类型,field4is是数字数据类型,正如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 twoand so on as values of the fiedl4column, you could change the above insertstatement as follows:

如果你想从字面上看到value onevalue 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 field4with absolutely random generated string literal you can use dbms_randompackage 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);