--------------------數(shù)據(jù)遷移：把舊數(shù)據(jù)庫中的所有用戶表數(shù)據(jù)插入到新庫存中--------------------
--新數(shù)據(jù)庫為：hydee
--舊數(shù)據(jù)庫為：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 + '    :新數(shù)據(jù)庫中不存在該表' ;
        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 + '    :該表已插入數(shù)據(jù)' ;
        goto _next;        
    end
    
    --【獲取表（新數(shù)據(jù)庫）的所有列（排除了 計算字段及自增字段）】
    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'        --排除時間戳字段
    
    --【獲取舊數(shù)據(jù)庫中表的所有列（排除了 計算字段及自增字段）】
    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 + "' + '該表在兩個數(shù)據(jù)庫存中的字段不完全相同' ;
        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);    
    --【獲取表（新數(shù)據(jù)庫）的主鍵列】
    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 + "'+'成功插入行數(shù)：'+ 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;