I have two databases with equivalent structure and I need to extract data from one of them in form of INSERT statements (generate script to apply it on the other database).
我有两个具有相同结构的数据库，我需要以INSERT语句的形式从其中一个数据库中提取数据（生成脚本以将其应用于其他数据库）。
How can I do it using Management Studio?
如何使用Management Studio执行此操作？
3 个解决方案
#1
5
You could use the free SSMS Toolpack add-in for SQL Server Management Studio.
您可以使用SQL Server Management Studio的免费SSMS Toolpack加载项。
See the section on Generate Insert statements from resultsets, tables or database
请参阅结果集，表或数据库中的生成插入语句一节
Update: OK, for SSMS Toolpack in SSMS 2012, a licensing scheme has been introduced. SSMS Toolpack for earlier versions of SSMS are however still free.
更新：好的，对于SSMS 2012中的SSMS Toolpack，已经引入了许可方案。但是，早期版本SSMS的SSMS Toolpack仍然是免费的。
#2
62
SSMS supports scripting all data as INSERTS in 2k8:
SSMS支持将所有数据编写为2k8中的INSERTS：
Right click on a database and select Tasks->Generate Scripts...
右键单击数据库并选择Tasks-> Generate Scripts ...
Pick only the tables, Click Next.
仅选择表格，单击“下一步”。
Click on the Advanced button. Scroll down and select "Types of data to script" == Data Only
单击“高级”按钮。向下滚动并选择“脚本数据类型”==仅数据
Decide whether you want results in a new query window or a file. Click on to Finish.
确定是否需要新查询窗口或文件中的结果。单击“完成”。
You then change the using DBname at the top of the script.
然后，您可以在脚本顶部更改使用DBname。
#3
12
You can do it with SQL Server Management Studio. Here are the steps - as Mitch Wheat describes above (for SQL Server Management Studio 2012):
您可以使用SQL Server Management Studio执行此操作。以下是步骤 - 如上面的Mitch Wheat所述（适用于SQL Server Management Studio 2012）：
- Right-click your database.
- 右键单击数据库。
- Select Tasks > Generate Scripts...
- 选择“任务”>“生成脚本”...
- Introduction Step. Click Next.
- 简介步骤。点击下一步。
- Choose Objects. Select entire database or selected objects. Click Next.
- 选择对象。选择整个数据库或所选对象。点击下一步。
- Set Scripting Options. Here's the key step. In order to get all data and objects, click the Advanced button. Then in the Advanced Scripting Options, set the value for Types of data to script to Schema and data.
- 设置脚本选项。这是关键步骤。要获取所有数据和对象，请单击“高级”按钮。然后在“高级脚本选项”中，将“数据类型”的值设置为脚本到架构和数据。
- Click OK. Then Next and Finish.
- 单击确定。然后是下一步和完成。
You will have a script file that contains both database schema and data.
您将拥有一个包含数据库架构和数据的脚本文件。
#1
5
You could use the free SSMS Toolpack add-in for SQL Server Management Studio.
您可以使用SQL Server Management Studio的免费SSMS Toolpack加载项。
See the section on Generate Insert statements from resultsets, tables or database
请参阅结果集，表或数据库中的生成插入语句一节
Update: OK, for SSMS Toolpack in SSMS 2012, a licensing scheme has been introduced. SSMS Toolpack for earlier versions of SSMS are however still free.
更新：好的，对于SSMS 2012中的SSMS Toolpack，已经引入了许可方案。但是，早期版本SSMS的SSMS Toolpack仍然是免费的。
#2
62
SSMS supports scripting all data as INSERTS in 2k8:
SSMS支持将所有数据编写为2k8中的INSERTS：
Right click on a database and select Tasks->Generate Scripts...
右键单击数据库并选择Tasks-> Generate Scripts ...
Pick only the tables, Click Next.
仅选择表格，单击“下一步”。
Click on the Advanced button. Scroll down and select "Types of data to script" == Data Only
单击“高级”按钮。向下滚动并选择“脚本数据类型”==仅数据
Decide whether you want results in a new query window or a file. Click on to Finish.
确定是否需要新查询窗口或文件中的结果。单击“完成”。
You then change the using DBname at the top of the script.
然后，您可以在脚本顶部更改使用DBname。
#3
12
You can do it with SQL Server Management Studio. Here are the steps - as Mitch Wheat describes above (for SQL Server Management Studio 2012):
您可以使用SQL Server Management Studio执行此操作。以下是步骤 - 如上面的Mitch Wheat所述（适用于SQL Server Management Studio 2012）：
- Right-click your database.
- 右键单击数据库。
- Select Tasks > Generate Scripts...
- 选择“任务”>“生成脚本”...
- Introduction Step. Click Next.
- 简介步骤。点击下一步。
- Choose Objects. Select entire database or selected objects. Click Next.
- 选择对象。选择整个数据库或所选对象。点击下一步。
- Set Scripting Options. Here's the key step. In order to get all data and objects, click the Advanced button. Then in the Advanced Scripting Options, set the value for Types of data to script to Schema and data.
- 设置脚本选项。这是关键步骤。要获取所有数据和对象，请单击“高级”按钮。然后在“高级脚本选项”中，将“数据类型”的值设置为脚本到架构和数据。
- Click OK. Then Next and Finish.
- 单击确定。然后是下一步和完成。
You will have a script file that contains both database schema and data.
您将拥有一个包含数据库架构和数据的脚本文件。