2

If I have a large amount of data in memory, what is the best way to copy it into a SQL CE table? The current technology stack is C#, ADO.net, and SQL CE.

My initial idea was to do one INSERT statement for each row of data, but this is time-consuming. Is there an easier way?

Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
Yes - that Jake.
  • 16,725
  • 14
  • 70
  • 96

2 Answers2

3

Steve Lasker has a great code sample demoing this with different methods, but the best method (by far) is SqlCeResultSet.

Here is a blog post from Steve with some good powerpoints on this subject.

JasonRShaver
  • 4,344
  • 3
  • 32
  • 39
2

The first thing that popped into my head was to do a sync/merge with a desktop or server database, though that doesn't sound like it really fits your needs. So here is my second thought:

BULK INSERT table_name FROM data_file

I am not sure if it is supported in your version of SQL CE (though it appears to be supported in 3.5 SP1 from what I can tell on the MSDN pages). http://msdn.microsoft.com/en-us/library/ms188365.aspx

You could also disable any indexes on the table while inserting the data to speed things up, then enable/rebuild the index once you are done inserting.

Goyuix
  • 23,614
  • 14
  • 84
  • 128