MS SQL Server 2005数据导入SQL语句

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

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

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