SqlBulkCopy Tutorial - Knowledge Base (KB)
47 results in tag: datatable
Do I need all database columns in my DataTable for SqlBulkCopy WriteToServer?
I'm importing some data from a CSV, and it doesn't have all the fields my table does. I was making a DataTable with only the columns of my CSV, but I'm getting errors about converting from String to Boolean. None of the fields I'm adding are Boolean, so...
asked by Thomas
How to add gridview rows to a datatable?
I have a gridview which will contain some 'n' number of rows.... Now i want to add all rows of the gridview to a datatable which will be used for bulkcopy operation......I have found this ...http://www.codeproject.com/KB/aspnet/GridView_To_DataTable.aspx...
asked by ACP
How to insert a DataTable with existing Key to a SQL Server Table
I am working with VB.NET.. i have a DataTable called "QUESTION", containing 3 fields:...QuestionNumber (unique integer key)...QuestionText...QuestionType...In my SQL Server database I created a Table called "QUESTION" with the same fields. QuestionNumber ...
asked by Olga
How to find offending column? Can't Convert from string to int32
I am using SqlBulkCopy. So I made a datatable and specifed it's columns then added rows to the datatable and then try to insert it....System.InvalidOperationException was unhandled by user code Message=The given value of type String from the data ...
asked by chobo2
sqlbulkcopy mem. management
I'm using SQLBULKCOPY to copy some data-tables into a database table, however, because the size of the files I'm copying run sometimes in excess of 600mb, I keep running out of memory....I'm hoping to get some advice about managing the table size before I...
asked by jordan.baucke
C# Optimisation: Inserting 200 million rows into database
I have the following (simplified) code which I'd like to optimise for speed:...long inputLen = 50000000; // 50 million DataTable dataTable = new DataTable(); DataRow dataRow; object[] objectRow; while (inputLen--) { objectRow[0] = ... objectRow[1...
asked by David Tang
How can I copy DataTable contents to an SQL table while I'm filling it
I have defined a DataTable by adding typed columns. I expect the DataTable to range between 1 million to 3 million rows....I am using Microsoft's TextFieldParser (because it supports multiple fixed width formats, well kind of, via a Peek method), to popul...
asked by mindless.panda
How can I set column type when using SqlBulkCopy to insert into a sql_variant column
I'm using SqlBulkCopy to insert/update from a .net DataTable object to a SQL Server table that includes a sql_variant column. However SqlBulkCopy insists on storing DateTime values put into that column as sql type 'datetime' when what I need is 'datetime...
asked by BowserKingKoopa
How to use BulkCopy without Defining the Destination Table
All, ...I have the following ...BulkCopy... operation:...// Convert the FlexGrid to a DataTable. DataTable currData = (DataTable)c1ErrFlexGrid.DataSource; // Insert the data into the database. SqlBulkCopy SqlBulkIns = new SqlBulkCopy(strConnString, SqlBu...
asked by MoonKnight
How to Update SQL Server Table With Data From Other Source (DataTable)
I have a ...DataTable... which is generated from .xls table....I would like to store this ...DataTable... into an existing table in SQL Server database....I use ...SqlBulkCopy... to store rows which have ...unique PK.......Problem is, I also have other ro...
asked by rofans91
Finding culprit rows on constraint failure
Any idea if it's possible to get a list of the trouble making rows in an INSERT/UPDATE statement when a constraint test fails? ...I'm trying to insert data with an SQLBulkCopy object reading data from a DataTable, and when the constraint fails I have no w...
asked by David Catriel
Automatically define the DataTable in C# from the schema of SQL server tables?
I already have some tables in SQL Server and I am writing a C# program to populate the tables from some flat files. I plan to use ...SQLBulkCopy... to load the tables. ...It can be a lot of work to define all the columns for each DataTable for SQLBulkCopy...
asked by ca9163d9
Mapping columns in a DataTable to a SQL table with SqlBulkCopy
I would like to know how I can map columns in a database table to the datatable in c# before adding the data to the database....using (SqlBulkCopy s = new SqlBulkCopy(conn)) { s.DestinationTableName = destination; s.WriteToServer(Ads_api_ReportDat...
asked by user2545743
SQLBulkCopy - WriteToServer method not working
I'm trying to export some data from an Excel file to a MDF database. To do that, I'm using Excel Data Reader and SQL Bulk Copy : ...using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using Excel; using S...
asked by Traffy
SqlBulkCopy - The given value of type String from the data source cannot be converted to type money of the specified target column
I'm getting this exception when trying to do an SqlBulkCopy from a DataTable....Error Message: The given value of type String from the data source cannot be converted to type money of the specified target column. Target Site: System.Object ConvertValue(Sy...
asked by Ricketts
How do I bulk insert two datatables that have an Identity relationship
I'm using SQLBulkCopy, in pseudocode I do this:...make new Employee datatable ...make new EmployeeAddress datatable...populate employee table but don't specificy employeeId as it's identity...populate EmployeeAddress datatable, but it contains an employee...
asked by NibblyPig
Getting an exception writing a boolean to sql using a datatable and SqlBulkCopy
It works fine for all other datatypes, but I am unable to get it to work with a 'bit' column....This is my SQL for doing the bulk write:...using (var bulk = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls))...
asked by NibblyPig
Update SQL server table using Datatbale
I have in memory table(DataTable), I want to update SQL table with the help of the DataTable in one go(Bulk update). I know how to bulk insert data from DataTable to SQL table. Is there a way to bulk update ?
asked by Jainendra
LINQ query IEnumerable<Object> for IEnumerable<DataRow> results in null
Why is my LINQ query ("rows") showing up as a null when it seems like I have plenty of data ("waypoints")?...I'm doing an insert of ~20k records and rather than go through Entity Framework I've opted for SqlBulkCopy, which seems to mean I need a datatable...
asked by Eric
How to add CsvHelper records to DataTable to use for SqlBulkCopy to the database
I am trying to read a CSV file with CsvHelper, load each record into a DataTable, and then use SqlBulkCopy to insert the data into a database table. With the current code, I get an exception when adding a row to the DataTable. The exception is: "Unable to...
asked by Justin Nafe
Page 1 of 3