title: Can I use user defined table type for large data insertion?
body: <p>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.</p>
  
  <p>Can anybody suggest me ,how do i approach this?
  What i have tried?</p>
  
  <pre><code>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
  </code></pre>