关于数据迁移

情况：
1.实际的应用中，我们会遇到数据库某些表及表中部门列数据的迁移
2.笔者当前按照自己的业务需求使用情况，初步测试了Mysql，postgresql及SQLServer相关的简单的数据迁移（备以后参考使用）
① mysql的相关语句

-- 移植单行
USE studentinfo;
DELIMITER //
CREATE PROCEDURE p2()
BEGIN
DECLARE id VARCHAR(10);
DECLARE NAME VARCHAR(10);
DECLARE sex VARCHAR(4);
DECLARE birthday VARCHAR(10);
DECLARE dept VARCHAR(10);
DECLARE major VARCHAR(10);
DECLARE classid VARCHAR(10);
DECLARE name_dept_cursor CURSOR FOR SELECT * FROM  stuinfo_table WHERE student_id='001';
OPEN name_dept_cursor;
FETCH name_dept_cursor INTO id,NAME,sex,birthday,dept,major,classid;
SELECT  id,NAME,sex,birthday,dept,major,classid;
CLOSE name_dept_cursor;
  BEGIN 
  INSERT INTO test.`stuinfo_table`(student_id,student_name,student_sex,student_birthday,student_dept,student_major,student_classid) VALUES(id,NAME,sex,birthday,dept,major,classid); 
  END;
END;

//删除存储过程
DROP PROCEDURE IF EXISTS p2;
//调用存储过程
CALL p2();


-- 移植多行
USE studentinfo;
DELIMITER //
CREATE PROCEDURE p2()
BEGIN
DECLARE id VARCHAR(10);
DECLARE NAME VARCHAR(10);
DECLARE sex VARCHAR(4);
DECLARE birthday VARCHAR(10);
DECLARE dept VARCHAR(10);
DECLARE major VARCHAR(10);
DECLARE classid VARCHAR(10);
DECLARE name_dept_cursor CURSOR FOR SELECT * FROM  stuinfo_table;
OPEN name_dept_cursor;
FETCH name_dept_cursor INTO id,NAME,sex,birthday,dept,major,classid ;
WHILE(id IS NOT NULL)
    DO
     INSERT INTO test.`stuinfo_table`(student_id,student_name,student_sex,student_birthday,student_dept,student_major,student_classid) VALUES(id,NAME,sex,birthday,dept,major,classid);   
     FETCH  name_dept_cursor INTO id,NAME,sex,birthday,dept,major,classid;
  END WHILE;
  CLOSE name_dept_cursor;
END;
DROP PROCEDURE IF EXISTS p2;
CALL p2();

-- 不会报错，优化后的数据移植语句样本
USE studentinfo;
DELIMITER //
CREATE PROCEDURE p2()
BEGIN
DECLARE id VARCHAR(10);
DECLARE NAME VARCHAR(10);
DECLARE sex VARCHAR(4);
DECLARE birthday VARCHAR(10);
DECLARE dept VARCHAR(10);
DECLARE major VARCHAR(10);
DECLARE classid VARCHAR(10);
DECLARE done BOOLEAN DEFAULT 0; 
DECLARE name_dept_cursor CURSOR FOR SELECT * FROM  stuinfo_table;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;  
-- DECLARE CONTINUE HANDLER FOR NOT FOUND  SET done = 1; --与上面的语句等价
OPEN name_dept_cursor;
FETCH name_dept_cursor INTO id,NAME,sex,birthday,dept,major,classid ;
  lp1:LOOP
      INSERT INTO test.`stuinfo_table`(student_id,student_name,student_sex,student_birthday,student_dept,student_major,student_classid) VALUES(id,NAME,sex,birthday,dept,major,classid);
      FETCH name_dept_cursor INTO id,NAME,sex,birthday,dept,major,classid ;     
      IF done!=0 THEN
   LEAVE lp1;
      END IF;
   END LOOP;      
  CLOSE name_dept_cursor;
END;
DROP PROCEDURE IF EXISTS p2;
CALL p2();
USE test 
SELECT student_id FROM `stuinfo_table`;

② 关于postgresql数据库的迁移
一、建环境
1.创建一个拓展（有该拓展postgresql才能建立远程数据访问）
create extension dblink;
---------------- 使用前测试用------------------------
-- 建立跨库连接

select dblink_connect('test_dblink2','dbname=security_db host=10.0.1.237 port=5432 user=root password=sa123');
select * from dblink('test_dblink2','SELECT pin,name,dept_id FROM pers_person') as data(pin VARCHAR(30),name VARCHAR(50),dept_id int)
LOOP 
-- 工号为空则不插入
   IF recordRow.pin is NOT NULL THEN
        INSERT INTO pers_person(pin,name,deptid) VALUES(recordRow.pin,recordRow.name,recordRow.dept_id); 
     END IF;
   END LOOP;
 PERFORM dblink_disconnect('test_dblink1');

---------------- 以下为真正使用sql------------------------

CREATE OR REPLACE FUNCTION p2()
RETURNS boolean AS 
$BODY$  
 DECLARE recordRow RECORD;
 DECLARE querySQlStr text;  
BEGIN
    querySQlStr:='SELECT pin,name,dept_id FROM pers_person';
    --  建立跨库连接
    PERFORM dblink_connect('test_dblink1','dbname=security_db host=10.0.1.237 port=5432 user=root password=sa123');
     -- 循环写入获得数据
    for recordRow in select * from dblink('test_dblink1','SELECT pin,name,dept_id FROM pers_person') as data(pin VARCHAR(30),name VARCHAR(50),dept_id int)  
     LOOP 
     -- 工号为空则不插入
      IF recordRow.pin is NOT NULL THEN
         INSERT INTO pers_person(pin,name,deptid) VALUES(recordRow.pin,recordRow.name,recordRow.dept_id); 
      END IF;
    END LOOP;
   PERFORM dblink_disconnect('test_dblink1');
 RETURN TRUE; 
