为 Oracle 生成 sql insert into
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1649183/
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
Generating sql insert into for Oracle
提问by Alhambra Eidos
The only thing I don't have an automated tool for when working with Oracle is a program that can create INSERT INTO scripts.
在使用 Oracle 时，我唯一没有的自动化工具是可以创建 INSERT INTO 脚本的程序。
I don't desperately need it so I'm not going to spend money on it. I'm just wondering if there is anything out there that can be used to generate INSERT INTO scripts given an existing database without spending lots of money.
我并不迫切需要它，所以我不会花钱。我只是想知道是否有任何东西可以用于在给定现有数据库的情况下生成 INSERT INTO 脚本而无需花费大量资金。
I've searched through Oracle with no luck in finding such a feature.
我在 Oracle 中搜索过，但没有找到这样的功能。
It exists in PL/SQL Developer, but errors for BLOB fields.
它存在于 PL/SQL Developer 中，但 BLOB 字段出错。
回答by Doug Porter
Oracle's free SQL Developer will do this:
Oracle 的免费 SQL Developer 将执行以下操作：
http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
You just find your table, right-click on it and choose Export Data->Insert
您只需找到您的表格，右键单击它并选择“导出数据”->“插入”
This will give you a file with your insert statements. You can also export the data in SQL Loader format as well.
这将为您提供一个包含插入语句的文件。您还可以以 SQL Loader 格式导出数据。
回答by Matas Vaitkevicius
You can do that in PL/SQL Developer v10.
1. Click on Table that you want to generate script for.
2. Click Export data.
3. Check if table is selected that you want to export data for.
4. Click on SQL inserts tab.
5. Add where clause if you don't need the whole table.
6. Select file where you will find your SQL script.
7. Click export.
您可以在 PL/SQL Developer v10 中做到这一点。
1. 单击要为其生成脚本的表。
2. 单击导出数据。
3. 检查是否选择了要为其导出数据的表。
4. 单击 SQL 插入选项卡。
5. 如果不需要整张表，添加 where 子句。
6. 选择您将在其中找到 SQL 脚本的文件。
7. 单击导出。
回答by teopost
Use a SQL function (I'm the author):
使用 SQL 函数（我是作者）：
Usage:
用法：
select fn_gen_inserts('select * from tablename', 'p_new_owner_name', 'p_new_table_name')
from dual;
where:
在哪里：
p_sql – dynamic query which will be used to export metadata rows
p_new_owner_name – owner name which will be used for generated INSERT
p_new_table_name – table name which will be used for generated INSERT
p_sql in this sample is 'select * from tablename'
此示例中的 p_sql 是 'select * from tablename'
You can find original source code here:
您可以在此处找到原始源代码：
Ashish Kumar's scriptgenerates individually usable insert statements instead of a SQL block, but supports fewer datatypes.
Ashish Kumar 的脚本生成单独可用的插入语句而不是 SQL 块，但支持的数据类型较少。
回答by mrD
If you have an empty table the Export method won't work. As a workaround. I used the Table View of Oracle SQL Developer. and clicked on Columns. Sorted by Nullable so NO was on top. And then selected these non nullable values using shift + select for the range.
如果您有一个空表，则导出方法将不起作用。作为一种解决方法。我使用了 Oracle SQL Developer 的表视图。并单击列。按 Nullable 排序，因此 NO 位于顶部。然后使用 shift + select 为范围选择这些不可为空的值。
This allowed me to do one base insert. So that Export could prepare a proper all columns insert.
这让我可以做一个基础插入。这样导出就可以准备适当的所有列插入。
回答by Hans-Peter St?rr
回答by John D
I have been searching for a solution for this and found it today. Here is how you can do it.
我一直在寻找解决方案，今天找到了。这是您如何做到的。
Open Oracle SQL Developer Query Builder
Run the query
Right click on result set and export
打开 Oracle SQL Developer 查询生成器
运行查询
右键单击结果集并导出
回答by Thorsten
If you have to load a lot of data into tables on a regular basis, check out SQL Loader or external tables. Should be much faster than individual Inserts.
如果您必须定期将大量数据加载到表中，请查看 SQL Loader 或外部表。应该比单个插入快得多。
回答by Sven S?nnichsen
You can also use MyGeneration(free tool) to write your own sql generated scripts. There is a "insert into" script for SQL Server included in MyGeneration, which can be easily changed to run under Oracle.
您还可以使用MyGeneration（免费工具）编写自己的 sql 生成脚本。MyGeneration 中包含 SQL Server 的“插入”脚本，可以轻松更改为在 Oracle 下运行。
