VB.NET datatable into SQL table - what is the best way??

Question

Post reply

VB.NET datatable into SQL table - what is the best way??

Vladimir Kim

SSC-Addicted

Points: 424
More actions
December 5, 2006 at 2:02 pm

#193425

Hi,
I build a datatable in VB.NET program and would like to insert all the records from that datatable
into the SQL table with the same column fields (same names and datatypes).
What would be the most effective way to do that?
Thank you in advance.
Vlad.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Vladimir Kim SSC-Addicted Points: 424 More actions · Answer 1

I'm trying to avoid multiple insert statements.

Thanks.

Russel Loski SSCertifiable Points: 7054 More actions · Answer 2

The only way that I know to do this is to use xml. You create a stored procedure that receives a text parameter. Then using OpenXML you can insert all of the records into a table.

Russel Loski, MCSE Business Intelligence, Data Platform

Charles Kincaid SSChampion Points: 13593 More actions · Answer 3

Vlad,

VB .Net does not have any native storage engine. Where is the data actually stored?

If the answer is "in a an MDB file" then that is Access. SQL Server can import that.

ATBCharles Kincaid

Lowell SSC Guru Points: 323482 More actions · Answer 4

I took his question to mean if you created a datatable in vb.NET, then inserted data into it, how can you create the table on SQL server, and then insert the contents of the datatable into the server's now table; that data is stored in memory by the application until the datatable is set to nothing, or exported to xml or something.

i know that the sql adapter will automatically create all the insert/update/delete statements behind the scenes for a datatable you load and then fiddle with; the command for your dataadapter.UpdateDataTable performs the actual list of commands that get created;

i think you'd have to do the following:

say you had a local datatable named dtLog you created and populated in your application.

call DataAdapter.ExecuteNonQuery with the syntax for the table creation.(CREATE TABLE dtLog ....)

load a datatable with something like "SELECT * dtLog WHERE 1=2"

dim dt as New Datatable

DataAdapter.LoadDataTable(sql,dt)

dt=dtlog 'might work? alternatively, a loop to dt.addrows and set each row = one from dt log;

DataAdapter.UpdateDataTable(dt)

i think that might work; hopefully it will point you in the right direction.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Brian Adams-265363 Mr or Mrs. 500 Points: 509 More actions · Answer 5

Vlad,

Look into the sqlbulkcopy object. If you used FillSchema to populate the columns of the datatable from the source table in SQL, you should be able to assign the connection and a few other properties to an instance of the SQLBulkCopy object and write it back to the Source table in sql.

Hope this helps,

Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(yoursqlconnection)

If yourdatatable.Rows.Count > 0 Then

bulkCopy.DestinationTableName = "yoursqltable"

bulkCopy.BulkCopyTimeout = (timeoutvalue)

bulkCopy.BatchSize = 10000

bulkCopy.WriteToServer(yourdatatable)

End If

Vladimir Kim SSC-Addicted Points: 424 More actions · Answer 6

I'm getting the data from the user input (windows app).

Vladimir Kim SSC-Addicted Points: 424 More actions · Answer 7

Thank you for your responses. I'm considering using SqlXmlAdapter or SqlBulkCopy.