/ * - Database data replication
Copy the data in a database to another database If a column is identified in the target database, it will not be copied.
Scope of application: The database structure changes, you want to upgrade the old database so you can create an empty space according to the new database structure, then copy all the data of the old database to the new library - * /
/ * - Call example
EXEC P_COPYDB 'Source Database', 'Target Database' EXEC P_COPYDB 'ACC_ 五 医', 'ACC_ Demo Data 8' - * /
if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_copydb]') And ObjectProperty (id, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_copydb] Go
Create proc p_copydb @ o_dbname sysname, - To copy data database - source database @n_dbname sysname, - Receive data database - Target Database @cleardB bit = 0 - Clear Target Database AsDeclare @SQL NVARCHAR (4000)
- Disable constraints, prevent data conflict in the copy @ SQL = 'declare #TBC CURSOR for SELECT NAME, TBNAME = Object_name (PARENT_OBJ) from' @ n_dbname '.. sysobjects where xtype in (' 'c', ' 'F' ')' exec (@sql) declare @name sysname, @ tbname sysnameopen #tbcfetch next from #tbc into @ name, @ tbnamewhile @@ fetch_status = 0begin set @ sql = 'alter table' @ n_dbname '.. [' @ TBNAME '] NOCHECK CONSTRAINT [' @ Name '] 'EXEC (@SQL) Fetch next from #tbc @ name, @ TBNameEndClose #TBC
- Copy Data Declare @ SQL1 VARCHAR (8000) SET @ SQL = 'Declare #TB CURSOR for SELECT A.NAME FROM' @ o_dbname '.. sysobjects a inner join' @ n_dbname '.. sysobjects b on a.name = B.Name where a.xtype = 'u' 'and b.XTYPE =' 'u' 'EXEC (@SQL) open #tbfetch next from # @@ @@ fetch_status = 0begin select @ SQL1 =' ', @ SQL =' SELECT @ SQL1 = @ SQL1 '', ['' a.name ''] '' from (SELECT NAME FROM '.. syscolumn where id in (select id from " @ @ o_dbname '.. sysobjects where name =' '' @ tbname '' ')) a inner join (select name from' @ n_dbname '.. syscolumns where status <> 0x80 and id in (select id from' @ n_dbname '..sysObjects where name =' '')) b on a.name = B.Name 'exec sp_executesql @ SQL, N' @ SQL1 NVARCHAR (4000) OUT ', @ SQL1 OUT
SELECT @ SQL1 = Substring (@ SQL1, 2, 8000) EXEC ('INSERT INTO' @ n_dbname '.. [' @ tbname '] (' @ SQL1 ') SELECT' @ SQL1 'from' @ o_dbname '.. [' @ TBNAME ']') IF @@ error <> 0 print ('INSERT INTO' @ n_dbname '.. [' @ TBNAME '] (' @ SQL1 ') SELECT' @ SQL1 'from' @ o_dbname ']) Fetch next from #tb @tbnamendclose #tbdeallocate # TB - Enable Constrained Open #Tbcfetch next from #tbc @name after Data Copy , @ tbnamewhile @@ fetch_status = 0begin set @ sql = 'alter table' @ n_dbname '.. [' @ tbname '] CHECK CONSTRAINT [' @ name ']' exec (@sql) fetch next from #tbc into @ Name, @ TBNameEndClose #tbcdeallocate #tbcgo
