使用从表中收集的值创建 SQL INSERT 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7515110/
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
Create SQL INSERT Script with values gathered from table
提问by Francesco
I need to create a INSERT script in order to insert in another database the same data.
If in SQL Server I select "Script table as > INSERT To" I can easily recreate the skeleton for the INSERT statement. However since I have several records to migrate, I would prefer to avoid having to insert the values manually.
我需要创建一个 INSERT 脚本，以便在另一个数据库中插入相同的数据。
如果在 SQL Server 中我选择“Script table as > INSERT To”，我可以轻松地重新创建 INSERT 语句的框架。但是，由于我有几条记录要迁移，因此我宁愿避免手动插入值。
Therefore is there any way to "automatically" get the INSERT script with also the values (coming from the target table) filled in?
因此，有没有办法“自动”获取 INSERT 脚本并填充值（来自目标表）？
I know this could be done with SSIS, but I am wondering whether it would be possible as well with a quicker solution.
我知道这可以通过 SSIS 完成，但我想知道是否也可以使用更快的解决方案。
回答by RedFilter
You can do this with SSMS.
您可以使用 SSMS 执行此操作。
1 - Right-click your database in Object Explorer.
2 - Select Tasks/Generate Scripts...
3 - On the Set Scripting Optionspage, click the Advancedbutton and make sure Types of data to scriptis set to Data only.
1 - 在对象资源管理器中右键单击您的数据库。
2 - 选择任务/生成脚本...
3 - 在设置脚本选项页面上，单击高级按钮并确保脚本数据类型设置为仅数据。
The resulting script will have a USE DATABASEstatement at the top. Change this to the database you would like to insert the data into.
生成的脚本将USE DATABASE在顶部有一个语句。将其更改为您想要插入数据的数据库。
回答by gbn
Use the freeSSMS tools pack to "generate insert statements"?
使用免费的SSMS 工具包“生成插入语句”？
Or in SSMS (don't have it on this PC to confirm) the export wizards allows you to "script data" too
或者在 SSMS 中（不要在这台 PC 上确认），导出向导也允许您“编写数据脚本”
回答by Aaron Bertrand
It will depend on the data types, because you need to conditionally enclose string values in quotes or cast numeric values as strings. You also need to deal with problem characters:
这将取决于数据类型，因为您需要有条件地将字符串值括在引号中或将数值转换为字符串。您还需要处理问题字符：
SELECT 'INSERT INTO dbo.DestinationTable(col1, col2, col3)
SELECT ' + CONVERT(VARCHAR(12), col1) + ','
+ '''' + REPLACE(col2, '''', '''''') + ''','
+ '''' + REPLACE(col3, '''', '''''') + ''';'
FROM dbo.SourceTable;
Vyas has a pretty complex stored procedurefor this purpose.
为此，Vyas 有一个非常复杂的存储过程。
Of course you can do this much easier by just saying:
当然，您只需说：
INSERT INTO OtherDatabase.dbo.DestinationTable(col1, col2, col3)
SELECT col1, col2, col3 FROM dbo.SourceTable;
In other words, you don't need to "script" an insert, you can just run it...
换句话说，您不需要“编写”插入的“脚本”，您只需运行它...
回答by Jeremy Holovacs
use the INSERT INTO ... SELECT format:
使用 INSERT INTO ... SELECT 格式：
回答by Kms
@RedFilter, your solution is working like a charm for less data SIZE.
@RedFilter，您的解决方案对于减少数据SIZE 来说就像一个魅力。
In my case, When I tried to open the Exported sql file, I faced the OutOfMemoryException.
就我而言，当我尝试打开导出的 sql 文件时，遇到了 OutOfMemoryException。
The file size is around 4GB.
文件大小约为 4GB。
In order to get the data from that file, I tried BCP(Bulk Copy Program)approach.
为了从该文件中获取数据，我尝试了BCP（批量复制程序）方法。
Hope that would be help full for someone.
希望这对某人有帮助。
