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数据库中的表导出为.sql文件，并将其作为一组INSERT INTO语句?
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数据库。
4 个解决方案
#1
16
Check out the SSMS Tool Pack - it's a great, FREE add-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管理Studio插件，它可以做很多事情——它可以在一个给定的表中生成INSERT语句。
#2
19
Updating since this Q&A was at the top of the search results when I was looking for the answer.
当我在寻找答案的时候，更新这个问答是搜索结果的顶部。
In MSSQL 2008 R2:
该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".
选择“选择特定的数据库对象”，然后选择要插入的表。单击Next，对话框将推进到“设置脚本选项”。
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.
向下滚动选项列表，直到找到“要脚本的数据类型”。单击该行并从下拉菜单中选择“仅使用数据”。点击“OK”。选择保存选项，并点击“下一步”几次。
Note - The output also includes the following after every 100 inserts.
注意——每100次插入之后，输出还包括以下内容。
GO
print 'Processed 200 total records'
#3
9
I have been using this stored procedure for a long time: sp_generate_inserts: the 2000 version and the 2005 (and up) version.
我使用这个存储过程已经有很长一段时间了:sp_generate_insert: 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将返回insert语句作为查询结果。不要忘记修改设置:增加每个列中显示的最大字符数(工具-选项-查询结果)。
#4
2
If you can use other DB management apps the quickest way would be using a tool like SqlDbx which has a built-in "Export as inserts (SQL)" function (just execute a query like SELECT * FROM Table and then use the contextual menu from the result grid).
如果您可以使用其他DB管理应用程序，最快的方法是使用SqlDbx这样的工具，它具有内置的“作为插入(SQL)导出”函数(只需从表中执行SELECT *这样的查询，然后从结果网格中使用上下文菜单)。
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语句为目标表的每一行生成一组结果。然后，您可以将结果导出到一个文件中，或者将其复制到剪贴板中，并将其粘贴到查询窗口中(即使有几兆字节的数据，它也能正常工作)。
#1
16
Check out the SSMS Tool Pack - it's a great, FREE add-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管理Studio插件，它可以做很多事情——它可以在一个给定的表中生成INSERT语句。
#2
19
Updating since this Q&A was at the top of the search results when I was looking for the answer.
当我在寻找答案的时候，更新这个问答是搜索结果的顶部。
In MSSQL 2008 R2:
该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".
选择“选择特定的数据库对象”，然后选择要插入的表。单击Next，对话框将推进到“设置脚本选项”。
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.
向下滚动选项列表，直到找到“要脚本的数据类型”。单击该行并从下拉菜单中选择“仅使用数据”。点击“OK”。选择保存选项，并点击“下一步”几次。
Note - The output also includes the following after every 100 inserts.
注意——每100次插入之后，输出还包括以下内容。
GO
print 'Processed 200 total records'
#3
9
I have been using this stored procedure for a long time: sp_generate_inserts: the 2000 version and the 2005 (and up) version.
我使用这个存储过程已经有很长一段时间了:sp_generate_insert: 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将返回insert语句作为查询结果。不要忘记修改设置:增加每个列中显示的最大字符数(工具-选项-查询结果)。
#4
2
If you can use other DB management apps the quickest way would be using a tool like SqlDbx which has a built-in "Export as inserts (SQL)" function (just execute a query like SELECT * FROM Table and then use the contextual menu from the result grid).
如果您可以使用其他DB管理应用程序，最快的方法是使用SqlDbx这样的工具，它具有内置的“作为插入(SQL)导出”函数(只需从表中执行SELECT *这样的查询，然后从结果网格中使用上下文菜单)。
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语句为目标表的每一行生成一组结果。然后，您可以将结果导出到一个文件中，或者将其复制到剪贴板中，并将其粘贴到查询窗口中(即使有几兆字节的数据，它也能正常工作)。