我正在使用SQL Server 2008 Management Studio,并有一个表,我想迁移到不同的db服务器。
有任何选项导出数据作为插入到SQL脚本??
我正在使用SQL Server 2008 Management Studio,并有一个表,我想迁移到不同的db服务器。
有任何选项导出数据作为插入到SQL脚本??
当前回答
在搜索了很多之后,这是我最好的选择:
如果您有大量数据,需要一个紧凑而优雅的脚本,可以试试:SSMS Tools Pack
它生成一个所有选择语句的联合,将项目插入到目标表中,并很好地处理事务。
截图
其他回答
下面是一个使用游标迭代源表创建数据迁移脚本的示例。
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 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!
在搜索了很多之后,这是我最好的选择:
如果您有大量数据,需要一个紧凑而优雅的脚本,可以试试:SSMS Tools Pack
它生成一个所有选择语句的联合,将项目插入到目标表中,并很好地处理事务。
截图
如果你正在运行SQL Server 2008 R2,在SSMS中内置的选项就像上面描述的marc_s一样发生了一些变化。不再像图中那样选择“脚本数据= true”,现在在“表/视图选项”分组上方有一个名为“脚本数据类型”的新选项。在这里,您可以选择只脚本数据、模式和数据或模式。效果非常好。
如果使用SQLServer 2008R2,则需要将“Types of data”设置为“script”字段。