结构相同的数据库之间复制数据

[sql]代码库

use old
go
DECLARE NoCheckConstraintAll CURSOR READ_ONLY FOR
  SELECT sst.name, 
         Schema_name(sst.schema_id) 
  FROM   sys.tables sst 
  WHERE  sst.TYPE = 'U'
DECLARE @name   VARCHAR(80), 
        @schema VARCHAR(40)
 
OPEN NoCheckConstraintAll
 
FETCH NEXT FROM NoCheckConstraintAll INTO @name, @schema
 
WHILE ( @@FETCH_STATUS <> -1 ) 
  BEGIN
      IF ( @@FETCH_STATUS <> -2 ) 
        BEGIN
                DECLARE @sql NVARCHAR(1024) 
SET @sql=' ALTER TABLE ' + Quotename(@schema) 
                           + 
                           '.' + Quotename(@name)+' NOCHECK CONSTRAINT ALL'
                  EXEC Sp_executesql @sql 
        END
 
      FETCH NEXT FROM NoCheckConstraintAll INTO @name, @schema
  END
 
CLOSE NoCheckConstraintAll
 
DEALLOCATE NoCheckConstraintAll
 
GO
 
DECLARE CopyTables CURSOR READ_ONLY FOR
  SELECT sst.name, 
         Schema_name(sst.schema_id) 
  FROM   sys.tables sst 
  WHERE  sst.TYPE = 'U'
DECLARE @name   VARCHAR(80), 
        @schema VARCHAR(40)
 
OPEN CopyTables
 
FETCH NEXT FROM CopyTables INTO @name, @schema
 
WHILE ( @@FETCH_STATUS <> -1 ) 
  BEGIN
      IF ( @@FETCH_STATUS <> -2 ) 
        BEGIN
                DECLARE @sql NVARCHAR(1024) 
SET @sql='INSERT INTO [new].' + Quotename(@schema) 
                           + 
                           '.' + Quotename(@name)+' SELECT * FROM '+ Quotename(@schema) 
                           + 
                           '.' + Quotename(@name)
                  EXEC Sp_executesql @sql 
        END
 
      FETCH NEXT FROM CopyTables INTO @name, @schema
  END
 
CLOSE CopyTables
 
DEALLOCATE CopyTables
 
GO
DECLARE CheckConstraintAll CURSOR READ_ONLY FOR
  SELECT sst.name, 
         Schema_name(sst.schema_id) 
  FROM   sys.tables sst 
  WHERE  sst.TYPE = 'U'
DECLARE @name   VARCHAR(80), 
        @schema VARCHAR(40)
 
OPEN CheckConstraintAll
 
FETCH NEXT FROM CheckConstraintAll INTO @name, @schema
 
WHILE ( @@FETCH_STATUS <> -1 ) 
  BEGIN
      IF ( @@FETCH_STATUS <> -2 ) 
        BEGIN
                DECLARE @sql NVARCHAR(1024) 
SET @sql=' ALTER TABLE ' + Quotename(@schema) 
                           + 
                           '.' + Quotename(@name)+' CHECK CONSTRAINT ALL'
                  EXEC Sp_executesql @sql 
        END
 
      FETCH NEXT FROM CheckConstraintAll INTO @name, @schema
  END
 
CLOSE CheckConstraintAll
 
DEALLOCATE CheckConstraintAll
 
GO