将 SQL Server 中的数据导出为 INSERT INTO

Exporting data In SQL Server as INSERT INTO

提问by Hyman Kada

I am using SQL Server 2008 Management Studio and have a table I want to migrate to a different db server.

我正在使用 SQL Server 2008 Management Studio 并且有一个表我想迁移到不同的数据库服务器。

Is there any option to export the data as an insert into SQL script??

是否有任何选项可以将数据作为插入导出到 SQL 脚本中？

回答by marc_s

In SSMS in the Object Explorer, right click on the database, right-click and pick "Tasks" and then "Generate Scripts".

在对象资源管理器的 SSMS 中，右键单击数据库，右键单击并选择“任务”，然后选择“生成脚本”。

This will allow you to generate scripts for a single or all tables, and one of the options is "Script Data". If you set that to TRUE, the wizard will generate a script with INSERT INTO () statement for your data.

这将允许您为单个或所有表生成脚本，其中一个选项是“脚本数据”。如果您将其设置为 TRUE，向导将为您的数据生成一个带有 INSERT INTO () 语句的脚本。

If using 2008 R2 or 2012 it is called something else, see screenshot below this one

如果使用 2008 R2 或 2012 它被称为其他东西，请参见下面的截图

2008 R2 or later eg 2012

2008 R2 或更高版本，例如 2012

Select "Types of Data to Script" which can be "Data Only", "Schema and Data" or "Schema Only" - the default).

选择“脚本的数据类型”，可以是“仅数据”、“架构和数据”或“仅架构”——默认值）。

And then there's a "SSMS Addin" Package on Codeplex (including source) which promises pretty much the same functionality and a few more (like quick find etc.)

然后在 Codeplex 上有一个“ SSMS Addin”包（包括源代码），它提供了几乎相同的功能和更多功能（如快速查找等）

回答by ruffin

For the sake of over-explicit brainlessness, after following marc_s' instructions to here...

为了过于明确的无脑，在按照marc_s 的指示到这里之后......

In SSMS in the Object Explorer, right click on the database right-click and pick "Tasks" and then "Generate Scripts".

在对象资源管理器的 SSMS 中，右键单击数据库，右键单击并选择“任务”，然后选择“生成脚本”。

... I then see a wizard screen with "Introduction, Choose Objects, Set Scripting Options, Summary, and Save or Publish Scripts" with prev, next, finish, cancel buttons at the bottom.

...然后我会看到一个向导屏幕，其中包含“介绍、选择对象、设置脚本选项、摘要和保存或发布脚本”，底部有上一个、下一个、完成、取消按钮。

On the Set Scripting Options step, you have to click "Advanced"to get the page with the options. Then, as Ghlouwhas mentioned, you now select "Types of data to script" and profit.

在“设置脚本选项”步骤中，您必须单击“高级”以获取包含选项的页面。然后，正如Ghlouw所提到的，您现在选择“要编写脚本的数据类型”并获利。

回答by Azadeh Khojandi

If you use it SQLServer 2008R2 you need to set Types of data to script field.

如果使用 SQLServer 2008R2，则需要将数据类型设置为脚本字段。

回答by Ghlouw

If you are running SQL Server 2008 R2 the built in options on to do this in SSMS as marc_s described above changed a bit. Instead of selecting Script data = trueas shown in his diagram, there is now a new option called "Types of data to script"just above the "Table/View Options" grouping. Here you can select to script data only, schema and data or schema only. Works like a charm.

如果您运行的是 SQL Server 2008 R2，则在 SSMS 中执行此操作的内置选项如上述 marc_s 略有更改。Script data = true现在有一个名为"Types of data to script"“表/视图选项”分组上方的新选项，而不是如图所示进行选择。在这里您可以选择仅编写数据脚本、模式和数据或仅模式。奇迹般有效。

回答by ingconti

for SQl server Mng Studio 2016:

对于 SQL Server Mng Studio 2016：

回答by Nickolay

For those looking for a command-line version, Microsoft released mssql-scripterto do this:

对于那些寻找命令行版本的人，微软发布mssql-scripter了这样做：

$ pip install mssql-scripter

# Generate DDL scripts for all database objects and DML scripts (INSERT statements)
# for all tables in the Adventureworks database and save the script files in
# the current directory
$ mssql-scripter -S localhost -d AdventureWorks -U sa --schema-and-data \
                 -f './' --file-per-object

回答by Neil Guy Lindberg

Just updating screenshots to help others as I am using a newer v18, circa 2019.

只是更新屏幕截图以帮助其他人，因为我使用的是 2019 年左右的较新 v18。

Here you can select certain tables or go with the default of all. For my own needs I'm indicating just the one table.

您可以在此处选择某些表或使用所有表的默认值。为了我自己的需要，我只指明了一张桌子。

Next, there's the "Scripting Options" where you can choose output file, etc. As in multiple answers above (again, I'm just dusting off old answers for newer, v18.4 SQL Server Management Studio) what we're really wanting is under the "Advanced" button. For my own purposes, I need just the data.

接下来，您可以在“脚本选项”中选择输出文件等。如上面的多个答案（同样，我只是为更新的 v18.4 SQL Server Management Studio 删除旧答案）我们真正想要的位于“高级”按钮下。为了我自己的目的，我只需要数据。

Finally, there's a review summary before execution. After executing a report of operations' status is shown.

