bulk insert 在mssql中使用

 

從遠端資料檔案中批量匯入

若要使用 BULK INSERT 從其他計算機中大容量匯入資料，必須在兩臺計算機之間共享資料檔案。 指定共享資料檔案時，請使用它的通用命名約定 (UNC) 名稱，其一般形式為 \\伺服器名\共享名\路徑\檔名。 此外，用來訪問該資料檔案的帳戶必須具有讀取遠端磁碟上的檔案所需的許可權。

首先 共享遠端伺服器資料夾 並設定為所有權

以下附上程式碼：

BULK INSERT tabletest
        FROM 'D:\\20170629.txt'
        WITH(
         FIRSTROW=1,
         FIELDTERMINATOR = ',', --使用“，”作為列分隔符
         ROWTERMINATOR = '\n', --使用“\n”作為行分隔符
         KEEPNULLS ,   --如果有些Column沒有值，設定 KEEPNULLS  選項，表示將該column設定為NULL
         check_constraints
)
           
SELECT * FROM tabletest

附上儲存過程的實現：

procedure [dbo].[loadData]
(@p_pathname varchar(1200))
as
begin
set nocount off;
declare @filename varchar(1200)
declare @stsql nvarchar(max)if (@p_pathname='' or @p_pathname is null)
    return;    
set @filename=@p_pathname;
set @stsql='BULK INSERT tabletest '
            +' FROM '''+@filename+''''
            +' WITH('
            +'  FIRSTROW=1,'
            +'  FIELDTERMINATOR = '','','--使用“，”作為列分隔符
            +'  ROWTERMINATOR = ''\n'','--使用“\n”作為行分隔符
            +'  KEEPNULLS ,  '     --如果有些Column沒有值，設定 KEEPNULLS   選項，表示將該column設定為NULL
            +'  check_constraints'
            +' )';
    /*
    print(@stsql);
    */
exec sp_executesql
         @statemnet=@stsql;                      
set nocount on;
end;

 下面是執行遠端伺服器網路上的檔案

BULK INSERT  table_test
        FROM '\\遠端伺服器名\\共享檔案files\\20170727.txt'
        WITH(
        KEEPIDENTITY,
         FIRSTROW=1,
         FIELDTERMINATOR = ',', --使用“，”作為列分隔符
         ROWTERMINATOR = '\n', --使用“\n”作為行分隔符
         KEEPNULLS ,   --如果有些Column沒有值，設定 KEEPNULLS  選項，表示將該column設定為NULL
         check_constraints
)

儲存過程實現 傳遞檔案路徑的方法以及給伺服器共享檔案路徑賦值

procedure [dbo].[loadData]
(@p_pathname varchar(1200))
as
begin
set nocount off;
declare @filename varchar(1200)
declare @stsql nvarchar(max)if (@p_pathname='' or @p_pathname is null)
    return;    

--如果是遠端網路伺服器上的檔案就採用此路徑傳值和賦值 賦予共享的遠端伺服器上的檔案路徑 
-- 程式檔案部署在那一臺伺服器就要用那一臺的名稱
 
 --去掉前兩位路徑D:  傳值路徑D:\\20170629.txt
set @filename='\\遠端檔案伺服器名+SUBSTRING(@p_pathname,3,len(@p_pathname)-2);
set @stsql='BULK INSERT tabletest '
            +' FROM '''+@filename+''''
            +' WITH('
            +'  FIRSTROW=1,'
            +'  FIELDTERMINATOR = '','','--使用“，”作為列分隔符
            +'  ROWTERMINATOR = ''\n'','--使用“\n”作為行分隔符
            +'  KEEPNULLS ,  '     --如果有些Column沒有值，設定 KEEPNULLS   選項，表示將該column設定為NULL
            +'  check_constraints'
            +' )';
    /*
    print(@stsql);
    */
exec sp_executesql
         @statemnet=@stsql;                      
set nocount on;
end;

 完整的 導資料流程儲存實現

 procedure [dbo].[dataload]
(
 @p_pathname varchar(1200)
)
as
begin
set nocount off;
declare @filename varchar(1200)

declare @stsql nvarchar(max)
declare @stsqlinsert nvarchar(max)
declare @stsqlDeleteTmp nvarchar(max)
declare @params nvarchar(max)
declare @stsqlReplace nvarchar(max)

if (@p_pathname='' or @p_pathname is null)
    return;
------------------------------------------------------------------------------------------------------
--如果是本地就採用此路徑傳值 D:\\files\\pay_cfm\\db_0301_20170727.txt
--set @filename=@p_pathname;
-----------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
--如果是遠端網路伺服器上的檔案就採用此路徑傳值和賦值 賦予共享的遠端伺服器上的檔案路徑    
--\\P-2017072U\\szrpp_files\\pay_cfm\\db_0301_20170727.txt 程式檔案部署在那一臺伺服器就要用那一臺的名稱
 --select substring('D:\\files\\pay_cfm\\db_0201_20170721.txt',3,len('D:\\files\\pay_cfm\\db_0201_20170721.txt')-2)
 --去掉前兩位路徑\\P-2017072U\szrpp_files
set @filename='\\WIN-VMCOI88888'+SUBSTRING(@p_pathname,3,len(@p_pathname)-2);
---------------------------------------------------------------------------------------

--第一先匯入
set @stsql='BULK INSERT CfmRcdTmp '
            +' FROM '''+@filename+''''
            +' WITH('
            +'  FIRSTROW=1,'
            +'  FIELDTERMINATOR = '','','--使用“，”作為列分隔符
            +'  ROWTERMINATOR = ''\n'','--使用“\n”作為行分隔符
            +'  KEEPNULLS ,  '     --如果有些Column沒有值，設定 KEEPNULLS   選項，表示將該column設定為NULL
            +'  check_constraints'
            +' )';
    
    --print(@stsql);
    --print(@stsqlinsert);


--第二再存入實際庫
set @stsqlinsert='insert into CfmRcd( 
                                            ReconSource ,
                                            CmfFile ,
                                            CfmDate ,
                                            FileNumCur ,
                                            RechargeSerial ,
                                            TradeSysWater ,
                                            TradeTime ,
                                            TradeFee ,
                                            CfmStatus 
                                            )

                                  SELECT    
                                            ReconSource ,
                                            CmfFile ,
                                            CfmDate ,
                                            FileNumCur ,
                                            RechargeSerial ,
                                            TradeSysWater ,
                                            TradeTime ,
                                            TradeFee ,
                                            CfmStatus   
                                            FROM CfmRcdTmp';
--第三進行清除臨時表
 SET @stsqlDeleteTmp='truncate table CfmRcdTmp';

 --第四再替換格式
set @stsqlReplace='
    update CfmRcd
   set reconsource=replace(reconsource,'''''''',''''),
    cmffile =replace(cmffile,'''''''',''''),
    cfmdate=replace(cfmdate,'''''''',''''),
    filenumcur=replace(filenumcur,'''''''',''''),
    rechargeserial =replace(rechargeserial,'''''''',''''),
    TradeSysWater=replace(TradeSysWater,'''''''',''''),
    tradetime=replace(tradetime,'''''''',''''),
    tradefee =replace(tradefee,'''''''',''''),
    cfmstatus=replace(cfmstatus,'''''''','''')    
    ';
exec sp_executesql @statemnet=@stsql;
exec sp_executesql @statinsert=@stsqlinsert;         
exec sp_executesql @stattruncate=@stsqlDeleteTmp;
exec sp_executesql @statReplace=@stsqlReplace;        
        
set nocount on;
end;