I don’t really have experience with TVP yet, however there is an nice performance comparison chart vs. BULK INSERT in MSDN here. They say that BULK INSERT has higher startup cost, but is faster thereafter. In a remote client scenario they draw the line at around 1000 rows (for “simple” server logic). Judging from their … Read more
On the basis of the comments to my question i’ve managed to get the stored procedure running with table valued parameters (and get the return values from the SP) The final script is as follows: import pandas as pd import pytds from pytds import login import sqlalchemy as sa from sqlalchemy import create_engine import sqlalchemy_pytds … Read more
Please do not create a DataTable to load via BulkCopy. That is an ok solution for smaller sets of data, but there is absolutely no reason to load all 10 million rows into memory before calling the database. Your best bet (outside of BCP / BULK INSERT / OPENROWSET(BULK…)) is to stream the contents from … Read more
UPDATE I’ve added support for this on Nuget Package – https://github.com/Fodsuk/EntityFrameworkExtras#nuget (EF4,EF5,EF6) Check out the GitHub repository for code examples. Slightly off question, but none the less useful for people trying to pass user-defined tables into a stored procedure. After playing around with Nick’s example and other Stackoverflow posts, I came up with this: class … Read more
DataTable, DbDataReader, or IEnumerable<SqlDataRecord> objects can be used to populate a table-valued parameter per the MSDN article Table-Valued Parameters in SQL Server 2008 (ADO.NET). The following example illustrates using either a DataTable or an IEnumerable<SqlDataRecord>: SQL Code: CREATE TABLE dbo.PageView ( PageViewID BIGINT NOT NULL CONSTRAINT pkPageView PRIMARY KEY CLUSTERED, PageViewCount BIGINT NOT NULL ); … Read more