Solved

ADO.NET: insert data from dataset/dataview/datatable into new table in a database

Posted on 2011-03-02
12
703 Views
Last Modified: 2012-08-13
Hi, I have a query that i need to run, do some stuff, and then insert result into a table in an access database. So, I've run the query and put it into a datatable... or dataview or dataset... don't know exactly what i need, but i can do all three.

So, when I have my result... how do i get that back into the database... what's the easiest and quickest way? Do i put it from a dataset, or datatable or dataview into the database (in a new table in this case) or how do i go about this? (can you show code required?)

thanks Aiden
0
Comment
Question by:AidenA
  • 7
  • 5
12 Comments
 
LVL 7

Expert Comment

by:EYoung
ID: 35019096
I would use the "INSERT INTO" command to put new records into an existing or new table.  Here is how the INSERT INTO command works.  You would just need to put it in a loop command if there is more than one record to add to the table.  Best of luck.


            mKey = Trim(mUser_Name) & "-" & FormatDateTime(Now, DateFormat.ShortDate) & "-" & FormatDateTime(Now, DateFormat.LongTime)
            Using connSQL As New SqlConnection(My.Settings.SQLConnection)
                mWork_String_1 = "INSERT INTO (your_table_name_here)" _
                    & " (mKey, Parent_Offer, Parent_Description, Child1_Offer, Child2_Offer, Child3_Offer, Offer_Year, Offer_Season)" _
                    & " Values ('" & mKey & "', '" & txtParent_Offer.Text & "', '" _
                    & txtParent_Description.Text & "', '" & txtWomens_Offer.Text & "', '" _
                    & txtMens_Offer.Text & "', '" & txtOther_Offer.Text & "', " _
                    & cboYear.Text & ", '" & mOffer_Season & "')"

                'Using connSQL As New SqlConnection(My.Settings.SQLConnection)
                Using cmdSQL As New SqlCommand(mWork_String_1, connSQL)
                    connSQL.Open()
                    cmdSQL.ExecuteNonQuery()
                    connSQL.Close()
                End Using
            End Using
0
 

Author Comment

by:AidenA
ID: 35019291
Hi thanks, but not totally sure that's what i'm looking for. Remember the select query has already been run, the result is in a dataset / datatable / dataview or whatever. Now I want to move information from that dataset / datatable / dataview into a new table in the database... exactly like that. Don't want to do it any other way? just want to know how to move data from dataset / datatable / dataview (whichever is appropriate) into a new table in a database?
0
 
LVL 7

Expert Comment

by:EYoung
ID: 35019398
If I understand you correctly, you want to read an existing data source and write the records out to a table.  If that is correct, the the INSERT INTO command would be a good choice to do the writing.  Just read each record in the data source and write them out using the above command.
0
 

Author Comment

by:AidenA
ID: 35025720
thanks but can you be more specific with exactly what i need to do? First of all, where's the best place to have this data? It can go into a dataset / datatable / dataview, where do I want to put it? And then how exactly do I run an insert into query on the dataset / datatable / dataview?

Also, that sounds like it's going to be very slow... what if you had thousands of records? I assume it would be slower then just running one 'Insert into' query on the whole table? Is there any way to do this so that you don't have to loop through every record in the dataset / datatable / dataview and run a query on each one?
0
 
LVL 7

Expert Comment

by:EYoung
ID: 35028776
The "easiest and quickest way" as per your original question is to use the INSERT INTO command even if you are loading a thousand records.  However, if you are loading ten thousand or more records, then you might consider reading up on the BULK INSERT command.  Here is a link and you can Google for more directions:

http://msdn.microsoft.com/en-us/library/ms188365.aspx
0
 

Author Comment

by:AidenA
ID: 35029110
yeah that's not really helping to be honest. I've spent ages on the internet looking at *exactly* how to do that, and i also came across the bulk insert (for sql mostly, but this requires an oledb command), but i don't know how to write it exactly, find info on this on internet doesn't seem so easy in this case. I could spend days figuring this out... but that's why i asked a question about it. Anyway, I managed to do a work around so it's not necessary anymore really, but if you can give me syntax in vb.net of how to get a record from a dataset / datatable / dataview into a database table that's what i'm giving the points for? that's what the question is above?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 7

Accepted Solution

by:
EYoung earned 500 total points
ID: 35029515
Here is an example for using a SQL Stored Procedure to read a SQL table into a DataTable then reading the DataTable and write each record out to another SQL table.  This example is from a VB 2008 Windows Application.  You will, of course, need to change the names to your names.


