导数据

从Erp库中把数据导入到 BanChe56 的库。 写一个存储过程。

 

CREATE proc [dbo].[Dev_MoveTableData]( @ToTable varchar(50), @FromTable varchar(50) , @useId bit , @IDFrom int ,@execute bit ) as begin

declare @sql nvarchar(4000);
declare @columns varchar(500);

if( @FromTable is null) set @FromTable = @ToTable;

set @sql = N'select * from Erp.dbo.[' + @FromTable + ']' ;

exec sp_executesql @sql;

set @sql = '';

if( @useId = 1) begin
    set @sql = N'
set identity_insert BanChe56.dbo.[$ToTable$] on
insert into BanChe56.dbo.[$ToTable$] ($AllColumns$) 
select $AllColumns$ from Erp.dbo.[$FromTable$] where ID > $IDFrom$
set identity_insert BanChe56.dbo.[$ToTable$] off
' ;


    select @columns = '[' + dbo.JoinStr2(name, '],[') + ']' from sys.columns where object_id = object_id(@ToTable) and is_computed = 0 ;

    set @sql = REPLACE(@sql,'$AllColumns$',@columns) ;
    
end else begin
    set @sql = N'
insert into BanChe56.dbo.[$ToTable$] ($Columns$) 
select $Columns$ from Erp.dbo.[$FromTable$] where ID > $IDFrom$
' ;

    select @columns = '[' + dbo.JoinStr2(name, '],[') + ']' from sys.columns where object_id = object_id(@ToTable)  and is_computed = 0 and is_identity = 0;

    set @sql = REPLACE(@sql,'$Columns$',@columns) ;
end 

set @sql = replace(@sql,'$FromTable$',@FromTable);
set @sql = replace(@sql,'$ToTable$',@ToTable);
set @sql = replace(@sql,'$IDFrom$', cast( @IDFrom as varchar(50)) );
 
    print @sql ;
    
    if( @execute =1 ) begin
        exec  sp_executesql @sql ;
    end ;

end ;
GO

 

 检查有数据的表：

SELECT a.name FROM sys.sysobjects AS a
WHERE EXISTS(

SELECT 1 FROM sys.sysindexes AS x
 WHERE x.id=a.id  AND x.rows>0 )
 AND a.type='U'