Hi,
I have a cust_bug_test table with 2 columns(ROOT_CAUSE, BUG_NUMBER) like as follows:
create table cust_bug_test(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER NOT NULL PRIMARY KEY);
insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('This is a #test issue.', 25940149);
insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('This is a #timing issue', 25768241);
insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('#timing', 26167402);
insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('this is #test issue.', 23250474);
insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('This is a #code issue', 23742921); I have got one query: select regexp_substr(root_cause,'\#([a-z0-9_]+)',1,1,'i',1) hashtag, bug_number from cust_bug_test to get the output as follows from above table:
HASHTAG BUG_NUMBER
------------------------------ ----------
test 25940149
timing 25768241
timing 26167402
test 23250474
code 23742921
Now I want to insert above 2 columns and there combination(concatenated with '_') into another table with 3 columns.
Other table name is cust_bug_tag and it is created as follows:
create table cust_bug_tag(tag_id VARCHAR(250) NOT NULL PRIMARY KEY, hashtag VARCHAR(250), BUG_NUMBER NUMBER REFERENCES cust_bug_test (BUG_NUMBER));
I want my cust_bug_tag to have entries like as follows:
tag_id HASHTAG BUG_NUMBER
-------------- ----------------- ----------
test_25940149 test 25940149
timing_25768241 timing 25768241
timing_26167402 timing 26167402
test_23250474 test 23250474
code_23742921 code 23742921
Could you please tell me complete query which will select rows from first table and do insert of those rows in cust_bug_tag like as above.
I don't understand why you need to compound the values into a single column?
In general this is a bad idea. Create a composite primary key instead:
create table cust_bug_tag(
hashtag VARCHAR(250), BUG_NUMBER NUMBER REFERENCES cust_bug_test (BUG_NUMBER),
primary key (hastag, bug_nubmer)
);
Anyway, to add the values to your new table, you just need to insert the select:
insert into cust_bug_tag
select regexp_substr(root_cause,'\#([a-z0-9_]+)',1,1,'i',1) || '_' || BUG_NUMBER,
regexp_substr(root_cause,'\#([a-z0-9_]+)',1,1,'i',1) , BUG_NUMBER
from cust_bug_test;
select * from cust_bug_tag;
TAG_ID HASHTAG BUG_NUMBER
test_25940149 test 25940149
timing_25768241 timing 25768241
timing_26167402 timing 26167402
test_23250474 test 23250474
code_23742921 code 23742921