Click here to Skip to main content
15,884,298 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

SQL
--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
Posted
Updated 19-Mar-16 21:33pm
v3
Comments
Afzaal Ahmad Zeeshan 20-Mar-16 3:37am    
A user defined table is also a table. Unless that table is going to be deleted somewhere later, you can surely add the data to the table.

1 solution

If you want to insert record only one table then you can use SqlBulkCopy. Follow below links:

Transferring Data Using SqlBulkCopy[^]

SqlBulkCopy: Bulk Copy data from DataTable (DataSet) to SQL Server Table using C# and VB.Net[^]

Secondly it is obvious that it will take if you will process 50,000 records. In this scenario, you can create background service(windows service or sql job) which will run and insert your data to SqlServer. So that it won't affect your main UI thread.
 
Share this answer
 
Comments
King Fisher 20-Mar-16 3:48am    
I cant create SQL Job/windows service.
[no name] 20-Mar-16 7:23am    
If you can't implement any background job then try to implement SqlBulkCopy. If you are processing from UI then it will take time and block your current thread.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900