Database Data Replication

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 data from one database to another
If a column is an identifier column in the target database, it will not be copied.

Applicability: the database structure has changed and you want to upgrade the old database.
In this way, you can create an empty database based on the new database structure, and then
Copy all data from the old database to the new database.

-- Producer build 2003.10 (reference please keep this information )--*/

/* -- Call example

Exec p_copydb 'bns _ a', 'bns _ new'
Exec p_copydb 'Acc _ Wuzhi ', 'Acc _ demo data 8'
--*/
Create proc p_copydb
@ O_dbname sysname, -- database for Data Replication -- source database
@ N_dbname sysname -- database for receiving data -- target database
As
Declare @ SQL nvarchar (4000)

-- Disable constraints/triggers to prevent data conflicts during replication
Set @ SQL = 'Clare # TBC cursor for select name
From '+ @ n_dbname +' .. sysobjects where xtype = ''u'' and status> = 0'
Exec (@ SQL)

declare @ tbname sysname
open # TBC
fetch next from # TBC into @ tbname
while @ fetch_status = 0
begin
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 into @ tbname
end
close # TBC

-- Copy Data
declare @ sql1 varchar (8000)
set @ SQL = 'descare # TB cursor for select. name from '
+ @ o_dbname + '.. sysobjects a inner join '
+ @ n_dbname + '.. sysobjects B on. name = B. name
where. xtype = ''u'' and B. xtype = ''u''
exec (@ SQL)
open # TB
fetch next from # TB into @ tbname
while @ fetch_status = 0
begin
select @ sql1 = ''< BR>, @ SQL = 'select @ sql1 = @ sql1 + '', ['' +. name + '']'' from (
select name from '+ @ o_dbname + '.. syscolumns 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 = ''' + @ tbname + ''')
) B on. 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 # TB into @ tbname
End
Close # TB
Deallocate # TB

-- Enable constraints after data replication is complete
Open # TBC
Fetch next from # TBC into @ tbname
While @ fetch_status = 0
Begin
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 into @ tbname
End
Close # TBC
Deallocate # TBC
Go