如何快速將正式機資料表匯入到測試機的資料表呢?

今天同事發一封信給我，因為他要在測試環境進行測試，由於測試環境沒有資料無法模擬，需要請客戶從正式機匯出六個資料表匯入到測試環境內的資料表內，由於正式機的資料表與測試機的table schema一模一樣，所以應該很簡單才對，結果客戶寄發Email告知6個檔案只有2個匯入OK，其他都失敗了。詢問原因及如何解決。

執行方式是使用SQL Server的Bulk Insert

發生異常的訊息如下所示

由以上執行SQL語法得知，他是從正式機將資料表匯出成CSV檔案，然後在測試環境執行Bulk Insert，但從錯誤訊息會發生以上原因有可能是有些欄位因為有逗號，造成欄位偏移，所以資料格式皆不正確，所以我跟同事說這樣的方法會有問題，我有兩種方法如下可以解決，

方法一：採用bcp 方式匯出與匯入

方法二：從正式機產生insert SQL語法然後在測試環境執行

底下我將說明我使用的方法

方法一：在測試環境中的資料庫執行底下sql語法，針對有資料筆數且忽略log的資料表產生bcp out及bcp in以及truncate table 的語法(這些資料表過濾條件皆可以自行修改)

1.執行底下語法

select * ,’bcp “‘+ schemaname+’.’+ tablename+'" out “%5\’+schemaname+’.’+ tablename+’.dat" -n -q -E  -S"%1″ -d"%2″ -U"%3″ -P"%4″‘ bcp_out ,’truncate table ‘ + schemaname+’.’+ tablename truncate_table ,’bcp “‘+ schemaname+’.’+ tablename+'" IN “%5\’+schemaname+’.’+ tablename+’.dat" -n -q -E  -S"%1″ -d"%2″ -U"%3″ -P"%4″‘ bcp_in from comm.vw_table_row_count_ext where    row_count>0 and tablename not like ‘%_log’

2.將bcp_out欄位資料複製後貼上記事本內，將檔名存成bcp_out.bat  3.開啟命令列視窗，輸入c:\>bcp_out.bat localhost sbp_db2 sbp_user xxxxx c:\bcp_out 4.複製truncate table欄位內容在測試環境執行 5.複製bcp in 欄位語法貼到記事本儲存檔名bcp_in.bat 6.在命令列視窗輸入底下語法匯入資料到測試環境資料庫內 c:\>bcp_in.bat localhost sbp_db2_target sbp_user xxx c:\bcp_out 方法二：從正式機產生要匯出的資料表insert SQL語法後，在到測試環境執行 1.先在測試環境的資料表中執行底下語法 select [comm].[fn_gen_insert_value_ext_sql] (schemaname,tablename,",0) insert_sql from comm.vw_table_row_count_ext where    row_count>0 and tablename not like ‘%_log’ 2.將執行結果複製貼上ssms上儲存後在正式機環境執行一次(若有資料表只需要部分資料，可以新增where條件過濾)。 3.將每一個執行結果複製後貼上ssms儲存後，再到測試環境執行即可。