SQL 如何将表的数据导出到 INSERT 语句中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4083444/
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 export a table's data into INSERT statements?
提问by Justin808
How can I export a table from a SQL Server 2000 database to a .sql file as a bunch of INSERT INTO statements?
如何将 SQL Server 2000 数据库中的表作为一组 INSERT INTO 语句导出到 .sql 文件？
One of the fields in the table is a Text datatype and holds HTML so doing this by hand would be rather time-consuming.
表中的字段之一是文本数据类型并保存 HTML，因此手动执行此操作会相当耗时。
I have access to SQL Server Management Studio 2008 to access the SQL Server 2000 database.
我有权访问 SQL Server Management Studio 2008 以访问 SQL Server 2000 数据库。
回答by mobill
Updating since this Q&A was at the top of the search results when I was looking for the answer.
更新，因为当我寻找答案时，此问答位于搜索结果的顶部。
In MSSQL 2008 R2:
在 MSSQL 2008 R2 中：
Right Click on database: Tasks -> Generate Scripts...
右键单击数据库：任务-> 生成脚本...
The Generate and Publish Scripts dialog will pop up. The Intro page is worthless. Click "Next"
将弹出生成和发布脚本对话框。介绍页面毫无价值。点击下一步”
Choose "Select Specific database objects" and then select the Table(s) you want to get Inserts for. Click Next and the dialog will advance to the "Set Scripting Options".
选择“选择特定数据库对象”，然后选择要为其插入的表。单击下一步，对话框将前进到“设置脚本选项”。
Click on Advanced and you should see:
单击高级，您应该看到：
Scroll down the list of Options until you find "Types of data to script". Click on that row and choose "Data Only" from the pull-down. Click "OK". Choose your Save options and click "Next" a few times.
向下滚动选项列表，直到找到“要编写脚本的数据类型”。单击该行并从下拉列表中选择“仅数据”。单击“确定”。选择您的保存选项，然后单击“下一步”几次。
Note - The output also includes the following after every 100 inserts.
注 - 每 100 次插入后，输出还包括以下内容。
GO
print 'Processed 200 total records'
回答by marc_s
Check out the SSMS Tool Pack- it's a great, FREEadd-on for SQL Server Management Studio which does a lot of things - among other it can generate INSERT statements from a given table.
查看SSMS 工具包——它是SQL Server Management Studio的一个很棒的免费附加组件，它可以做很多事情——除其他外，它可以从给定的表中生成 INSERT 语句。
回答by Endy Tjahjono
I have been using this stored procedure for a long time: sp_generate_inserts: the 2000version and the 2005 (and up)version.
我已经使用这个存储过程很长时间了：sp_generate_inserts：2000版本和2005（及更高版本）版本。
You use it like this:
你像这样使用它：
sp_generate_inserts 'thetablename'
or if you want to filter:
或者如果你想过滤：
sp_generate_inserts 'thetablename', @from='from ... where ... order by ...'
The sp will return inserts statements as query results. Don't forget to modify setting: increase the maximum number of characters displayed in each column (tools - options - query results).
sp 将返回 inserts 语句作为查询结果。不要忘记修改设置：增加每列显示的最大字符数（工具-选项-查询结果）。
回答by Alberto Martinez
If you can use other DB management apps the quickest way would be using a tool like SqlDbxwhich has a built-in "Export as inserts (SQL)" function (just execute a query like SELECT * FROM Tableand then use the contextual menu from the result grid).
如果您可以使用其他数据库管理应用程序，最快的方法是使用像SqlDbx这样的工具，它具有内置的“导出为插入 (SQL)”功能（只需执行类似的查询SELECT * FROM Table，然后使用结果网格中的上下文菜单） .
If you need to stick to SQL Management Studio then you could use a stored procedure like this one:
如果您需要坚持使用 SQL Management Studio，那么您可以使用这样的存储过程：
http://vyaskn.tripod.com/code/generate_inserts.txt
http://vyaskn.tripod.com/code/generate_inserts.txt
It generates a set of results with the SQL INSERT statement for each row of the target table. Then you can exports the results to a file, or just copy them to the clipboard and paste in the query window (it works fine even with several megabytes of data).
它使用 SQL INSERT 语句为目标表的每一行生成一组结果。然后你可以将结果导出到一个文件，或者只是将它们复制到剪贴板并粘贴到查询窗口中（即使有几兆字节的数据也能正常工作）。
