缺乏匯入資料許可權，SQL Server建立測試資料

環境

SQL Server 2012 + CentOS 6.3

 

問題描述

只具有生產庫的登入、查詢、建立暫存資料表許可權，缺失匯入資料（比如Excel檔案、txt文檔、sql指令碼等等）許可權，需要建立暫存資料表，插入測試資料。

 

問題類比

由於生產庫的資料是敏感性資料，並且資料量非常大，當然不能提供出來。這裡只是對這個問題進行一個類比。資料量少和資料量大操作方法是一樣的。

 

問題解決

我們可以這樣：

 

這是Excel中的來源資料，1:

 

圖1 Excel 來源資料

Step 1，首先把來源資料（Excel中的資料）拷貝出來，或者另存新檔csv檔案（以逗號作為分隔），然後重新命名尾碼為txt。這裡的檔案名稱假設為source.txt，然後把行首標題去掉；

6789,Robin,朱二,成都1234,justdb,張三,瀘州4567,HelloWorld,李四,廣州5678,CSDN Blog,王五,中山1331,Wen,鄧六,深圳3142,Wentasy,徐七,長沙4131,Fantasy,燕八,昆明

 

Step 2，來源資料準備好了，那現在我們切換到Linux環境下開始對資料進行處理。觀察來源資料中有四列資料，那麼我們需要分隔資料。這裡採用awk處理。代碼如下：

#-F表示以逗號作為分隔，把來源資料中的每列分別儲存為新的四個檔案awk -F","'{print $1}' source.txt > source1.txtawk -F","'{print $2}' source.txt > source2.txtawk -F","'{print $3}' source.txt > source3.txtawk -F","'{print $4}' source.txt > source4.txt

來源資料如下：

[root@robin opt]# cat source.txt

1234,justdb,張三,瀘州4567,HelloWorld,李四,廣州5678,CSDN Blog,王五,中山1331,Wen,鄧六,深圳3142,Wentasy,徐七,長沙4131,Fantasy,燕八,昆明

操作結果：

[root@robin opt]# cat source1.txt

123445675678133131424131

 

效果2：

 

圖2 Step 2

Step 3，考慮到這些資料都是基於文本儲存的，那麼INSERT插入時需要在值的首尾加上單引號或者雙引號。代碼如下：

#^表示行首，此行代碼錶示在每行的行首加上yy，注意此處添加的內容不要和本文相同；sed 's/^/yy/g'source1.txt –i#$表示行尾，此行代碼錶示在每行的行尾加上zz，同理，意此處添加的內容不要和本文相同sed 's/$/zz/g'source1.txt –i#把行首的yy替換成單引號sed"s/yy/\'/g" source1.txt –i#把行尾的zz替換成單引號sed"s/zz/\'/g" source1.txt –i #說明：讀者也可以把行尾和行首替換為相同的內容，那把替換後的內容再替換為單引號就只需要執行一行代碼即可。#這裡只示範一個文本，其餘文本操作方法相同。

 

操作結果如下：

[root@robin opt]# cat source1.txt

yy1234zzyy4567zzyy5678zzyy1331zzyy3142zzyy4131zz

 

[root@robin opt]# cat source1.txt

'1234''4567''5678''1331''3142''4131'

 

效果3：

 

圖 3 Step 3

Step 4，我們得到每列帶單引號的文本，但是我們需要把這四個檔案的每列放到一個檔案中，就像炒青椒肉絲，把切好的瘦肉絲、佐料、青椒放到鍋裡炒一樣。我們可以採用如下方法合并檔案，使用paste命令，命令如下：

#此命令表示以逗號作為分隔，合并經過上述處理的四個檔案，並儲存到結果檔案paste -d ","source1.txt source2.txt source3.txt source4.txt > result.txt

操作結果如下：

[root@robin opt]# cat result.txt

'1234','justdb','張三','瀘州''4567','HelloWorld','李四','廣州''5678','CSDN Blog','王五','中山''1331','Wen','鄧六','深圳''3142','Wentasy','徐七','長沙''4131','Fantasy','燕八','昆明'

 

效果4：

 

圖4 Step 4

Step 5，將得到的結果進行最後的處理。我們在行尾加入INSERT語句，這裡假設後面建立的暫存資料表名稱為##temp，在行尾加上括弧和分號，語句如下：

