Hi,
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:
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
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