oracle 如何在不命名所有列的情况下合并两个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8944969/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I merge two tables without naming all columns?
提问by jalopezp
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 documentationinsists 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 文档坚持我在合并中的“插入”和“设置”之后使用列名，因此可能需要其他一些语句。结果应该是这样的：
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;
采纳答案by John Doyle
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);
回答by Andrew
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 developer 中的 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.
我发现我需要使用合并语句来完成我的工作 - 与存在的更新相比，我发现合并可以节省大量时间。
回答by Matt Donnan
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
回答by bhamby
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.
没有像*for那样的简写SELECT。
回答by Victor
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');
