alter procedure [dbo].[xxxx](
@newDatabaseName varchar(500), -- cn new database
@oldDatabaseName varchar(500)--us database
)
as
begin
/*TEST DATA
set @newDatabaseName='[eData_wqlNew]';
set @oldDatabaseName='[eData_wql]';
*/
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#eTables') and type='U')
begin
print 'EXSITES'
drop table #eTables;
end
create table #eTables
(
id [int] IDENTITY(1,1) NOT NULL,
name varchar(250) null
)
insert into #eTables select name from sysobjects where XTYPE='u' and name like 'e%'
select * from #eTables
declare @delSql varchar(max);
declare @insertSql varchar(max);
declare @newTableName varchar(500);
declare @oldTableName varchar(500);
declare @minId int,@maxId int;
set @minId=(select min(id) from #eTables)
set @maxId=(select max(id) from #eTables)
while (@minId<=@maxId)
begin
declare @tableName varchar(500);
select @tableName=name from #eTables where id=@minId;
print 'no.'+convert(varchar(500),@minId)+' '+convert(varchar(500),@tableName)
set @minId=@minId+1;
print @minId;
set @newTableName=@newDatabaseName+'.dbo.'+@tableName;
print '@newTableName:'+@newTableName;
set @oldTableName=@oldDatabaseName+'.dbo.'+@tableName;
print '@oldTableName:'+@oldTableName;
begin tran A --begin tran
set @delSql='delete from '+@newTableName;
print '@delSql:'+@delSql;
execute(@delSql);
--1\lock table(share)
--2\whether has identity
--3\open identity column insert(if has)
--4\table insert
--5\close identity column insert(if has)
--?6\think of the case that the table struture changed
declare @collist nvarchar(max);
declare @colSqL nvarchar(max);
--get the table col list(not think of 6)
/*set @colSqL='set @collist=(select name+'','' from sys.columns where object_id=OBJECT_ID('''+@oldTableName+''') for xml path(''''))';
print '@colSql:'+@colSql;
exec sp_executesql @colSqL, N'@collist varchar(max) output', @collist output
select @collist=substring(@collist,1,len(@collist)-1);
print '+@newTableName+' where 1=2');
execute('select * into ##oldColInfo from '+@oldTableName+' where 1=2');
--select newCol.name from (select name from tempdb..syscolumns where id=OBJECT_ID('tempdb..#newColInfo')) newCol join (select name from tempdb..syscolumns where id=OBJECT_ID('tempdb..#oldColInfo')) oldCol on newCol.name=oldCol.name
--set @colSqL='set @collist=(select name+'','' from sys.columns where object_id=OBJECT_ID('''+@oldTableName+''') for xml path(''''))';
--get the same col info
set @colSqL='set @collist=(select newCol.name+'','' from (select name from tempdb..syscolumns where id=OBJECT_ID(''tempdb..##newColInfo'')) newCol join (select name from tempdb..syscolumns where id=OBJECT_ID(''tempdb..##oldColInfo'')) oldCol on newCol.name=oldCol.name for xml path(''''))';
print '@colSql:'+@colSql;
exec sp_executesql @colSqL, N'@collist varchar(max) output', @collist output
select @collist=substring(@collist,1,len(@collist)-1);
print '@collist:'+isnull(@collist,'null');
--get the table has identity
declare @hasIdentity varchar(50);
Select @hasIdentity=OBJECTPROPERTY(OBJECT_ID( ''+@newTableName+''), 'TableHasIdentity ');
print '@hasIdentity:'+isnull(@hasIdentity,'null');
set @insertSql='select * from '+@newTableName+' with (TABLOCK);';--deny other trans to /insert/updata data;
if(@hasIdentity='1')
begin
set @insertSql+='set identity_insert '+ @newTableName +' on;';
end
set @insertSql+='insert into '+@newTableName+'('+@collist+')'+' select '+@collist+' from '+@oldTableName+';';
if(@hasIdentity='1')
begin
set @insertSql+='set identity_insert '+ @newTableName +' off;';
end
print '@insertSql:'+isnull(@insertSql,'null');
execute(@insertSql);
if @@error<>0 --has error
begin
rollback tran A--rollback
return;
--print convert(varchar(500),@minId)+'trans';
end
commit tran A
end
end