一段用于在SQL Server间在线复制表数据的查询代码

上一篇 / 下一篇 2008-06-23 15:30:42 / 个人分类：其它

/*
这个没多少用处，只是偶然的工作需要而写的。
从其他服务器复制相同的数据：要求源数据库的表结构和目的数据库的表结构要相同
一、先从源数据库服务器导出脚本，在本地建立一个空的数据库
二、在本地建立链接服务器到源数据库服务器
三、设置运行参数
      @source_db   源数据库，格式为：链接服务器.源数据库.dbo.
      @dest_db          目标数据库，只需数据库名称
      @ingore_tbs       要忽略的表清单，位于此处的表将被忽略，格式为：表名,表名,……
      @append_mod   1 从源数据库添加数据       0 用源数据库表的数据覆盖本地表
      @copy_ident   1 复制标识字段的值       0 不复制标识字段 (identity字段）
四、运行代码
五、注意事项
      如果设置@append_mod为1，且本地表存在主键重复，将导致该表复制失败
*/

DECLARE @source_db    VARCHAR(50),
        @dest_db      VARCHAR(50),
        @ingore_tbs   VARCHAR(1000),
        @drop_table   bit,
        @append_mod   bit,
        @copy_ident   bit,
        @table_name   VARCHAR(200),
        @col_name     sysname,
        @col_ident    bit,
        @col_type     sysname,
        @col_list     VARCHAR(1000),
        @sql          VARCHAR(2000)

SET @source_db = 'SQLS.TestDB.dbo.'
SET @dest_db    = 'TestDB'
SET @ingore_tbs = 'xdk,'
SET @drop_table = 0
SET @append_mod = 0
SET @copy_ident = 1

--检查现数据库与目标数据库是否相同，为了安全性考虑，不自动更改当前数据库
IF (db_name() <> @dest_db)
BEGIN
    PRINT '当前数据库与目标数据库不相符，请更改当前数据库。'
    RETURN
END

--遍历所有表名称
DECLARE cur_tb CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U'
OPEN cur_tb
FETCH NEXT FROM cur_tb INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
--如果处于忽略列表，则跳到下一个表
IF CHARINDEX(@table_name+',', @ingore_tbs) > 0
    GOTO NEXT_TABLE

--如果源数据库不存在这个表，跳到下一个表
EXEC ('SELECT name INTO #tmp FROM ' + @source_db + 'sysobjects WHERE name = ''' + @table_name + '''')
IF @@ROWCOUNT = 0
    GOTO NEXT_TABLE

--拷贝数据
--判断清除数据
IF @append_mod = 0 BEGIN
    PRINT '删除表内容：' + @table_name
    SET @sql = 'DELETE FROM ' + @table_name
    EXEC (@sql)
END
--得到所有字段列表
IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE id = object_id('tempdb.dbo.#cols'))
    DROP TABLE #cols
CREATE TABLE #cols([col_name] sysname, col_ident bit, col_type sysname)
SET @sql = 'INSERT INTO #cols SELECT A.name, COLUMNPROPERTY(B.id, A.name, ''IsIdentity''),'
           + '(SELECT TOP 1 name FROM systypes C WHERE C.xtype = A.xtype) '
           + 'FROM syscolumns A JOIN sysobjects B ON B.id = A.id '
           + 'WHERE B.name = ''' + @table_name + ''' ORDER BY A.colid'
EXEC (@sql)
--遍历所有字段，得到要复制的字段列表
SET @col_list = ''
DECLARE cur_col CURSOR FOR SELECT [col_name], col_ident, col_type FROM #cols
OPEN cur_col
FETCH NEXT FROM cur_col INTO @col_name, @col_ident, @col_type
WHILE @@FETCH_STATUS = 0
BEGIN
    --timestamp类型的字段不复制
    IF @col_type <> 'timestamp'
    BEGIN
      IF @copy_ident = 1 OR @col_ident = 0
        SET @col_list = @col_list + ',' + @col_name
    END
    FETCH NEXT FROM cur_col INTO @col_name, @col_ident, @col_type
END
CLOSE cur_col
DEALLOCATE cur_col
--如果没有需要复制的字段，继续处理下一个表
SET @col_list = SUBSTRING(@col_list, 2, 1000)
IF (@col_list = '') GOTO NEXT_TABLE
--生成SQL语句，复制数据
SET @sql = ''
IF @copy_ident = 1 AND EXISTS (SELECT 1 FROM #cols WHERE col_ident = 1)
    SET @sql = 'SET IDENTITY_INSERT ' + @table_name + ' ON '
SET @sql = @sql + 'INSERT INTO ' + @table_name + '(' + @col_list + ') '
             + 'SELECT ' + @col_list + ' FROM ' + @source_db + @table_name
PRINT '复制表：' + @table_name
EXEC (@sql)

--得到下一个表名称
NEXT_TABLE:
FETCH NEXT FROM cur_tb INTO @table_name
END
CLOSE cur_tb
DEALLOCATE cur_tb

导入论坛引用链接收藏分享给好友推荐到圈子管理举报

TAG:

查看全部评论