Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3297
  • Last Modified:

How to use ADO.Net DataTable to bulk-insert many records into database table?

Is it possible to create a DataTable object, define a few columns, add a few thousand rows, connect it to a database table, then "sync" up and have it insert those rows into a SQL Server database table?

Is this reasonable efficient or is it still "under the covers" doing multiple inserts? (and in that case no need to go through all the hassle of using the DataTable approach?)

Has anyone tried this?

If so, what are the concrete steps of how to create these objects and assign properties to pull this off?
0
ZuZuPetals
Asked:
ZuZuPetals
  • 4
  • 4
  • 2
  • +1
1 Solution
 
tcullerCommented:
This is possible, I believe, but I won't go into the "how" because it would be mercilessy inefficient. The purpose ofthe DataTable and DataSet objects are to load data into memory. If you're planning on bulk inserting thousands of rows into a database, there's no reason to load them into memory first and have them sit there, eating up all of your RAM. Anything to do with a database comes down to SQL statements under the hood--that's the point of a Database Engine.

I recommend just using an "INSERT INTO" SQL-statement (or a series of them) to do the trick. If you need help on that, let me know--just post a few more details on what exactly you're trying to achieve.
0
 
Anthony PerkinsCommented:
>>Is it possible to create a DataTable object, define a few columns, add a few thousand rows, connect it to a database table, then "sync" up and have it insert those rows into a SQL Server database table?<<
This is only possible in SQL Server 2008.  See this article from Tim Chapman (chapmandew):
Passing table valued parameters in SQL Server 2008
http://blogs.techrepublic.com.com/datacenter/?p=168

With SQL Server 2000, your options are down to:
1. Inserting one row at a time.
2. Passing all the rows as one using Xml or some other mechanism.

0
 
ZuZuPetalsAuthor Commented:
I was hoping this would be an optimal solution for my earlier post:

    "Hourly Import 20K Rows... What is Best Practice? SSIS?"

I already have data in memory and have the task of getting it into the database efficiently somehow.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
tcullerCommented:
Well, if it must be in memory, I would use a massive update. How is it formatted in memory? I mean to ask, what are the exact types of objects, what is the collection you're using, etc.
0
 
ZuZuPetalsAuthor Commented:
Let me rephrase the question:  The problem is this...

We're capturing data in variables in code and need to write the information as a record in the database.  But we need to do this many thousands of times per day, so we're looking for a way to avoid a constant stream of one record inserts to the database because we believe this will be less efficient than sort of "caching" them up and then periodically bulk inserting them.  

The data we're collecting does not come from a structured format (does not come from a file or table) it is captured as variable data in code and needs to be inserted as a record.  

Are we correct in assuming periodic bulk inserts will be more efficient than an endless string of 1 record inserts?  If so, does anyone have any ideas on how best to "cache" this data and then bulk insert it periodically?
0
 
ZuZuPetalsAuthor Commented:
They are numbers stored in a C# String object, delimited by row, easily machine readable/parseable/transformable into whatever form is necessary.
0
 
Anthony PerkinsCommented:
In case you missed my comment, here it goes again:

With SQL Server 2000, your options are down to:
1. Inserting one row at a time.
2. Passing all the rows as one using Xml or some other mechanism.
0
 
Anthony PerkinsCommented:
You are using SQL Server 2000, right?  If not what version are you using?
0
 
ZuZuPetalsAuthor Commented:
Using SQL Server 2005.

I can easily construct a simple XML document from my data.  How do I call ADO.Net object so that the data is inserted into the target database table.  That would swell!
0
 
Anthony PerkinsCommented:
Using ADO.NET you pass the Xml to a Stored procedure and then you have a couple of options:
1. Use OPENXML() (also supported in SQL Server 2000)
2. Use the Xml Data methods (new with SQL Server 2005)

To shred the Xml into table(s).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now