How do I merge two tables without naming all columns?

Question

So say I have these two tables with the same columns. Use your imagination to make them bigger:

USER_COUNTERPARTY:
ID  |Name                        |Credit Rating    |Sovereign Risk    |Invoicing Type
----+----------------------------+-----------------+------------------+---------------
1   |Nat Bank of Transnistria    |7                |93                |Automatic
2   |Acme Ltd.                   |25               |12                |Automatic
3   |CowBInd LLP.                |49               |12                |Manual

TEMP:
ID  |Name                        |Credit Rating    |Sovereign Risk    |Invoicing Type
----+----------------------------+-----------------+------------------+---------------
2   |Acacacme Ltd.               |31               |12                |Manual
4   |Disenthralled Nimrod Corp.  |31               |52                |Automatic

and I want to merge them into one, replacing with the second one whatever has the same ID in the first one, and inserting whatever is not there. I can use this statement:

MERGE INTO USER_COUNTERPARTY C
USING TEMP T
ON (C.COUNTER_ID = T.COUNTER_ID)
WHEN MATCHED THEN UPDATE SET
    C.COUNTER_NAME = T.COUNTER_NAME,
    C.COUNTER_CREDIT = T.COUNTER_CREDIT,
    C.COUNTER_SVRN_RISK = T.COUNTER_SVRN_RISK,
    C.COUNTER_INVOICE_TYPE = T.COUNTER_INVOICE_TYPE
WHEN NOT MATCHED THEN INSERT VALUES (
    T.COUNTER_ID,
    T.COUNTER_NAME,
    T.COUNTER_CREDIT,
    T.COUNTER_SVRN_RISK,
    T.COUNTER_INVOICE_TYPE);

Which is nice enough, but notice that I have to name each of the columns. Is there any way to merge these tables without having to name all the columns? Oracle documentation insists that I use column names after both 'insert' and 'set' in a merger, so some other statement might be needed. The result should be this:

ID  |Name                        |Credit Rating    |Sovereign Risk    |Invoicing Type
----+----------------------------+-----------------+------------------+---------------
1   |Nat Bank of Transnistria    |7                |93                |Automatic
2   |Acacacme Ltd.               |31               |12                |Manual
3   |CowBInd LLP.                |49               |12                |Manual
4   |Disenthralled Nimrod Corp.  |31               |52                |Automatic

In case it helps I'm pasting this here:

CREATE TABLE USER_COUNTERPARTY
( COUNTER_ID             INTEGER       NOT NULL PRIMARY KEY,
  COUNTER_NAME           VARCHAR(38),
  COUNTER_CREDIT         INTEGER,
  COUNTER_SVRN_RISK      INTEGER,
  COUNTER_INVOICE_TYPE   VARCHAR(10) );

INSERT ALL
INTO USER_COUNTERPARTY VALUES (1, ‘Nat Bank of Transnistria’, 7, 93, ‘Automatic’)
INTO USER_COUNTERPARTY VALUES (2, ‘Acme Ltd.’, 25, 12, ‘Manual’)
INTO USER_COUNTERPARTY VALUES (3, ‘CowBInd LLP.’, 49, 12, ‘Manual’)
SELECT * FROM DUAL;

CREATE TABLE TEMP AS SELECT * FROM USER_COUNTERPARTY;
DELETE FROM TEMP;

INSERT ALL
INTO TEMP VALUES (2, ‘Conoco Ltd.’, 25, 12, ‘Automatic’)
INTO TEMP VALUES (4, ‘Disenthralled Nimrod Corp.’, 63, 12, ‘Manual’)
SELECT * FROM DUAL;

You can query the data dictionary (e.g. `USER_TAB_COLUMNS`) to generate the list of column names. — Jeffrey Kemp, Jan 25 '12 at 14:56
True, you can. But what then? I'd maybe have to put them into a collection, loop through them, and use a dynamic insert. For updating all columns I think delete and insert would be better. But I'll keep the data dictionary in mind. — jalopezp, Jan 26 '12 at 10:53
"But what then?" As I said: *generate the list of column names*. e.g. `SELECT ','||column_name FROM user_tab_columns WHERE table_name='MYTABLE' ORDER BY column_id;` - then you copy-and-paste the results into your merge statement. Sure, it's not pretty, and must be re-done if the schema changes, but it works. Certainly I'm not talking about run-time code generation :) — Jeffrey Kemp, Jan 27 '12 at 06:29

score 4 · Accepted Answer · answered Jan 21 '12 at 00:37

4

I believe the only option you have to avoid using the column names is two separate statements:

delete from USER_COUNTERPARTY UC
      where exists
              (select null
                 from TEMP T
                where T.COUNTER_ID = UC.COUNTER_ID);

insert into USER_COUNTERPARTY UC
  select *
    from TEMP T
   where not exists
           (select null
              from USER_COUNTERPARTY UC
             where T.COUNTER_ID = UC.COUNTER_ID);

answered Jan 21 '12 at 00:37

John Doyle

7,475
5
33
40

