SqlBulkCopy Tutorial - Knowledge Base (KB)
47 results in tag: datatable
Does SqlBulkCopy WriteToServer need all database columns in my DataTable?
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 can I make a datatable with gridview rows?
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 put a DataTable into a SQL Server Table with an Existing Key
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 can I locate the problematic column? Converting from string to int32 is not possible.
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
management sqlbulkcopy mem
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
Inserting 200 million entries into a database using C# optimization
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 replicate the contents of a DataTable to a SQL table as I populate 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
When using SqlBulkCopy to insert into a sql variant column, how do I specify the column type?
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 Having to Create a 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 a SQL Server Table Using Data from a Different 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 the rows that caused the 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