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
/ * - 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 from the old database to the new library.
- Zou Jian 2003.10 (Please keep this information) - * /
/ * - Call example
EXEC P_COPYDB 'BNS_AA', 'BNS_NEW' EXEC P_COPYDB 'ACC_ 五 医', 'ACC_ Presentation Data 8' - * / CREATE P_COPYDB @ o_dbname sysname, - To copy data database - source database @n_dbname sysname - Receive data database - target database asdeclare @sql nvarchar (4000)
- Disable constraint / trigger to prevent data conflict in copy @ SQL = 'declare #tbc cursor for select name from' @ n_dbname '.. sysobjects where xtype =' 'u' 'and status> = 0'Exec (@SQL)
declare @tbname sysnameopen #tbcfetch next from #tbc into @tbnamewhile @@ fetch_status = 0begin set @ sql = 'alter table' @ n_dbname '.. [' @ tbname '] NOCHECK CONSTRAINT ALL' exec (@sql) set @ SQL = 'ALTER TABLE' @ n_dbname '] [' @ TBNAME '] disable trigger all' exec (@sql) fetch next from #tbc @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 '.. [' @ tbname ']) fetch next from #tbdeallocate # TB - Enable constraints Open #TBCFetch next from #TBC INTO @tbnamewhile @@ fetch_status = 0begin set @ sql = 'alter table' @ n_dbname '.. [' @ TBNAME '] check constraint all' exec (@sql) set @ SQL = 'alter table' @ n_dbname '.. [ ' @ TBNAME '] Enable Trigger All 'Exec (@SQL) fetch next from #tbc @tbnameEndClose #tbcdeallocate #tbcgo
