超强的数据导入SQL语句[MS SQL 2005]

Simonzy 2009-08-20 04:11:54

在一个项目中，需要将远程的MS SQL 2005的数据导入到本地。但是，由于用户的权限不够，不能够通过备份/恢复的方式来完成。虽然能够通过SQL Server Management Studio提供的数据导入/导出功能来完成，但是与实际的需求还有差别，不能解决的问题如下：

* 当本地数据库表结构存在时，不能够导入自增列的数据，导入后本地的数据自增列进行了重构
* 当本地数据库表结构不存在时，在导入的时候建立表结构，虽然可以导入全部的数据，但是表的主键、触发器丢失了。

为了实际的需要，本人写了一个SQL脚本，通过链接服务器的方式，可以在数据表结构相同的情况下从远端导入数据到本地的数据库中。SQL脚本如下：

/*

通过链接服务器方式，在两个SQL Server2005服务器中导入数据，支持自增列

先决条件：确保两个服务器中数据库结构全部相同

*/



declare @linkServer varchar(200) /* 链接服务的访问字符串 */

declare @allTables varchar(8000) /* 所有的表名称，逗号分隔 */

declare @Table varchar(200) /* 当前表名称 */

declare @Cols varchar(8000) /* 当前表中的所有列,逗号分隔 */

declare @n int /* 表名称之前的逗号位置 */

declare @nx int /* 表名称之后的逗号位置 */

declare @isIdentity int /* 是否为自增列 */



set @allTables = ''

set @linkServer = '[lingkServerName].[DataBase].dbo.'



-- 禁用约束

exec  sp_msforeachtable   'alter   table   ?   nocheck   CONSTRAINT   all' 



BEGIN TRAN



-- 获取所有的表名,以逗号分隔

select @allTables = @allTables + ',' + [name] from sys.objects where type = 'U' order by [name]

--select [name] from sys.objects where type = 'U' order by [name]

set @n = CHARINDEX(',',@allTables)

set @nx = CHARINDEX(',',@allTables,@n + 1)



-- 循环表名导入数据

while @n < @nx and @nx <= len(@allTables) + 1

begin

	set @Table = '['+substring(@allTables,@n + 1,@nx - @n -1)+']'

	set @Cols = ''



	-- 准备当前表的列

	select @Cols = @Cols + ',[' +[name] + ']' from sys.columns where object_id = OBJECT_ID(@Table) and is_computed = 0

	select @isIdentity = count(*) from sys.columns where is_identity = 1 and object_id = OBJECT_ID(@Table)

	--select @Table + ': ' + @cols

	if (len(@Cols) > 0)

	begin

		set @Cols = right(@Cols,len(@Cols) -1)

		print cast(@isIdentity as varchar(8)) + ':' + @Table

		-- 禁用触发器

		EXEC('DISABLE Trigger ALL on ' + @Table)

		if @isIdentity > 0 

			-- 对自增列的操作

			EXEC(' SET IDENTITY_INSERT '+@Table+' ON; ' 

			+ ' insert into '+@Table+'('+@Cols+') select '+@Cols+' from '+@linkServer + @Table

			+ ' SET IDENTITY_INSERT '+@Table+' OFF; ')

		else

			EXEC('insert into '+@Table+'('+@Cols+') select '+@Cols+' from '+@linkServer + @Table)

		-- 恢复触发器

		EXEC('ENABLE Trigger ALL on ' + @Table)

	end



	-- 获取下一个表名

	set @n = @nx

	set @nx = CHARINDEX(',',@allTables,@n + 1)

	if @nx = 0 set @nx = len(@allTables) + 1

end



if @@error = 0 

	commit 

else 

	rollback



-- 恢复约束

exec  sp_msforeachtable   'alter   table   ?   check   CONSTRAINT   all'