Using connSQL As New SqlConnection(My.Settings.SQLConnection)
            mWork_String_1 = "MAPS_GB_Get_Some_Records_for_One_Offer"
            Using cmdSQL As New SqlCommand(mWork_String_1, connSQL)
                cmdSQL.CommandType = CommandType.StoredProcedure
                cmdSQL.CommandText = mWork_String_1
                cmdSQL.Parameters.Add("@Offer_Id", SqlDbType.Char).Value = mOffer_Id
                cmdSQL.Parameters.Add("@Audit_Which_Version", SqlDbType.Char).Value = mAudit_Which_Version
                connSQL.Open()
                cmdSQL.ExecuteNonQuery()

                daFFCT_Offer_Product = New SqlDataAdapter(cmdSQL)
                dtFFCT_Offer_Product = New DataTable
                daFFCT_Offer_Product.Fill(dtFFCT_Offer_Product)
                Dim drFFCT_Offer_Product As DataRow

                For Each drFFCT_Offer_Product In dtFFCT_Offer_Product.Rows
                    mItem_Number = drFFCT_Offer_Product("Offer_Product_Id").ToString

                    Using connSQL4 As New SqlConnection(My.Settings.SQLConnection)
                        mWork_String_1 = "INSERT INTO MAPS_GB" _
                             & " (User_Name, InDesign_GB_File_Name, Item_Number, Item_Description, Item_Found)" _
                             & " Values ('" & mUser_Name & "', '" & mWork_File_Name & "', '" & mItem_Number & "', '', 'N'" & ")"
                             Using cmdSQL4 As New SqlCommand(mWork_String_1, connSQL4)
                                            connSQL4.Open()
                                            cmdSQL4.ExecuteNonQuery()
                                            cmdSQL4.Dispose()
                                            connSQL4.Close()
                                            connSQL4.Dispose()
                             End Using
                    End Using

                Next drFFCT_Offer_Product

                cmdSQL.Dispose()
                connSQL.Close()
                connSQL.Dispose()
            End Using
        End Using

Open in new window

0
 

Author Comment

by:AidenA
ID: 35034969
thanks for that.

That seems a very cumbersome way of doing it... and must be very slow? So it looks like your extracting the data from your database, putting it into a datatable, iterating through that datatable, then extracting the data you want, and then doing a seperate update query based on the data you extracted.

Well, I could have done that myself of course, but i can't believe there is no better way to do it? In that case I'd have to put all the values into variables... what if there was 50 columns or more? it would be very messy.

There must be a more direct way of getting data from a datatable directly into a database, without having to go through this intermediate step of extracting it from the datatable in code and then running multiple sql queries? Or is this really the only way you know of? Have you used this bulk query syntax before, and is that only for sqlserver or whatever and not for oledb?
0
 
LVL 7

Expert Comment

by:EYoung
ID: 35037935
You asked for examples of how to transfer records from a datasource to a SQL table.  I have given you two examples.  The second one was extracted from an existing program of mine that did much more than you are seeing.  I removed the extra lines of code to cut it down the code as much as possible so you would get the idea.

Another idea would be to create a dts that you could call from your program.  Dts's run on the server in SQL Server and are quick.
0
 

Author Comment

by:AidenA
ID: 35038391
yeah i just can't believe that's the best way to do it. Maybe it is, but I'd just be surprised. The dataadapters have an update command (as in http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update.aspx)... I was thinking that might be the way to do it... but the particular example there seems to call one that's already set with the OledbCommandBuilder... so wasn't sure how to get that working.

Anyway, ok thanks for the help, I'll come back to this later as I've other problems now!
0
 
LVL 7

Expert Comment

by:EYoung
ID: 35038464
You're welcome.  Best of luck.
0
 
LVL 7

Expert Comment

by:EYoung
ID: 35038564
You might consider using a dts in SQL Server.  Dts's can read from and write to many different data sources.  Additionally, they are quick and can be customized at run time using passed paramaters.

The DataSet/DataTable/TableAdapter you are using is reading records from one data source and you are wanting to write those records (or selected fields) out to another data source quickly.  That is what dts's can do.

Here is a link that explains dts:  http://en.wikipedia.org/wiki/Data_Transformation_Services

If you are using SQL 2005 or later, then dts has been replaced by ssis which is even better.  Here is a link:  http://en.wikipedia.org/wiki/SQL_Server_Integration_Services

Good luck.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now