如何在不命名所有列的情况下合并两个表？

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:

我希望将它们合并为一个,用第二个替换第一个具有相同ID的内容,并插入不存在的内容。我可以用这句话:

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:

这还不错,但请注意我必须为每个列命名。有没有办法合并这些表而不必命名所有列? Oracle文档坚持认为我在合并中使用'insert'和'set'之后的列名,因此可能需要一些其他语句。结果应该是这样的:

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;

5 个解决方案

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

#2

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

#3

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.

如果您有列的默认值(并且您希望使用这些默认值),则可以省略insert语句中的那些值,否则,您必须指定要插入或更新值的每个列。

There is no shorthand like * for SELECT.

对于SELECT,没有类似*的简写。

#4

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:

我获取了列名(我从SQL开发人员的表DDL中提取它们,但也使用tab_columns表中的方法)并将它们插入到Excel电子表格中。然后我删除了Varchar等语句(使用文本到列Excel函数,然后只删除了varchar,number等语句最后的列),所以它只留下了字段名称。然后我在下一个Excel列中插入一个公式,=“dest。”和A2&“= src。”&A2&“,”并填写所有110个字段,然后在新的Excel列中,使用= A2&“,”并在一个新列,=“src。”和A2&“,”,再次填写所有字段。然后在SQL表中输入:

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.

我还有一个用于合并具有不同列名的表的版本,但这涉及在Excel工作表中映射字段的附加步骤。

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.

我发现我需要使用合并语句来完成我所做的事情 - 与存在的更新相比,我发现Merge可以节省大量时间。

#5

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.

我遇到了同样的问题,我写了一个程序,获取所有表列的列表,并构建动态SQL查询以进行更新,而无需命名所有列。

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

#1