0

Wondering if there is a way to insert a row into a table from another, with exception of one column in the middle without specifying all the column name? I have 128 columns in the table.

I created a view to store the original records.

CREATE VIEW V_TXN_STG AS
SELECT * FROM TXN_STG;

In table TXN_STG, only one column BRN_CODE is changing.

Something like this doesn't work, because the column is not on the last, but somewhere middle of table structure.

INSERT INTO TXN_STG 
SELECT v.*, 'BRN-001' AS BRN_CODE 
FROM V_TXN_STG v;
user2102665
  • 429
  • 2
  • 11
  • 26

2 Answers2

0

I don't believe that this is possible without explicitly specifying the columns in your select.

mrHLand
  • 43
  • 2
  • 10
0

first you have to get the columns: 

SELECT listagg(column_name, ',') within group (order by column_name) columns
FROM all_tab_columns
WHERE table_name = 'AAA' --Table to insert too
and column_name <> 'B' -- column name you want to exclude
GROUP BY table_name;

Then insert that result on the first row:

insert into aaa(A,C) -- A,C is my result from above,I have excluded column B
select * 
from (select 'a' A,'q' AMOUNT,'c' C from dual union all -- my sample data
select 'a','a','c'  from dual union all
select 'a','b','c'  from dual union all
select 'a','c','c' from dual union all
select 'a','d','c'  from dual ) 
pivot
( 
  max(1)   
  for (AMOUNT) -- the column you want to remove from the sample data
  IN ()
) 
where 1=1 
order by A;
W_O_L_F
  • 1,049
  • 1
  • 9
  • 16
  • Hi W_O_L_F, i couldn't get the part you wrote with the sample data. In first you use listagg to get the columns looks fine, but how about the data? Where is the script that select values from existing columns and supply new value to the excluded column? – user2102665 Apr 11 '19 at 02:16
  • this part : select 'a' A,'q' AMOUNT,'c' C from dual union all -- my sample data select 'a','a','c' from dual union all select 'a','b','c' from dual union all select 'a','c','c' from dual union all select 'a','d','c' from dual Is just a way to show how it works replace with the table you want to read from. – W_O_L_F Apr 12 '19 at 05:51