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