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

有任何选项导出数据作为插入到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!

其他回答

SQl server Mng Studio 2016:

如果使用SQLServer 2008R2,则需要将“Types of data”设置为“script”字段。

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

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

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

2008 R2或更高版本,例如2012

选择“脚本的数据类型”,可以是“仅数据”,“模式和数据”或“仅模式”-默认值)。

然后在Codeplex上有一个“SSMS插件”包(包括源代码),它承诺几乎相同的功能和更多的功能(如快速查找等)。

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

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”,现在在“表/视图选项”分组上方有一个名为“脚本数据类型”的新选项。在这里,您可以选择只脚本数据、模式和数据或模式。效果非常好。