Can I use user defined table type for large data insertion?

Viewed 1085

I have around 50000 records in my DataTable,i tried to use user defined table type as a parameter in my stored procedure.anyway its taking too much time for insertion and my application got hanged.

Can anybody suggest me ,how do i approach this? What i have tried?

User defined table type:
CREATE TYPE [dbo].[TYPE_INSERT_EXCELDATA] AS TABLE(
    [MERCHANT] [nvarchar](50) NOT NULL,
    [DEAL_ID] [nvarchar](50) NOT NULL,
    [MODEL_NAME] [nvarchar](100) NOT NULL,
    [PRODUCT_URL] [nvarchar](max) NOT NULL,
    [LINE_RENTAL] [nvarchar](50) NOT NULL,
    [IMAGE_URL] [nvarchar](max) NOT NULL,
    [CATEGORY_NAME] [nvarchar](50) NOT NULL,
    [BRAND] [nvarchar](50) NOT NULL,
    [PRICE] [float] NOT NULL,
    [NETWORK] [nvarchar](250) NOT NULL,
    [TARIFF_NAME] [nvarchar](250) NOT NULL,
    [CONTRACT_LENGTH] [nvarchar](50) NOT NULL,
    [MINUTES] [nvarchar](50) NOT NULL,
    [TEXTS] [nvarchar](50) NOT NULL,
    [DATA] [nvarchar](50) NOT NULL,
    [GIFTS] [nvarchar](250) NULL
)
GO


PROC:
CREATE PROCEDURE [dbo].[SP_SET_PRODUCT_EXCELDATA]
      @TblExcelData TYPE_INSERT_EXCELDATA READONLY,
      @OUTPUT NVARCHAR(MAX) OUTPUT
AS
BEGIN
      SET NOCOUNT ON;

     BEGIN TRY
      INSERT INTO GLB_M_EXCELDATA(MERCHANT, DEAL_ID, MODEL_NAME,PRODUCT_URL,LINE_RENTAL,IMAGE_URL,CATEGORY_NAME,BRAND,PRICE,NETWORK,CONTRACT_LENGTH,TARIFF_NAME,MINUTES,TEXTS,DATA,GIFTS)

      SELECT MERCHANT, DEAL_ID, MODEL_NAME,PRODUCT_URL,LINE_RENTAL,IMAGE_URL,CATEGORY_NAME,BRAND,PRICE,NETWORK,CONTRACT_LENGTH,TARIFF_NAME,MINUTES,TEXTS,DATA,GIFTS FROM @TblExcelData
      END TRY
      BEGIN CATCH
      INSERT INTO TBL_LOG(ERRORMESSAGE,PROCEDURENAME,CREATEDDATE,ERRORNUMBER) VALUES(ERROR_MESSAGE(),ERROR_PROCEDURE(),GETDATE(),ERROR_NUMBER());
      SET @OUTPUT='FAILURE';
      END CATCH

END
0 Answers
Related