Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

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?
0
ConnexusDave
Asked:
ConnexusDave
2 Solutions
 
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
 
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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
 
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
 
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now