關(guān)于數(shù)據(jù)遷移

情況：
1.實(shí)際的應(yīng)用中，我們會(huì)遇到數(shù)據(jù)庫某些表及表中部門列數(shù)據(jù)的遷移
2.筆者當(dāng)前按照自己的業(yè)務(wù)需求使用情況，初步測(cè)試了Mysql，postgresql及SQLServer相關(guān)的簡(jiǎn)單的數(shù)據(jù)遷移（備以后參考使用）
① mysql的相關(guān)語句

-- 移植單行
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;

//刪除存儲(chǔ)過程
DROP PROCEDURE IF EXISTS p2;
//調(diào)用存儲(chǔ)過程
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();

-- 不會(huì)報(bào)錯(cuò)，優(yōu)化后的數(shù)據(jù)移植語句樣本
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; --與上面的語句等價(jià)
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`;

② 關(guān)于postgresql數(shù)據(jù)庫的遷移
一、建環(huán)境
1.創(chuàng)建一個(gè)拓展（有該拓展postgresql才能建立遠(yuǎn)程數(shù)據(jù)訪問）
create extension dblink;
---------------- 使用前測(cè)試用------------------------
-- 建立跨庫連接

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 
-- 工號(hào)為空則不插入
   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');
     -- 循環(huán)寫入獲得數(shù)據(jù)
    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 
     -- 工號(hào)為空則不插入
      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;  
-------- 調(diào)用函數(shù) -------
select * from  p2();

備注：參考網(wǎng)址
③ SQLServer的數(shù)據(jù)遷移

DECLARE  @zk_userid int   --人員id
DECLARE  @zk_username varchar(200) --姓名
DECLARE  @zk_card varchar(50) --卡號(hào)
DECLARE  @kh_userid varchar(50)   --人員id
DECLARE  @kh_username varchar(200) --姓名
DECLARE  @kh_card varchar(50) --卡號(hào)
DECLARE  @zj_card varchar(50) --中間轉(zhuǎn)換
DECLARE  @r bigint  
DECLARE  @i bigint
DECLARE  @zk_pin int
DECLARE  @zk_deptid varchar(10) --部門號(hào)
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號(hào)樓' 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
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容