mysql to sql sersver

USE SCK_PARA
GO
/****** Object:  StoredProcedure [dbo].[syncdata_mysql2sqlserver]    Script Date: 08/19/2015 13:26:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,> -- Description: <Description,,>
-- ============================================= ALTER PROCEDURE [dbo].[syncdata_mysql2sqlserver]
CREATE PROCEDURE [dbo].[syncdata_mysql2sqlserver]
AS
declare @sql nvarchar(4000);
declare @sql2 nvarchar(4000);
declare @sql3 nvarchar(4000);
declare @columns nvarchar(4000);
declare @columns2 nvarchar(4000);
declare @identity int;
BEGIN  --声明一个游标,查询数据库的所有表名
 declare tableNameCursor cursor for SELECT name FROM SysObjects Where XType='U' ORDER BY Name
 --打开
 open tableNameCursor    
 --声明一个变量,用于读取游标中的值  
declare @tableName varchar(200)     
         fetch next from tableNameCursor into @tableName            --循环读取
         
         while @@fetch_status=0     
         begin
         --开启显示插入标识值的sql    
            set @sql2 = 'set IDENTITY_INSERT '+@tableName+' ON';              --关闭显示插入标识值的sql
            set @sql3 = 'set IDENTITY_INSERT '+@tableName+' OFF';    --查询出该表包含的字段名并拼接
 
            select @columns = stuff((select ',"'+COLUMN_NAME+'"'
            from INFORMATION_SCHEMA.columns
            WHERE TABLE_NAME=@tableName order by ORDINAL_POSITION
            for xml path('')),1,1,'')  
            select @columns2 = stuff((select ','+COLUMN_NAME
            from INFORMATION_SCHEMA.columns
            WHERE TABLE_NAME=@tableName
            order by ORDINAL_POSITION
            for xml path('')),1,1,'')    
            Select @identity=OBJECTPROPERTY(OBJECT_ID(@tableName),'TableHasIdentity')
    
            print @tableName
   --有标识列    
   if @identity=1     
   begin      
   set @sql = @sql2 +' insert into '+ @tableName +'('+@columns+') select * from OPENQUERY (MySQL ,''select '+@columns2+' from '+ @tableName+''' )';
    exec(@sql)      
    exec(@sql3)
    
end
   --没有标识列
   else    
     begin   
        set @sql = ' insert into '+ @tableName +'('+@columns+') select * from
        OPENQUERY (MySQL ,''select * from '+ @tableName+''' )';      
        exec(@sql)  
    end
   
fetch next from tableNameCursor into @tableName
         end       
     close tableNameCursor    --关闭游标       
     deallocate tableNameCursor    --删除游标  
END

posted on 2016-08-18 15:54  jimmyfan  阅读(304)  评论(0编辑  收藏  举报

导航