是否有从结果集构建插入语句的 Oracle SQL 工具？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/735139/
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
Is there an Oracle SQL tool that builds insert statements from a result set?
提问by Glenn Wark
Is there an Oracle SQL tool that builds insert statements from a result set? We are currently only allowed to use a tool called SQL Station. I'd like to either suggest a tool, like Rapid SQL or CrazySQuirrell, or build my own re-usable chunk of sql.
是否有从结果集构建插入语句的 Oracle SQL 工具？我们目前只允许使用名为 SQL Station 的工具。我想推荐一个工具，比如 Rapid SQL 或 CrazySQuirrell，或者构建我自己的可重用 sql 块。
采纳答案by Glenn Wark
I found this solution, which is what I'm using now. Thanks for all of the help. It turns out we can use SQL+ too. For some reason I can't run it in SQL Station.
我找到了这个解决方案，这就是我现在正在使用的。感谢所有的帮助。事实证明，我们也可以使用 SQL+。出于某种原因，我无法在 SQL Station 中运行它。
COPY FROM userid/password@from_DB TO userid/password>@to_DB INSERT toDB_tablename USING SELECT * FROM fromDB_tablename where ....;
COPY FROM userid/password@from_DB TO userid/password>@to_DB INSERT toDB_tablename USING SELECT * FROM fromDB_tablename where ....;
commit;
犯罪;
回答by Dave Costa
Where is this result set coming from? If you mean that you want to execute a SELECT, then insert the resulting data into another table, you can do that in a single SQL statement:
这个结果集来自哪里？如果您的意思是要执行 SELECT，然后将结果数据插入到另一个表中，您可以在单个 SQL 语句中执行此操作：
INSERT INTO table2 (columnA, columnB)
SELECT columnA, columnB
FROM table1;
回答by dpbradley
PL/SQL Developer will do this as well. I've used both PL/SQL Developer as well as Oracle's SQL Developer, and in my opinion PL/SQL Developer has a smoother and more consistent interface. Not sure about SQL Developer, but PL/SQL Dev. also lets you export result sets as CSV,XML, and HTML.
PL/SQL Developer 也会这样做。我使用过 PL/SQL Developer 和 Oracle 的 SQL Developer，在我看来，PL/SQL Developer 有一个更流畅和更一致的界面。不确定 SQL Developer，但 PL/SQL Dev。还允许您将结果集导出为 CSV、XML 和 HTML。
It also behaves OK under WINE if you're running Linux.
如果您运行的是 Linux，它在 WINE 下也能正常运行。
回答by Mark Harrison
If you want command line tools, the free cx_OracleTools will do this, and some other nice things as well.
如果您需要命令行工具，免费的 cx_OracleTools 可以做到这一点，还有其他一些不错的东西。
http://cx-oracletools.sourceforge.net/
http://cx-oracletools.sourceforge.net/
- CompileSource - execute statements in a file, checking for errors
- CopyData - copy data from one table or view to another
- DbDebugger - allows simple debugging of PL/SQL
- DescribeObject - describe objects as SQL statements for recreation
- DescribeSchema - describe multiple objects as SQL statements for recreation
- DumpCSV - dump the results of a select statement as comma separated values
- DumpData - dump the results of a select statement as insert statements
- ExportColumn - dump the data from a column into a file
- ExportData - dump the data from a database into a portable dump file
- ExportObjects - describe object as SQL statements for recreation in files
- ExportXML - export data from a table into a simple XML file
- GeneratePatch - generate SQL script to go from one set of objects to another
- GenerateView - generate a view statement for a table
- ImportColumn - import the contents of a file into a column in the database
- ImportData - import the data dumped with ExportData
- ImportXML - import data from an XML file (such as those created by ExportXML)
- RebuildTable - generate SQL script to rebuild the table
- RecompileSource - recompile all invalid objects in the database
- CompileSource - 在文件中执行语句，检查错误
- CopyData - 将数据从一个表或视图复制到另一个
- DbDebugger - 允许简单的 PL/SQL 调试
- DescribeObject - 将对象描述为 SQL 语句以供娱乐
- DescribeSchema - 将多个对象描述为 SQL 语句以供娱乐
- DumpCSV - 将选择语句的结果转储为逗号分隔值
- DumpData - 将选择语句的结果转储为插入语句
- ExportColumn - 将列中的数据转储到文件中
- ExportData - 将数据库中的数据转储到可移植的转储文件中
- ExportObjects - 将对象描述为用于在文件中重新创建的 SQL 语句
- ExportXML - 将表中的数据导出到简单的 XML 文件中
- GeneratePatch - 生成从一组对象到另一组对象的 SQL 脚本
- GenerateView - 为表生成视图语句
- ImportColumn - 将文件内容导入数据库中的列
- ImportData - 导入使用 ExportData 转储的数据
- ImportXML - 从 XML 文件（例如由 ExportXML 创建的文件）导入数据
- RebuildTable - 生成 SQL 脚本来重建表
- RecompileSource - 重新编译数据库中的所有无效对象
回答by Glenn Wark
Yes look at Oracle sql developer.Its free can be downloaded from otn.oracle.com
是的，看看Oracle sql developer。它的免费可以从otn.oracle.com下载
回答by Boydski
In a pinch, using string contatenation works great for smaller statements you want to build:
在紧要关头，使用字符串串联非常适合您要构建的较小语句：
Select
'Insert Into MyOtherTableTable Values(''' || MyMainTableColumn1 || ''' and ''' || MyMainTableColumn2 || ''')'
From MyMainTable
回答by Shrikanth
Right click on the result set of the query, you will get a pop up. select export data and insert. it will ask you for the location to save the file in which insert statements are generated. give file name and the path to save it.
右键单击查询的结果集，您将看到一个弹出窗口。选择导出数据并插入。它会询问您保存生成插入语句的文件的位置。给出文件名和保存路径。
回答by cmaluenda
I know it is too late but It could be helpfull for somebody. If you go to the table, you can "export" the data. The second step is "Specify Data" where you can add some filters. This only works for a table data.
我知道为时已晚，但它可能对某人有所帮助。如果你去表，你可以“导出”数据。第二步是“指定数据”，您可以在其中添加一些过滤器。这仅适用于表数据。
Cheers
干杯
回答by rachid
With Oracle SQL-Developer type and execute as script (F5):
使用 Oracle SQL-Developer 键入并作为脚本执行 (F5)：
select /*insert*/
* from dual;
output:
输出：
Insert into "dual" (DUMMY) values ('X');
you can try also /*csv*/" or /*html*/
你也可以试试 /*csv*/" 或 /*html*/
来源：http: //www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/
SELECT /*csv*/ * FROM scott.emp;
SELECT /*xml*/ * FROM scott.emp;
SELECT /*html*/ * FROM scott.emp;
SELECT /*delimited*/ * FROM scott.emp;
SELECT /*insert*/ * FROM scott.emp;
SELECT /*loader*/ * FROM scott.emp;
SELECT /*fixed*/ * FROM scott.emp;
SELECT /*text*/ * FROM scott.emp;
