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
First things first: I have no idea where you are getting the tableName and columnName, but if they are user-supplied, then this is open to SQL injection. At the very least, use QUOTENAME() to ensure no actual code is injected. Secondly, you are not actually using the TVP. The code you have is just saying … Read more
My first, ad hoc, idea was to implement my own IType. public class Sql2008Structured : IType { private static readonly SqlType[] x = new[] { new SqlType(DbType.Object) }; public SqlType[] SqlTypes(NHibernate.Engine.IMapping mapping) { return x; } public bool IsCollectionType { get { return true; } } public int GetColumnSpan(NHibernate.Engine.IMapping mapping) { return 1; } public … 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