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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

How can I resolve this stored procedure issue?

This is complicated by SQLBulkCopy, so I'd think about using a staging table and a ...clause OUTPUT...As an example, using SQL mixed with client pseudo code...create SQLConnection Create #temptable Bulkcopy to #temptable Call proc on same SQLConnection proc: INSERT tableA (..) OUTPUT INSERTED.key, .. INTO TableB SELECT .. FROM #temptabl...
sql sqlbulkcopy sql-server
asked by gbn

How can I index all of the OpenStreetMap data efficiently?

Utilizing a database for this makes logical. Keep in mind that all data in SQL CE is unicode as well as the possibility that the size is related to how small the pbf file is. What is sluggish is not obvious in your inquiry. After the import, you may also attempt compacting the database file; this can make the file somewhat smaller. Your.xap could s...
linq-to-sql openstreetmap sqlbulkcopy sql-server-ce
asked by ErikEJ

SqlBulkCopy to temporary table in SqlServer 2008

It seems that neither your saved procedure nor your temporary table are generated. This is the cause:...SqlCommand oSqlCommand = new SqlCommand("SP_CreateTax", oSqlConnection); ...Because you must call a procedure in order to run it, your saved procedure is not run:...EXEC SP_CreateTax... ....Note: Avoid naming stored procedures with these letters....
c# sqlbulkcopy sql-server-2008
asked by Alberto Solano

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

In regards to System, I think you're wrong. Data. SqlClient. SqlBulkCopy:...https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlBulkCopy.cs...If this is going to be an ongoing process, follow their advice and abandon entity framework (of whichever persuasion) in favor of using sql bulk copy. On the ot...
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?

Without additional information, it's difficult to say, however I assume that the fact that your timestamp is a clustered index is to blame. Before sending the data to the output table, try sorting it by this field.
indexing sql sqlbulkcopy sql-server
asked by Joe C

EF6 data context throws stackoverflow exception after SqlBulkCopy insrt large data

Okay, issue resolved. I'll offer my fix in case someone else has a similar issue....I changed the T4 template so that the EDM may generate the following entities from my database:...Use the INotifyPropertyChanged interface....Implement INotifyDataErrorInfo and use DataAnnotations to apply data validation while validating validation criteria in a di...
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 moves really quickly! Nothing, in my opinion, moves more quickly than carrying out this action....BULK INSERT EmployeeDB.dbo.Employees FROM 'C:\Data\EmployeeData_c.dat' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\r\n' ); ...See the following link for further information....https://www.simple-t...
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

The easiest approach to transfer a large number of tables from one schema of a database to another schema of the same database is to use the command ...SUBSTITUTE INTO...SELECT FROM..., thus execute as a user who has access to both schema, for example:...INSERT INTO schema2.table_y ( field, field, field ) SELECT field, field, field FROM schema1.tab...
postgresql sqlbulkcopy
asked by FuzzyChef

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

The only way to keep track of every change is via OUTPUT; you cannot keep track of every change with a variable....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), ...
database sqlbulkcopy sql-server stored-procedures
asked by ildanny

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

The mssql server jar is out of date, which is the cause of this. A 6.4.0 upgrade or later must be applied. If you are using Maven, it will seem as follows:... <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> <version>6.4.0.jre8</version> </dependency> ...Note that the artifac...
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