插入...选择...有子查询或无列顺序 [英] insert into... select ... with subquery or without column order

问题描述

我正在使用Oracle数据库，我想知道是否可以编写类似的内容:

I'm using Oracle db and I wonder if it's possible to write something like:

INSERT INTO CL (select COLUMN_NAME from USER_TAB_COLUMNS where TABLE_NAME='CL')
SELECT * FROM CLT;

或:

INSERT INTO CL (select COLUMN_NAME from USER_TAB_COLUMNS where TABLE_NAME='CL')
SELECT (select COLUMN_NAME from USER_TAB_COLUMNS where TABLE_NAME='CL') FROM CLT;

所以想法是两个表都具有相同的列，但是列的顺序不匹配，所以当我尝试做简单的时候

So the idea is that both tables have the same columns, but the columns order don't match, so when I try to do simple

INSERT INTO CL 
SELECT * FROM CLT;

我一直在获取ORA-00932:数据类型不一致，如果我一一指定所有列就不会发生.但是我不想这样做，因为我的表有约50列，而且我想拥有一个健壮的解决方案，以后也可以将其应用于其他表.

I keep getting ORA-00932: inconsistent datatypes, it doesn't happen if I specify all columns one by one. But I don't want to do that, because my table has ~50 columns and I want to have robust solution that I could apply later to other tables as well.

这就是为什么我在考虑使用子查询在INSERT INTO查询中获取列名的原因，但是这在sql中是不可能的，或者我做错了.

That's why I was thinking about using subquery to get the column names in the INSERT INTO query but either this is not possible in sql or i'm doing something wrong.

是否有任何方法可以跳过列的顺序(并强制sql使用名称?)或在该查询中使用子查询以相同顺序获得两倍的所有列名称?

Is there any way to skip order of the columns in that (and force sql to use the names maybe? ) or use subquery within that query to obtain two times all column names in the same order?

PS.我当时正在考虑重新排序，以将它们引导至不可见"并返回可见"，但我的版本不支持此功能.同样，它也不会像我所需要的那样可重用.

PS. I was thinking about reordering chaging them to INVISIBLE and back to VISIBLE but it's not supported in my version. Also it wouldn't be as reusable as I need.

推荐答案

不，您不能使用子查询来生成列列表作为SQL语句的一部分.

No, you can't use a subquery to generate the column list as part of an SQL statement.

您可以从数据字典生成完整的语句:

You can generate the full statement from the data dictionary:

select 'insert into cl ("'
  || listagg(column_name, '","') within group (order by column_id)
  || '") select "'
  || listagg(column_name, '","') within group (order by column_id)
  || '" from clt'
from user_tab_columns where table_name = 'CLT';

，然后将其复制并粘贴，或者使用匿名块中的动态SQL:

and then either copy and paste that, or use dynamic SQL from an anonymous block:

declare
  stmt varchar2(4000);
begin
  select 'insert into cl ("'
    || listagg(column_name, '","') within group (order by column_id)
    || '") select "'
    || listagg(column_name, '","') within group (order by column_id)
    || '" from clt'
  into stmt
  from user_tab_columns where table_name = 'CLT';

  dbms_output.put_line(stmt); -- to check and debug
  execute immediate stmt;
end;
/

带有几个虚拟表:

create table clt (col1 number, col2 date, col3 varchar2(10));
create table cl (col3 varchar2(10), col1 number, col2 date);

insert into clt (col1, col2, col3) values (42, date '2018-07-12', 'Test');

insert into cl
select * from clt;

SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE

运行该块可获得:

insert into cl ("COL1","COL2","COL3") select "COL1","COL2","COL3" from clt

PL/SQL procedure successfully completed.

select * from cl;

COL3             COL1 COL2      
---------- ---------- ----------
Test               42 2018-07-12

如果您可能想经常这样做，那么您也可以将该匿名块转换为采用两个表名的过程(您说它需要可重用，但这可能意味着相同的表，并且可能只是脚本中的一个块).

You could also turn that anonymous block into a procedure that takes two table names if this is something you're likely to want to do often (you said it needed to be reusable, but that could mean for the same tables, and could just be a block in a script).

您还可以走得更远，只包括出现在两个表中的列，或者验证数据类型是否完全匹配；尽管还有更多工作，可能完全没有必要.

You could also go further and only include columns that appear in both tables, or verify data types match exactly; though that's a bit more work and may well not be necessary.

这篇关于插入...选择...有子查询或无列顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！