# MS Access DB 轉置 SQLite
###### tags: `基碼工作站`

### 使用工具
`DBeaver` 是一個SQL客戶端和資料庫管理工具。

### 原本 MS AccessDB

原始檔名為`serialLog.mdb`

#### table schema

#### data sample

### 新版 SQLite

原始檔名為`eCardDB.db`

#### table schema

#### data sample

### AccessDB 資料匯出

#### 匯出步驟
Step1:Export
Step2:選擇SQL
Step3:找到匯出的.sql檔案

#### SQL範例

INSERT INTO `Data` (`index`,COMPANYNO,`START`,`END`,CARDCOUNT,KEYUSED,SLOT) VALUES
	 (0,70759028,1,40,40,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (1,70759028,41,80,40,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (2,70759028,81,120,40,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (3,70759028,121,160,40,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (4,70759028,161,200,40,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (5,70759028,201,240,40,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (6,70759028,241,250,10,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (7,70759028,1001,1005,5,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (8,70759028,1006,1010,5,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (9,70759028,1011,1011,1,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0);
INSERT INTO `Data` (`index`,COMPANYNO,`START`,`END`,CARDCOUNT,KEYUSED,SLOT) VALUES
	 (10,70759028,90001,90010,10,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (11,70759028,90011,90012,2,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (12,70759028,40361,40374,14,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (13,70759028,40375,40381,7,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (14,70759028,40382,40441,60,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (15,70759028,40442,40493,52,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (16,70759028,40494,40528,35,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (17,70759028,40494,40528,35,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (18,70759028,40494,40528,35,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0),
	 (19,70759028,50001,50045,45,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,Se',0);

### SQLite 資料匯入

#### 匯入步驟
Step1:修正SQL
因為新版DB的Table名稱與欄位略有更動，所以需要修正指令。

INSERT INTO `CardRecord` (idx,COMPANYNO,`START`,`END`,CARDCOUNT,KEYUSED,SLOT) VALUES
	 (29,16744111,90000051,90000250,200,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,SeedKey00C6,SeedKey00CA',4),
	 (208,99787179,11000001,11000002,2,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,SeedKey00C6,SeedKey00CA',1),
	 (247,99787179,11000001,11000002,2,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,SeedKey00C6,SeedKey00CA',0),
	 (0,1820210,72000001,72000003,3,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,SeedKey00C6,SeedKey00CA',11),
	 (209,3741302,9001,9500,500,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,SeedKey00C6,SeedKey00CA',1),
	 (248,70759028,80465,80482,18,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,SeedKey00C6,SeedKey00CA',0),
	 (249,70759028,73739,74238,500,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,SeedKey00C6,SeedKey00CA',0),
	 (210,99787179,1091,1091,1,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,SeedKey00C6,SeedKey00CA',1),
	 (211,99787179,1106,1106,1,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,SeedKey00C6,SeedKey00CA',1),
	 (212,99787179,1085,1085,1,'SeedKey00C0,SeedKey00C1,SeedKey00C3,SeedKey00C4,SeedKey00C6,SeedKey00CA',1);

step2:執行SQL script

step3:重新整理，檢查資料