sed 's/^/INSERT INTO ##tempVALUES(/g' result.txt -ised 's/$/);/g'result.txt -i

 

操作結果如下：

[root@robin opt]# cat result.txt

INSERT INTO ##temp VALUES('1234','justdb','張三','瀘州');INSERT INTO ##temp VALUES('4567','HelloWorld','李四','廣州');INSERT INTO ##temp VALUES('5678','CSDN Blog','王五','中山');INSERT INTO ##temp VALUES('1331','Wen','鄧六','深圳');INSERT INTO ##temp VALUES('3142','Wentasy','徐七','長沙');INSERT INTO ##temp VALUES('4131','Fantasy','燕八','昆明');

 

效果5：

 

圖5 Step 5

Step 6，建立暫存資料表，語句如下：

CREATE TABLE ##temp(       ID CHAR(16) NOT NULL,       EName VARCHAR(20),       CName VARCHAR(40),       City VARCHAR(20));

Step 7，開啟SQLServer的查詢分析器，然後執行建立暫存資料表的語句和插入資料的語句。

執行結果6：

 

圖6 插入資料效果

其他說明

1.如果檔案中每行的末尾出現空格，我們可以使用此命令把空格去掉：

sed 's/\ \+$//'source1.txt –i

2.如果檔案中出現^M，我們可以使用此命令將^M去掉：

sed 's/^M//g'source_4.txt –i

3.本文只是簡單的類比，資料量小不能體現這種方法的優越性，如果資料量大，那給你帶來的是質的飛躍；

4.本文中Step3可以簡化，直接在每列的行首和行尾加入INSERT和括弧，但是這樣只是行首和行尾OK了，每個字串還是沒有用單引號括起來，可以把每行作為一個單元，然後加入單引號，而不是本文的將每個列分隔出來；

5.本文還想告訴讀者的是多使用Linux吧，並且學會一門指令碼語言，這會讓你的工作事半功倍；

6.本文是基於沒有匯入資料的許可權的情況下做的，如果有該許可權，自然很簡單，如果沒有，那本文還是很有參考價值。其實本文提供的就是一種思路，如何把問題拆分、如何巧妙的拼接文本。使用到的核心技術就是Linux的Shell，比如awk、sed的用法。

 

最終的一鍵指令碼

#!/bin/bash#FileName:auto_import_data.sh#Desc:Auto Import DataTo MS SQL#Date:2014-3-14 17:53:12#Author:Robin #1.分離資料awk -F","'{print $1}' source.txt > source1.txtawk -F","'{print $2}' source.txt > source2.txtawk -F","'{print $3}' source.txt > source3.txtawk -F","'{print $4}' source.txt > source4.txt #2.在行首和行尾添加單引號sed 's/\ \+$//'source1.txt -ised 's/^/yy/g'source1.txt -ised 's/$/zz/g'source1.txt -ised"s/yy/\'/g" source1.txt -ised"s/zz/\'/g" source1.txt -i sed 's/^/yy/g'source2.txt -ised 's/$/zz/g'source2.txt -ised "s/yy/\'/g"source2.txt -ised"s/zz/\'/g" source2.txt -i sed 's/^/yy/g'source3.txt -ised 's/$/zz/g'source3.txt -ised"s/yy/\'/g" source3.txt -ised"s/zz/\'/g" source3.txt -i sed 's/^/yy/g'source4.txt -ised 's/$/zz/g'source4.txt -ised "s/yy/\'/g"source4.txt -ised"s/zz/\'/g" source4.txt -i #3.合并檔案paste -d ","source1.txt source2.txt source3.txt source4.txt > result.txt #4.拼接為最終的插入語句sed 's/^/INSERT INTO ##tempVALUES(/g' result.txt -ised 's/$/);/g'result.txt -i

  我的郵箱：wgbno27@gmail.com  新浪微博：@jutdb           公眾平台：JustOracle（號：justoracle）  資料庫技術交流群：336882565（加群時驗證 From CSDN XXX）  All is well  2014年3月15日  By Robin Wen

	@Wentasy 博文僅供參考，歡迎大家來訪。如有錯誤之處，希望批評指正。原創博文如需轉載請註明出處，謝謝 :) [CSDN部落格]