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

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'

如果你正在运行SQL Server 2008 R2，在SSMS中内置的选项就像上面描述的marc_s一样发生了一些变化。不再像图中那样选择“脚本数据= true”，现在在“表/视图选项”分组上方有一个名为“脚本数据类型”的新选项。在这里，您可以选择只脚本数据、模式和数据或模式。效果非常好。

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

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'

SQl server Mng Studio 2016:

对于那些寻找命令行版本的人来说，微软发布了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

dbatools。io是一个基于PowerShell的更加活跃的项目，它提供了Get-DbaDbTable和Export-DbaDbTableData cmdlet来实现这一点:

PS C:\> Get-DbaDbTable -SqlInstance sql2016 -Database MyDatabase \
           -Table 'dbo.Table1', 'dbo.Table2' | 
        Export-DbaDbTableData -Path C:\temp\export.sql

你也可以查看SQL Server Management Studio 2008的“Data Scripter插件”:

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

它们的特点如下:

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!