--------------------数据迁移：把旧数据库中的所有用户表数据插入到新库存中--------------------
--新数据库为：hydee
--旧数据库为：hdyee_old
use hydee;
go
set nocount on
SET QUOTED_IDENTIFIER OFF
declare @sql nvarchar(max)
declare @table_name char(30)
declare @table_id int
declare @col varchar(8000)
declare @where varchar(8000)
declare @count int = 0;
declare @is_same_out int = 0;
if OBJECT_ID('tempdb..#inserted_table_name') is null
create table #inserted_table_name(i int not null,table_name varchar(50) not null);
declare table_name cursor for
select row_number()over(order by name) as table_id,name
from hydee_old.sys.objects
where type = 'u' --and name = 'u_store_i'--'c_dddw'
order by name
open table_name
fetch next from table_name into @table_id,@table_name
WHILE @@FETCH_STATUS = 0
begin
if not exists(select * from hydee.sys.objects where name = @table_name and type = 'u')
begin
print cast(@table_id as char(7)) + @table_name + ' :新数据库中不存在该表' ;
goto _next;
end
--select @count = COUNT(*) from hydee_old.dbo.talbe_name;
if exists(select * from #inserted_table_name where table_name = @table_name)
begin
print cast(@table_id as char(7)) + @table_name + ' :该表已插入数据' ;
goto _next;
end
--【获取表（新数据库）的所有列（排除了 计算字段及自增字段）】
if OBJECT_ID('tempdb..#col') is not null drop table #col
select column_id,name,is_identity,is_computed
into #col
from sys.columns
where object_id = object_id(@table_name)
and is_computed = 0 --排除计算字段
and is_identity = 0 --排除自增字段
and name <> 'stamp' --排除时间戳字段
--【获取旧数据库中表的所有列（排除了 计算字段及自增字段）】
if OBJECT_ID('tempdb..#col_old_database') is not null drop table #col_old_database
set @sql = "
select column_id,name,is_identity,is_computed
into #col_old_database
from hydee_old.sys.columns
where object_id = object_id('hydee_old.dbo."+ @table_name +"')
and is_computed = 0
and is_identity = 0
and name <> 'stamp'
if exists(select * from #col nc full join #col_old_database oc on nc.name = oc.name where nc.name is null or oc.name is null)
begin
print '" + cast(@table_id as char(7)) + "'+ '" + @table_name + "' + '该表在两个数据库存中的字段不完全相同' ;
set @is_same_in = 1;
end
else
begin
set @is_same_in = 0;
end
";
--select @sql
--break;
exec sp_executesql @sql,
N'@is_same_in int output ',
@is_same_in = @is_same_out out;
if @is_same_out = 1 goto _next;
--将字段转换为行
set @col=
(
select ',' + name
from #col
for xml path('')
);
set @col = SUBSTRING(@col,2,8000);
--【获取表（新数据库）的主键列】
if OBJECT_ID('tempdb..#key') is not null drop table #key
select ic.column_id,c.name
into #key
from sys.indexes i
join sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
join sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
where i.is_primary_key = 1
and i.object_id = object_id(@table_name)
--条件
set @where =
(
select ' and a.' + name + ' = b.' + name
from #key
for xml path('')
)
set @where = SUBSTRING(@where,6,8000);
--set @sql = 'SET IDENTITY_INSERT hydee.dbo.' + @table_name + ' ON;';
set @sql = "declare @rowcount int = 0;" + CHAR(13) + CHAR(10) +
"disable trigger all on dbo." + @table_name + ";" + CHAR(13) +
"insert hydee.dbo." + @table_name + "(" +@col +") " + CHAR(13) +
"select " + @col + CHAR(13) +
"from hydee_old.dbo." + @table_name + " a" + CHAR(13) ;
if @where <> ''
begin
set @sql += "where not exists(select * from hydee.dbo." + @table_name +" b where " + @where + ");" + CHAR(13)
end
else
begin
set @sql = "truncate table hydee.dbo." + @table_name +";" + @sql;
end
set @sql +="select @rowcount = @@ROWCOUNT;" + CHAR(13) +
"print '" + cast(@table_id as char(7)) + @table_name + "'+'成功插入行数：'+ char(9) + cast(@rowcount as varchar(22));" +
"enable trigger all on dbo." + @table_name + ";";
--set @sql += 'SET IDENTITY_INSERT hydee.dbo.' + @table_name + ' OFF;';
exec(@sql);
--select @sql;
if not exists(select * from #inserted_table_name where table_name = @table_name)
begin
insert #inserted_table_name(i,table_name) select @table_id,@table_name;
end
--break;
_next:
fetch next from table_name into @table_id,@table_name
end
close table_name
deallocate table_name
select * from #inserted_table_name;
--drop table #inserted_table_name;