END;
$BODY$   
LANGUAGE plpgsql VOLATILE 
COST 100;  
ALTER FUNCTION  p2()  
OWNER TO ROOT;  
-------- 调用函数 -------
select * from  p2();

备注：参考网址
③ SQLServer的数据迁移

DECLARE  @zk_userid int   --人员id
DECLARE  @zk_username varchar(200) --姓名
DECLARE  @zk_card varchar(50) --卡号
DECLARE  @kh_userid varchar(50)   --人员id
DECLARE  @kh_username varchar(200) --姓名
DECLARE  @kh_card varchar(50) --卡号
DECLARE  @zj_card varchar(50) --中间转换
DECLARE  @r bigint  
DECLARE  @i bigint
DECLARE  @zk_pin int
DECLARE  @zk_deptid varchar(10) --部门号
DECLARE  @j int
SET @zk_pin = 0

  DECLARE curDeviceCusror CURSOR
  FOR select GE.Base_Chn_Name,GE.Base_Dept_Code,GC.Base_Card_No from General_Employee as GE,General_Card_Distributed as GC where GE.Base_Employee_ID=GC.Base_Employee_ID
  FOR READ Only
  OPEN curDeviceCusror

  FETCH Next FROM curDeviceCusror INTO @kh_username,@zk_deptid,@kh_card
  WHILE(@@Fetch_Status=0)  
  BEGIN
IF @kh_card IS NOT NULL
  BEGIN
  --print @kh_card
  SET @zj_card=substring(@kh_card,7,1)
  SET @zj_card=@zj_card+substring(@kh_card,8,1)
  SET @zj_card=@zj_card+substring(@kh_card,5,1)
  SET @zj_card=@zj_card+substring(@kh_card,6,1)
  SET @zj_card=@zj_card+substring(@kh_card,3,1)
  SET @zj_card=@zj_card+substring(@kh_card,4,1)
  SET @zj_card=@zj_card+substring(@kh_card,1,1)
  SET @zj_card=@zj_card+substring(@kh_card,2,1)
  print @zj_card
  SET @r=0
  SET @i=1
      set @j=0
  SET @zk_pin=@zk_pin+1
  
 while @i<=len(@zj_card)
   begin

   set @r=@r+

   convert(bigint,
   (
   case 
   when substring(@zj_card,@i,1)<='9' then substring(@zj_card,@i,1)
   when substring(@zj_card,@i,1)<='A' then '10'
   when substring(@zj_card,@i,1)<='B' then '11'
   when substring(@zj_card,@i,1)<='C' then '12'
   when substring(@zj_card,@i,1)<='D' then '13'
   when substring(@zj_card,@i,1)<='E' then '14'
   when substring(@zj_card,@i,1)<='F' then '15'
   end
   ))
   *power(16,len(@zj_card)-@i)
   SET @i=@i+1

   END
  END
 SET @kh_card=@r
--print @kh_userid
--print @kh_username

print @kh_card
print @zk_pin   
print '----------------'
begin
    set @j=convert(int,(
      case 
  when @zk_deptid='0002' then 2
  when @zk_deptid='0003' then 3
      when @zk_deptid='0004' then 4
      when @zk_deptid='0005' then 5
      when @zk_deptid='0006' then 6
      when @zk_deptid='0007' then 7
      when @zk_deptid='0008' then 8
      when @zk_deptid='0009' then 9
      when @zk_deptid='001'  then 10
      when @zk_deptid='0010' then 11
      when @zk_deptid='0011' then 12
      when @zk_deptid='0016' then 13
      when @zk_deptid='0018' then 14
      when @zk_deptid='002' then 15
      when @zk_deptid='002773' then 16
      when @zk_deptid='01' then 17
      when @zk_deptid='02' then 18
      when @zk_deptid='03' then 19
      when @zk_deptid='04' then 20
      when @zk_deptid='05' then 21
      when @zk_deptid='06' then 22
      when @zk_deptid='07' then 23
      when @zk_deptid='1' then 24
      when @zk_deptid='1#A' then 25
      when @zk_deptid='10' then 26
      when @zk_deptid='11' then 27
      when @zk_deptid='11#' then 28
      when @zk_deptid='110' then 29
      when @zk_deptid='1-A' then 30
      when @zk_deptid='1号楼' then 31
      when @zk_deptid='500' then 32
      when @zk_deptid='55' then 33
      when @zk_deptid='9#' then 34
      end
    ))
end     
insert into zktk.dbo.userinfo(
                  badgenumber,name,card,defaultdeptid,status,
                  att,overtime,holiday,photo,offduty,deltag,set_valid_time,isatt
              ) values(@zk_pin,@kh_username,@kh_card,@j,0,1,1,1,'',0,0,0,1)


   --   ###########################################       
  FETCH Next FROM curDeviceCusror INTO @kh_username,@zk_deptid,@kh_card
  END
  Close curDeviceCusror
  Deallocate curDeviceCusror

最后编辑于：2018.02.23 15:42:10

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 195,783评论 5赞 462
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 82,360评论 2赞 373
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 142,942评论 0赞 325
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 52,507评论 1赞 267
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 61,324评论 5赞 358
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 46,299评论 1赞 273
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 36,685评论 3赞 386
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 35,358评论 0赞 254
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 39,652评论 1赞 293
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 34,704评论 2赞 312
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 36,465评论 1赞 326
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,318评论 3赞 313
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 37,711评论 3赞 299
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 28,991评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,265评论 1赞 251
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 41,661评论 2赞 342
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 40,864评论 2赞 335

关于数据迁移

推荐阅读更多精彩内容