0

I would like to append a few rows to an already existing table (15 rows to be precise). What is the quickest way to do this?

This works, but it seems redundant.

select person_id from people_table
union all
select '0010' as person_id from dual
union all
select '0019' as person_id from dual
union all
select '0085' as person_id from dual

I was wondering if there's a solution along the lines of:

select person_id from people_table
union all
select ('0010','0019','0085') as person_id from dual

Please note that I want to preserve the leading zeros for each element in my list. This post is almost what I'm looking for, but it converts each element to integers and drops the leading zeros.

Arturo Sbr
  • 5,567
  • 4
  • 38
  • 76
  • "Concatenate" is a technical word, with a different meaning from the way you are using it. In a technical field like computing, using language imprecisely, like you are, causes a lot of problems. –  Jul 03 '20 at 14:37
  • Concatenating by rows is very common in tons of data frame libraries (python's `pandas.concat` for example). I didn't want to use `join` because that has a whole other meaning in SQL. And what do you even mean by "technical word". Can you think of something more ambiguous to say? – Arturo Sbr Jul 03 '20 at 14:42
  • Right. What you do want to do in your problem is to "union" (or "union all") a few rows to an already existing rowset. –  Jul 03 '20 at 14:46
  • Does this answer your question? [How can I select from list of values in Oracle](https://stackoverflow.com/questions/10353969/how-can-i-select-from-list-of-values-in-oracle) – Ponder Stibbons Jul 03 '20 at 14:51
  • @PonderStibbons Almost! That syntax seems very convenient, but it converts each entry in my list to numeric values and gets rid of the leading zeros. – Arturo Sbr Jul 03 '20 at 14:59
  • @ArturoSbr Second answer applies to varchars. And all answers posted here are covered there. – Ponder Stibbons Jul 03 '20 at 16:07

3 Answers3

2

You need to use the hiearchy query as follows:

select person_id from people_table
union all
select regexp_substr('0010,0019,0085','[^,]+',1,level) 
  from dual
connect by  level <= length (regexp_replace('0010,0019,0085', '[^,]+'))  + 1 ;
Popeye
  • 35,427
  • 4
  • 10
  • 31
  • Awesome, this works. Could you explain what the parameters `'[^,]+', 1, level` do? – Arturo Sbr Jul 03 '20 at 14:43
  • This may work, but note that the OP doesn't have a comma-separated input, he does have several atomic inputs. Your solution assumes he will list-aggregate them first. –  Jul 03 '20 at 14:47
1

Please use below query using regular expression,

select person_id from people_table
union all
select distinct  trim(regexp_substr(('0010,0019,0085'),'[^,]+', 1, level) ) as person_id
 from dual
 connect by regexp_substr(('0010,0019,0085'), '[^,]+', 1, level)
 order by level;
Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53
  • This may work, but note that the OP doesn't have a comma-separated input, he does have several atomic inputs. Your solution assumes he will list-aggregate them first. –  Jul 03 '20 at 14:47
1

You can use a system-provided collection type; you select from it using the TABLE operator (even that is no longer necessary since Oracle 12, as I demonstrate below). Note that the column name is COLUMN_NAME - that is the name Oracle chose when they created the system-provided type.

Let's create a small table for testing:

create table people_table (person_id varchar2(10), person_name varchar2(10));

insert into people_table (person_id, person_name) values ('2003', 'Maria');
insert into people_table (person_id, person_name) values ('2005', 'Peter');

Then, here is how you can do what you wanted:

select person_id from people_table
union all
select column_value from sys.odcivarchar2list('1000', '1001', '1002')
;

PERSON_ID
---------
2005
2003
1000
1001
1002

Do a Google search for SYS.ODCIVARCHAR2LIST (and similar SYS.ODCINUMBERLIST) if you are not familiar with it; it's quite useful.

  • Perhaps worth adding, any scalar collection type will do, including ones you create yourself or others already present in the system: `select * from all_coll_types where coll_type = 'TABLE' and elem_type_name = 'VARCHAR2'` – William Robertson Jul 04 '20 at 08:56