How to send large amount of Data To SqlServer Stored Procedure

.net c# sqlbulkcopy sql-server stored-procedures

Question

I wish to transfer a lot of data to the SQL Server from a.Net-based application. This data records are arriving from a CSV file, hence a stored procedure on the SQL Server side should be used to process them. SqlBulkCopy, which I am aware of, writes straight to tables. this indicates that I cannot use it. I'm speaking from records that total over 100,000,000. This much information will overwhelm my memory. Therefore, my ideal question is: Is there a way to stream data from.Net to a stored procedure? Any assistance would be appreciated.

1
1
11/26/2015 10:55:21 AM

Accepted Answer

You seem to be looking for Parameters with a Table Value. The steps for creating a TVP type in a database and using it in a stored procedure are sufficiently documented. When establishing aSqlParameter , you may provideIEnumerable<SqlDataRecord> This is simply given by a C# iterator function that systematically reads CSV file data records one by one, converts each record's data, and produces a corresponding result for each record.SqlDataRecord . The data will still be bulk-inserted into a tempdb on the SQL Server side before being sent to the stored procedure, but at least on the client side, no memory is required to retain all of that data.

0
11/26/2015 11:53:28 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow