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;

Solution 1

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);

Solution 2

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.

Solution 3

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

Comments

[jalopezp]

Yeah, I guess this is the way to do it.

[jalopezp]

But how come you use 'select null' instead of a 'select *'? Is this for efficiency? Should I also be doing this?

[John Doyle]

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.

[RyanfaeScotland]

@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 stackoverflow.com/questions/11005209/… and the second needed the alias removed on the first line which made it 'insert into USER_COUNTERPARTY'.

[D. Mika]

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.