Using C#, process each row and copy it to a new table.

c# sqlbulkcopy sql-server sql-server-2008

Question

I have an MSSQL 2008 table with a few million records. I need to iterate over each row, modify some of the data, and copy the updated record to a new table using a C# application that gets executed on a daily basis.

I have tried doing this using ADO.NET entities, but there are memory issues involved with this method, not to mention it is very slow. I have read up on bulk-copy libraries and SQL-only ways for copying one table to another, but none of them involve modifying records before copying them. I need to find a better way for performing this operation.

1
3
8/24/2014 2:50:55 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

As you mention memory issues I'm guessing you're trying to load the million rows into memory, process them and then write them back to the database. You can avoid this by 'streaming' the data instead of loading it entirely. The SqlDataReader will handle buffering for you so on the reading side you can do a simple WHILE loop that fetches rows one by one. The actual conversion you already have working it seems so all you need to do is take care of writing the results back into the database. IMHO the fastest way to do so is by storing a buffer of multiple results (start with 100, work up and see where the sweet spot is) in a data-table and then push that data-table into the database using the SqlBulkCopy class. Rinse & repeat.

PS: Sounds like a 'fun' problem. Do you have any sample data sitting somewhere to test this out ? 5 hours sounds like a LONG time for something that looks trivial at first, then again 20 million times virtually nothing still adds up. More specifically I wonder how 'large' the data is on the RTF side : are the values ca 2k on average or rather 200k? And what kind of hardware do you run this on ?

3
4/3/2012 7:11:36 AM

Popular Answer

The fastest performing option would be to re-write your C# application logic into a CLR stored procedure so that all processing takes place on the server.

BluesRockAddict

Related Questions

Splitting a huge table into two and bulk copying using SQL Server and C#.net

I would create it in one table and create well defined indexes. ...Consider a filter index for the flag column. This is done with a where clause in t-sql and the filter page in ssms. ...http://msdn.microsoft.com/en-us/library/cc280372.aspx
.net c# sqlbulkcopy sql-server-2008
asked by Russell Hart

How to copy and validate data from one table (all varchar) to another (typed) in C#?

Do it in T-SQL with a ...linked server.... ...I.e.: ...--begin a transaction to wrap validation and load BEGIN TRAN --Validate that no tickets are set to closed without a completion date SELECT * FROM bigTableOnLocalServer with (TABLOCKX) -- prevent new rows WHERE ticketState = '1' /* ticket closed */ and CompletionDate = 'open' --if validatio...
c# entity-framework sqlbulkcopy
asked by Mitch

Fastest way to insert 30 thousand rows in a temp table on SQL Server with C#

Your problem may be in ...localTempTable.AcceptChanges();... Since it commit your changes.... If you do the next , I think it will run faster... foreach (var item in ids) { DataRow row = localTempTable.NewRow(); row[0] = item; localTempTable.Rows.Add(row); } localTempTable.AcceptChanges(); using (Sql...
bulkinsert c# sql sqlbulkcopy sql-server
asked by Mzf

Bulk copy of SQL data from table on disk to an in-memory table

create a class.....public class MemoryObject { public Int64 Id {get; set;} public string OtherProperty {get; set;} } ...then, use a SQLDataReader......public List<MemoryObject> GetMemoryObjects() { using (SqlConnection connection = new SqlConnection("...")) { string sql = "query"; SqlCommand command = new Sql...
c# database sql sqlbulkcopy sql-server
asked by christiandev

Excel Data to new SQL Server table using C# - new table not being created?

Hi think you are not checking your table name before inserting excel data into data table. you can go through the below code....class excelTwosheetsBulk1 { private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); static void Main(string[] args) { log...
c# sqlbulkcopy sql-server tsql
asked by Naresh.boddu

Using the SQL bulk copy in C# and deleting the string headers

Try this : ...string format = "MM/dd/yyyy hh:mm:ss.fff"; DateTime d =DteTime.ParseExact(s[6],format,System.Globalization.CultureInfo.InvariantCulture); dr["DateTime"]=d; ...Change the format as you want like "MM/dd/yyyy" or "MM/dd/yyyy hh:mm" etc
c# datetime sql sqlbulkcopy sql-server
asked by tHiNk_OuT_oF_bOx

Bulk insert into existing table - only insert new rows

Use a temporary table to ...bulk insert.... Then, run a query to copy new rows over, filtering out duplicates with a ...where... clause:...insert YourTable (id, col1, col2, col3, ...) select id , col1 , col2 , col3 , ... from TempTable t where not exists ( select * from YourTable y...
c# sqlbulkcopy sql-server
asked by Andomar

Unable to compare SQL and datatable and copy new entries to sql table

Please put the appropriate spaces between the SQL statements of your query...cmdString = "INSERT INTO Daten ([Zyklus ID]) " & _ "SELECT [Zyklus ID] " & _ "FROM Test " & _ "LEFT JOIN Daten ON (Test.[Zyklus ID] = Daten.[Zyklus ID]) " & _ "WHERE (Daten.[Zyklus ID] IS NULL);"
datatable sql sqlbulkcopy sql-server vb.net
asked by Steve

Missing Data after insert into my end sql table using Sql Bulk Copy

you need to look at the data in Access, and find/fix those bad column values, or handle them in your TSQL after the export. One option to debug - replace temp table with real table, replace datetime datatype with string, do your export, then query the table to find the bad values so you know what kind of data to look for. After that, you can swit...
c# console-application datatable sqlbulkcopy sql-server-2008
asked by Greg

Using SQL Bulk Copy to insert ~3.7 million rows throws OutOfMemory exception

Thank you all for your response....I figured out what was the problem. Firstly, it didn't occur when calling the SQL Bulk Copy, which I assumed it could have been because, in previous run, I was able to run few 1000 less records than the current one, so I thought the SQL Bulk Copy had limitations or need to settings in order to handle large number ...
c# sqlbulkcopy sql-server
asked by superachu

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