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......