最后，在执行前有一个摘要。执行后会显示操作状态报告。

回答by Nate

You could also check out the "Data Scripter Add-In" for SQL Server Management Studio 2008 from:

您还可以从以下位置查看 SQL Server Management Studio 2008 的“Data Scripter Add-In”：

http://www.mssql-vehicle-data.com/SSMS

http://www.mssql-vehicle-data.com/SSMS

---

---

Their features list:

他们的功能列表：

• It was developed on SSMS 2008 and is not supported on the 2005 version at this time (soon!)

• Export data quickly to T-SQL for MSSQL and MySQL syntax

• CSV, TXT, XML are also supported! Harness the full potential, power, and speed that SQL has to offer.

• Don't wait for Access or Excel to do scripting work for you that could take several minutes to do -- let SQL Server do it for you and take all the guess work out of exporting your data!

• Customize your data output for rapid backups, DDL manipulation, and more...

• Change table names and database schemas to your needs, quickly and efficiently

• Export column names or simply generate data without the names.

• You can chose individual columns to script.

• You can chose sub-sets of data (WHERE clause).

• You can chose ordering of data (ORDER BY clause).

• Great backup utility for those grungy database debugging operations that require data manipulation. Don't lose data while experimenting. Manipulate data on the fly!

• 它是在 SSMS 2008 上开发的，目前在 2005 版本上不受支持（很快！）

• 将数据快速导出到 T-SQL，用于 MSSQL 和 MySQL 语法

• 还支持 CSV、TXT、XML！利用 SQL 必须提供的全部潜力、功能和速度。

• 不要等待 Access 或 Excel 为您完成可能需要几分钟才能完成的脚本工作——让 SQL Server 为您完成并消除导出数据的所有猜测工作！

• 自定义您的数据输出以进行快速备份、DDL 操作等...

• 根据您的需要快速有效地更改表名和数据库架构

• 导出列名或简单地生成没有名称的数据。

• 您可以选择单独的列来编写脚本。

• 您可以选择数据的子集（WHERE 子句）。

• 您可以选择数据的排序（ORDER BY 子句）。

• 适用于需要数据操作的那些脏兮兮的数据库调试操作的出色备份实用程序。实验时不要丢失数据。即时操作数据！

回答by Αλ?κο?

All the above is nice, but if you need to

以上所有都很好，但如果你需要

1. Export data from multiple views and tables with joins
2. Create insert statements for different RDBMSs
3. Migrate data from any RDBMS to any RDBMS

1. 使用联接从多个视图和表中导出数据
2. 为不同的 RDBMS 创建插入语句
3. 将数据从任何 RDBMS 迁移到任何 RDBMS

then the following trick is the one and only way.

那么下面的技巧是唯一的方法。

First learn how to create spool files or export result sets from the source db command line client. Second learn how to execute sql statements on the destination db.

首先学习如何从源数据库命令行客户端创建假脱机文件或导出结果集。其次学习如何在目标数据库上执行sql语句。

Finally, create the insert statements (and any other statements) for the destination database by running an sql script on the source database. e.g.

最后，通过在源数据库上运行 sql 脚本为目标数据库创建插入语句（和任何其他语句）。例如

SELECT '-- SET the correct schema' FROM dual;
SELECT 'USE test;' FROM dual;
SELECT '-- DROP TABLE IF EXISTS' FROM dual;
SELECT 'IF OBJECT_ID(''table3'', ''U'') IS NOT NULL DROP TABLE dbo.table3;' FROM dual;
SELECT '-- create the table' FROM dual;
SELECT 'CREATE TABLE table3 (column1 VARCHAR(10), column2 VARCHAR(10));' FROM dual;

SELECT 'INSERT INTO table3 (column1, column2) VALUES (''', table1.column1, ''',''', table2.column2, ''');' FROM table1 JOIN table2 ON table2.COLUMN1 = table1.COLUMN1;

The above example was created for Oracle's db where the use of dual is needed for table-less selects.

上面的示例是为 Oracle 的 db 创建的，其中无表选择需要使用 dual 。

The result set will contain the script for the destination db.

结果集将包含目标数据库的脚本。

回答by Shane

Here is an example of creating a data migration script using a cursor to iterate the source table.

下面是使用游标创建数据迁移脚本以迭代源表的示例。

SET NOCOUNT ON;  
DECLARE @out nvarchar(max) = ''
DECLARE @row nvarchar(1024)
DECLARE @first int = 1

DECLARE cur CURSOR FOR 
    SELECT '(' + CONVERT(CHAR(1),[Stage]) + ',''' + [Label] + ''')'
    FROM CV_ORDER_STATUS
    ORDER BY [Stage]

PRINT 'SET IDENTITY_INSERT dbo.CV_ORDER_STATUS ON'
PRINT 'GO'

PRINT 'INSERT INTO dbo.CV_ORDER_STATUS ([Stage],[Label]) VALUES';

OPEN cur
FETCH NEXT FROM cur
    INTO @row

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @first = 1
        SET @first = 0
    ELSE
        SET @out = @out + ',' + CHAR(13);

    SET @out = @out + @row

    FETCH NEXT FROM cur into @row
END

CLOSE cur
DEALLOCATE cur

PRINT @out

PRINT 'SET IDENTITY_INSERT dbo.CV_ORDER_STATUS OFF'
PRINT 'GO'