How to send large amount of Data To SqlServer Stored Procedure

.net c# sqlbulkcopy sql-server stored-procedures

Question

I want to send large amounts of data from a .Net based application to the SQL Server. This Data Records are coming from a CSV file and should be funneled through a Stored Procedure on the SQL Server side. I know about SqlBulkCopy but SqlBulklCopy writes directly to tables. Means, it is not usable for me. I am speaking from over 100.000.000 Records. I won't be able to hold this amount of data in memory. So ideally I ask if there exists some kind of streaming Data from .Net to a Stored Procedure. I am grateful for any help.

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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

I think you are seeking for Table-Valued Parameters. There is enough documentation how to create TVP type in the database and use it inside a stored procedure. When setting up a SqlParameter, you can supply IEnumerable<SqlDataRecord> which can easily be provided by a C# iterator function directly reading sequentially CSV file data records one by one and for each record, parse, convert and yield a corresponding SqlDataRecord. At the Sql Server side the data will still be bulk inserted into a tempdb before passed to the stored procedure, but at least at client side you don't need a memory to hold all that data.

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

Related Questions

How can I resolve this stored procedure issue?

SQLBulkCopy complicates this so I'd consider using a staging table and an ...OUTPUT clause...Example, in a mixture of client pseudo code and SQL...create SQLConnection Create #temptable Bulkcopy to #temptable Call proc on same SQLConnection proc: INSERT tableA (..) OUTPUT INSERTED.key, .. INTO TableB SELECT .. FROM #temptable close con...
sql sqlbulkcopy sql-server
asked by gbn

How can I index all of the OpenStreetMap data efficiently?

It makes sense to use a database for this. The size may be due to the compactness of the pbf file, also keep in mind that all data in SQL CE is unicode. Your question is unclear - what is slow? Also, you can try to Compact the database file after the import, it may shrink the file a bit. Depending on the resulting size, your .xap may still be small...
linq-to-sql openstreetmap sqlbulkcopy sql-server-ce
asked by ErikEJ

SqlBulkCopy to temporary table in SqlServer 2008

It seems that your stored procedure isn't executed and then your temporary table isn't created. The reason is this:...SqlCommand oSqlCommand = new SqlCommand("SP_CreateTax", oSqlConnection); ...Your stored procedure isn't executed, because to execute a procedure, you have to invoke: ...EXEC SP_CreateTax.......Note: It's not suggested to create stor...
c# sqlbulkcopy sql-server-2008
asked by Alberto Solano

Entity Framework Core 1.0 - How to seed a large amount of data

I believe you are mistaken about System.Data.SqlClient.SqlBulkCopy:...https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlBulkCopy.cs...Do as they recommend, ditch entity framework (of whatever stripe) and use sql bulk copy if this is going to be an on going task. On the other hand, you say "seed" --...
asp.net-core entity-framework-core seeding sqlbulkcopy
asked by jmoreno

Why do I get high fragmentation when using SqlBulkCopy to move large amounts data between databases?

Its hard to tell without more data but I bet that your timestamp being a clustered index is causing this. Try sorting the data by this field before sending to the output table.
indexing sql sqlbulkcopy sql-server
asked by Joe C

EF6 data context throws stackoverflow exception after SqlBulkCopy insrt large data

Ok, problem solved, just in case someone else would face a similar error, I will share my solution....I have modified the T4 template to enable the EDM to produce entities from my database that:...Implement the INotifyPropertyChanged....Apply Data Validation by implementing the INotifyDataErrorInfo and using DataAnnotations by checking validation r...
entity-framework-6 sqlbulkcopy wpf
asked by Khaled

Best practice for transferring a large amount of data. Using Stored Procedure or SQL Bulk Copy

Bulk Insert is blazing fast! I don't think anything is faster than doing something like this....BULK INSERT EmployeeDB.dbo.Employees FROM 'C:\Data\EmployeeData_c.dat' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\r\n' ); ...See the link below for more details....https://www.simple-talk.com/sql/learn-sql-se...
c# sql sqlbulkcopy sql-server stored-procedures
asked by ASH

Postgres: Copying large subsets of data(10k+rows) from one schema to another for immediate use

Since you are copying from a bunch of tables in one schema of a database to another schema of the same database, and cannot start until your fastest way to do so is going to be by using ...INSERT INTO ... SELECT FROM..., run as a user who has access to both schema, e.g.:...INSERT INTO schema2.table_y ( field, field, field ) SELECT field, field, fie...
postgresql sqlbulkcopy
asked by FuzzyChef

Create procedure that will insert or update data from one temporary table to other 4 tables

You cannot use a variable to track all the changes, but you track all the changes with OUTPUT....if object_id('dbo.[MSPCompanies]') is not null drop table dbo.[MSPCompanies]; if object_id('dbo.[MSPTempTable]') is not null drop table dbo.[MSPTempTable]; create table dbo.[MSPTempTable] ( MSP_Companies_ID int identity(1,1), INN ...
database sqlbulkcopy sql-server stored-procedures
asked by ildanny

java.lang.NoSuchMethodError: com.microsoft.sqlserver.jdbc.SQLServerBulkCopyOptions.setAllowEncryptedValueModifications(Z)V

This is due to an old version of the mssql server jar. Make sure you update it to 6.4.0 or later. If you're using maven it will look like this:... <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> <version>6.4.0.jre8</version> </dependency> ...Note the artifactId used to be ca...
apache-spark mssql-jdbc scala sqlbulkcopy sql-server
asked by Matthew

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

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