Bulk Insert proc to try out ur optimization skills
This SP can be used for bulk inserting data from an ascii text file onto a table. You have to change the structure, of the temporary table (ITEM_MASTER_TEMP) and the actual table onto which data is inserted, to suit ur needs . I wish you people workout this SP and try to optimize it as much as possible. I hope you will inform me if you find a way to optimize the proc.
Note:
In the actual proc the whole script is a single string, there is no cancatenation.
CREATE PROCEDURE BULK_INSERT
@VendCodeINT,
@ItemFileVARCHAR(300),
@FmtFileVARCHAR(300),
@MaxErrorsINT,
@KiloBytesPerBatchINT,
@AppendToStockBIT,
@RowsInsertedINTOUTPUT,
@RowsDeletedINTOUTPUT
WITH RECOMPILE
AS
BEGIN
SET XACT_ABORT OFF
DECLARE @Sql NVARCHAR(3000)
DECLARE @ParamDef NVARCHAR(100)
SET @RowsInserted=0
SET @RowsDeleted=0
SET @Sql = ''
SET @Sql = @Sql + 'DECLARE @Error INT' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N''ITEM_MASTER_TEMP' + CONVERT(VARCHAR,@VendCode) + ''') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)'
SET @Sql = @Sql + 'DROP TABLE ITEM_MASTER_TEMP' + CONVERT(VARCHAR,@VendCode) + CHAR(13) + CHAR(10)
--CREATING THE TEMPORARY TABLE
SET @Sql = @Sql + 'CREATE TABLE ITEM_MASTER_TEMP' + CONVERT(VARCHAR,@VendCode) + CHAR(13) + CHAR(10)
SET @Sql = @Sql + '(' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_ARTIST VARCHAR (50) NULL ,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_TITLE VARCHAR (100) NOT NULL ,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_PRICE FLOAT NOT NULL' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'CHECK (ITEM_PRICE >= 0),' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_MEDIA TINYINT NOT NULL' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'DEFAULT(1)' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'CHECK (ITEM_MEDIA >= 1 AND ITEM_MEDIA <= 57),' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_AVAIL_QTY INT NOT NULL' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'DEFAULT(1)' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'CHECK (ITEM_AVAIL_QTY >= 0),' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_DISC_STATE TINYINT NOT NULL' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'DEFAULT(1)' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'CHECK (ITEM_DISC_STATE >= 1 AND ITEM_DISC_STATE <= 4),' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_COVER_STATE TINYINT NOT NULL' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'DEFAULT(1)' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'CHECK (ITEM_COVER_STATE >= 1 AND ITEM_COVER_STATE <= 4),' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_LANG TINYINT NOT NULL' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'DEFAULT(1)' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'CHECK (ITEM_LANG >= 1 AND ITEM_LANG <= 10),' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_DATE DATETIME NULL ,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_GENRE TINYINT NOT NULL' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'DEFAULT(1)' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'CHECK (ITEM_GENRE >= 1 AND ITEM_GENRE <= 31),' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_TRACKING VARCHAR (20) NULL ,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_RELEASE_ID VARCHAR (20) NULL ,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_IMAGE_URL VARCHAR (100) NULL ,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_DESC VARCHAR (1000) NULL ,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_LABEL VARCHAR (30) NULL' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + ')' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'SET @Error = @@ERROR' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'IF @Error <> 0' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'BEGIN' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'RAISERROR(''Error creating temporary table'',16,1)' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'GOTO BOTTOM' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'END' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'SET DATEFORMAT mdy' + CHAR(13) + CHAR(10)
--UPLOADING DATA TO THE TEMPORARY TABLE
SET @Sql = @Sql + 'BULK INSERT ITEM_MASTER_TEMP' + CONVERT(VARCHAR,@VendCode) + ' FROM ''' + @ItemFile + '''' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'WITH' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + '(' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'MAXERRORS=' + CONVERT(VARCHAR,@MaxErrors) + ',' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'FORMATFILE=''' + @FmtFile + ''',' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'CHECK_CONSTRAINTS,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'KILOBYTES_PER_BATCH=' + CONVERT(VARCHAR,@KiloBytesPerBatch) + ',' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'TABLOCK' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + ')' + CHAR(13) + CHAR(10)
--BEGIN TRANSACTION
SET @Sql = @Sql + 'BEGIN TRAN' + CHAR(13) + CHAR(10)
--BEGIN TRANSACTION
--IF SPECIFIED, REFRESH THE VENDOR'S ITEM STOCK
SET @Sql = @Sql + 'IF (@AppendToStock = 0)' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'BEGIN' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'DELETE FROM ITEM_MASTER WHERE ITEM_VEND_CODE=''' + CONVERT(VARCHAR,@VendCode) + '''' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + ' SET @RowsDeleted = @@ROWCOUNT' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'SET @Error = @@ERROR' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'IF @Error <> 0' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'BEGIN' + CHAR(13) + CHAR(10)
--ROLLBACK TRANSACTION
SET @Sql = @Sql + 'ROLLBACK TRAN' + CHAR(13) + CHAR(10)
--ROLLBACK TRANSACTION
SET @Sql = @Sql + 'DROP TABLE ITEM_MASTER_TEMP' + CONVERT(VARCHAR,@VendCode) + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'RAISERROR(''Error clearing existing stock'',16,1)' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'GOTO BOTTOM' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'END' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'END' + CHAR(13) + CHAR(10)
--COPY DATA FROM THE TEMPORARY TABLE TO ITEM_MASTER
SET @Sql = @Sql + 'INSERT INTO ITEM_MASTER' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + '(' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_VEND_CODE,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_ARTIST,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_TITLE,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_MEDIA,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_PRICE,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_AVAIL_QTY,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_LABEL,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_RELEASE_ID,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_GENRE,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_DATE,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_LANG,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_DISC_STATE,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_COVER_STATE,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_TRACKING,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_IMAGE_URL,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_DESC' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + ')' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + ' SELECT' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_VEND_CODE=''' + CONVERT(VARCHAR,@VendCode) + ''',' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_ARTIST,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_TITLE,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_MEDIA,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_PRICE,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_AVAIL_QTY,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_LABEL,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_RELEASE_ID,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_GENRE,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_DATE,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_LANG,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_DISC_STATE,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_COVER_STATE,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_TRACKING,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_IMAGE_URL,' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'ITEM_DESC' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + ' FROM ITEM_MASTER_TEMP' + CONVERT(VARCHAR,@VendCode) + CHAR(13) + CHAR(10)
SET @Sql = @Sql + ' SET @RowsInserted = @@ROWCOUNT' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'SET @Error = @@ERROR' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'IF @Error <> 0' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'BEGIN' + CHAR(13) + CHAR(10)
--ROLLBACK TRANSACTION
SET @Sql = @Sql + 'ROLLBACK TRAN' + CHAR(13) + CHAR(10)
--ROLLBACK TRANSACTION
SET @Sql = @Sql + 'DROP TABLE ITEM_MASTER_TEMP' + CONVERT(VARCHAR,@VendCode) + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'RAISERROR(''Error copying data from temporary table to item_master'',16,1)' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'GOTO BOTTOM' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'END' + CHAR(13) + CHAR(10)
--COMMIT TRANSACTION
SET @Sql = @Sql + 'COMMIT TRAN' + CHAR(13) + CHAR(10)
--COMMIT TRANSACTION
--DELETE THE TEMPORARY TABLE
SET @Sql = @Sql + 'DROP TABLE ITEM_MASTER_TEMP' + CONVERT(VARCHAR,@VendCode) + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'SET @Error = @@ERROR' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'IF @Error <> 0' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'BEGIN' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'RAISERROR(''Error droping temporary table'',16,1)' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'END' + CHAR(13) + CHAR(10)
SET @Sql = @Sql + 'BOTTOM:' + CHAR(13) + CHAR(10)
SET @ParamDef = '@AppendToStock BIT, @RowsInserted INT OUTPUT, @RowsDeleted INT OUTPUT'
DECLARE @ret INT
EXEC @ret = SP_EXECUTESQL @Sql, @ParamDef, @AppendToStock=@AppendToStock, @RowsInserted=@RowsInserted OUTPUT, @RowsDeleted=@RowsDeleted OUTPUT
RETURN @ret
END
GO 
