Solved

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

Posted on 2009-05-07
11
3,211 Views
Last Modified: 2012-05-06
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
Comment
Question by:ZuZuPetals
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 9

Expert Comment

by:tculler
ID: 24325212
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24325358
>>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
 
LVL 2

Author Comment

by:ZuZuPetals
ID: 24325647
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Expert Comment

by:tculler
ID: 24326172
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
 
LVL 2

Author Comment

by:ZuZuPetals
ID: 24326563
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
 
LVL 2

Author Comment

by:ZuZuPetals
ID: 24326881
They are numbers stored in a C# String object, delimited by row, easily machine readable/parseable/transformable into whatever form is necessary.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24328072
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24328095
You are using SQL Server 2000, right?  If not what version are you using?
0
 
LVL 2

Author Comment

by:ZuZuPetals
ID: 24328833
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24331388
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
 
LVL 2

Expert Comment

by:muskad202
ID: 25217705
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question