Yeah, I guess this is the way to do it. – jalopezp Jan 23 '12 at 09:57
But how come you use 'select null' instead of a 'select *'? Is this for efficiency? Should I also be doing this? – jalopezp Jan 23 '12 at 09:58
The `select` in the sub-query to an `exists` is just there to indicate if a row is returned or not, it doesn't matter what columns are returned. Some people will use `select 1`, others `select *`, there is no real difference. – John Doyle Jan 23 '12 at 10:04
1

@JohnDoyle I think your syntax is wrong for the table aliases. For me the first query needed changed to 'delete UC from USER_COUNTERPARTY UC' as per http://stackoverflow.com/questions/11005209/why-cant-i-use-an-alias-in-a-delete-statement and the second needed the alias removed on the first line which made it 'insert into USER_COUNTERPARTY'. – RyanfaeScotland Mar 24 '15 at 09:56
One big difference, which has to be considered, is the fact, that with the DELETE statement possibly existing ON DELETE CASCADE constraints cause the dependent tables may "lose" data records. This does not happen with MERGE. – D. Mika Nov 09 '18 at 11:42

score 1 · Answer 2 · answered Jul 31 '14 at 12:37

I have come across the problem described and the way I have tackled it is very low tech, but thought I would share in case it triggered other ideas for people.

I took the column names (I extract them from the table DDL in SQL developer, but also use the method in the tab_columns table) and inserted them into an Excel spreadsheet. I then removed the Varchar etc statements (using text to columns Excel function and then just deleting the column(s) that the varchar, number etc statements ended up in) so it just left the field names. I then inserted a formula in the next Excel column, ="dest."&A2&"=src."&A2&"," and filled down for all 110 fields, and then in a new Excel column, used =A2&"," and in a new column, ="src."&A2&",", again filling down for all fields. Then in a SQL sheet, I enter:

merge into <schema>.<destination_table> dest
  using <schema>.<source_table> src
on (dest.<link> = src.<link>)
when matched then update set
(<copy all of the first column, 
  not including the linking fields and removing the comma at the end>)
when not matched then insert 
(<copy and paste the second column from Excel, and remove the final comma>)
values
(<copy and paste the third column from Excel and remove the final comma>)

I also have a version for merging tables with different column names, but that involves an additional step of mapping the fields in the Excel sheet.

I find I need to use merge statements for what I do - I find Merge into an immense time saver compared with Update where exists.

score 1 · Answer 3 · answered Jan 20 '12 at 17:59

1

You could try using a wrapped union statement like this:

SELECT (*) FROM
(SELECT * FROM Table1 WHERE ID NOT IN (SELECT ID FROM Table2)
 UNION ALL
 SELECT * FROM Table2)
ORDER BY 1

answered Jan 20 '12 at 17:59

Matt Donnan

4,933
3
20
32

score 0 · Answer 4 · answered Dec 18 '17 at 15:44

I came across the same problem and I wrote a procedure that gets list of all table columns and builds dynamic sql query to do an update without naming all columns.

PROCEDURE update_from_table(
  p_source VARCHAR2,  -- Table to copy all columns from
  p_target VARCHAR2,  -- Table to copy into 
  p_id_name VARCHAR2 -- Primary key name
)
  AS
v_sql VARCHAR2(4096) := 'UPDATE ' || p_target || ' t1 SET (';
v_sql_src VARCHAR2(4096) := ') = (SELECT ';
v_sql_end VARCHAR2(4096) := ' FROM '|| p_source ||' t2 WHERE t1.'||p_id_name||' = t2.'||p_id_name||') 
WHERE EXISTS (
SELECT 1
  FROM '|| p_source ||' t2
 WHERE t1.'||p_id_name||' = t2.'||p_id_name||' )';
v_first BOOLEAN := TRUE;  
BEGIN
FOR col IN
(select column_name from user_tab_columns
  where table_name = p_source
)
LOOP
  IF NOT v_first THEN
    v_sql:= v_sql || ', '; -- adding comma before every arg except first
    v_sql_src := v_sql_src || ', ';
  ELSE
    v_first := FALSE;
  END IF;
  v_sql:= v_sql || col.column_name ;
  v_sql_src:= v_sql_src || col.column_name ;
END LOOP;

v_sql := v_sql || v_sql_src || v_sql_end;
EXECUTE IMMEDIATE v_sql;
END update_from_table;

And then I do merge in 2 steps:

-- Insert not matching records
INSERT INTO USER_COUNTERPARTY
            SELECT *
            FROM TEMP WHERE COUNTER_ID NOT IN (
        SELECT USER_COUNTERPARTY.COUNTER_ID 
        FROM USER_COUNTERPARTY 
        JOIN TEMP ON TEMP.COUNTER_ID = USER_COUNTERPARTY.COUNTER_ID);
-- Update matching records
update_from_table('TEMP', 'USER_COUNTERPARTY', 'COUNTER_ID');

score 0 · Answer 5 · answered Jan 20 '12 at 20:02

If you have default values for columns (and you wish to use these default values), you can omit those from your insert statement, but otherwise, you have to specify every column that you either want to insert or update values for.

There is no shorthand like * for SELECT.

How do I merge two tables without naming all columns?

5 Answers5