locked
BULK INSERT and BCP RRS feed

  • Question

  • set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go




    Create PROCEDURE 
     [dbo].[Import]  
    @DEST_FILEPATH VARCHAR(500),@FORMAT_FILEPATH VARCHAR(500),  
    @SERVERNAM VARCHAR(100),@TABLE VARCHAR(500),@batchsize varchar(10) = 100000 ,@packetsize varchar(10) =16192  
    ,@USERNAME VARCHAR(100),@PASSWORD1 VARCHAR(100),@ErrorState int output
    AS  
    BEGIN  
            SET NOCOUNT ON  ;

        
        DECLARE @STR_COMMAND NVARCHAR(1000);                
            DECLARE @StartTime datetime;
        DECLARE @StatusMessage varchar(25);
        DECLARE @RowCountQuery varchar(250);

            SET @StartTime = getdate();
        SET @StatusMessage = 'SUCCEED'
                    

                    SET @STR_COMMAND =  'BCP '+@TABLE+' IN '+ @DEST_FILEPATH + ' -f ' + @FORMAT_FILEPATH + ' -b'+@batchsize+ ' -a'+@packetsize+'  -S'+@SERVERNAM+ ' -U'+@USERNAME+ ' -P'+ @PASSWORD1 + ' -h "TABLOCK"'

                    EXEC @ErrorState = XP_CMDSHELL @STR_COMMAND

                    if (@ErrorState !=0) SET @StatusMessage = 'FAILED'    



    END


    this is the stored procedure which accepts the DAT and XML file path and it imports the file content to the table.

    Before importing i will truncate the table.

    I having certain doubts

    1. I thinking of droping the index before importing and recreating the index after..
    2. Need to set auto_create and auto_update statistics off
    3.
    Some of the sites saying for IMPORTING they are recommending the BULK
    INSERT instead of BCP(since bulk insert is faster). is it true... is
    that BULK INSERT HAVING MEMORY RESTRICTIONS........
    4. i have a plan
    to split the imported files in to several partitions and import the
    several partitions file to the table in parallel....

    How these this things are possible...

    how to do the bulk insert on the other query server from master server like other server.



    how to achieve these things and any other methods available to boost the performance......







    Thursday, November 13, 2008 9:25 AM