What is the fastest way to move a datatable up to a MSSQL Server.

I have created a datatable in memory that is created from various souces.
When I am done I want to load this table into my MSSQL server.
Right now I do it by reading 1 line at a time from the datatable and I do an insert into the MSSQL.
Is there a faster way to load the Datatable to the MSSQL?
ConnexusDaveAsked:
Who is Participating?
 
chapmandewCommented:
create procedure parsexml
(
@xml xml
)
as
begin
insert into tablename(fld1, fld2)
select t.c.value('@attribute', 'varchar(10)'), t.c.value('@attribute2', 'varchar(10)')
from @xml.nodes('/header/body') t(c)
end
go

about xquery: http://msdn.microsoft.com/en-us/library/ms345122(SQL.90).aspx
0
 
chapmandewCommented:
use integration services.  right click the db, got to all tasks and then export  (through SSMS)

by the way, when you say "in memory datatable"...do you mean a temp table?
0
 
ConnexusDaveAuthor Commented:
Ok, but I need to be able to do this through vb.net so the orginal program has control and can what you said happen.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
chapmandewCommented:
OK, I gotcha.  One way, so long as the table is not super big, is to serialize it to XML, and then pass that XML string to a stored proc with an XML parameter and then use XQuery in the proc to insert into the table.
0
 
ConnexusDaveAuthor Commented:
The table contains over 8000 records.
Is there anything like a bulk load or a way I can super load an array that could be "Bulk" pushed/loaded.
0
 
chapmandewCommented:
there is for other sql tables, but not for a VB datatable object.  

take a look at this link and let me know if it helps:  http://www.sqlservercentral.com/articles/SQL+Server+2008/66554/
0
 
ConnexusDaveAuthor Commented:
Ok I do know to call a stored procedure in vb.net so if I can get some help writing what I need as a stored procedure the points are yours...
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
The fastest way is to CREATE the table in SQL that you want to put the results.

Then follow this article to ensure that the SqlDataAdapter is created and then use the SqlCommandBuilder to create you the statements for the update and insert.

Then execute the DataAdapter.Fill(table).
Then DataAdapter.Update(table).

http://dotnetperls.com/sqlcommandbuilder-example
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.

All Courses

From novice to tech pro — start learning today.