上傳大檔到Azure Blob進行SQL的Bulk Insert

Download AzCopy and Unzip to C:(for Example)

下載網址：https://docs.microsoft.com/zh-tw/azure/storage/common/storage-use-azcopy-v10

注：Blob要先開啟Share的簽章設定:Shared access signature

azcopy make "https://xxxxxxx.blob.core.windows.net/dbfile?sv=2020-02-10&ss=bfqt&srt=sco&sp=rwdlacupx&se=2021-04-26T23:26:15Z&st=2021-04-26T15:26:15Z&spr=https&sig=1asdfasdbasdfasdbqasdfasdfasdf%3D"

azcopy.exe copy "C:\test.csv" "https://xxxxxxx.blob.core.windows.net/dbfile?sv=2020-02-10&ss=bfqt&srt=sco&sp=rwdlacupx&se=2021-04-26T23:26:15Z&st=2021-04-26T15:26:15Z&spr=https&sig=1asdfasdbasdfasdbqasdfasdfasdf%3D"

In Query Editor(Sql Azure)

--資料庫密碼
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!asdkfajd;skfjasldjkfalksdjflasd@' ;

--建立權杖(use Blob SAS Token)
CREATE DATABASE SCOPED CREDENTIAL AccessAzureSecret
WITH
  IDENTITY = 'SHARED ACCESS SIGNATURE',
  -- Remove ? from the beginning of the SAS token
  SECRET = 'sv=2020-02-10&ss=bfqt&srt=sco&sp=rwdlacupx&se=2021-04-26T23:44:32Z&st=2021-04-26T15:44:32Z&spr=https&sig=1asdfasdbasdfasdbqasdfasdfasdf%3D' ;

CREATE EXTERNAL DATA SOURCE dbFile
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'https://xxxxxxx.blob.core.windows.net/dbfile'
 , CREDENTIAL= AccessAzureSecret)


BULK INSERT [dbo].[learning_records] FROM 'stage_learning_record.csv' WITH (
    DATA_SOURCE = 'dbFile',
    FIELDTERMINATOR=',',rowterminator = '\n', KEEPNULLS);

註：
csv裡面，欄位是NULL的話，要先把欄位NULL取代成空字串(不是””), 若日期長度有問題的話，就也要先取代掉

若bulk insert執行發生超過10次錯誤，就會被中斷哦