Import data into SQL Server 2008 Express

Question

I have a very large DataSet containing about 160,000 records. If I go through a dataset and import each record, it can take about 20 minutes before the full dataset is imported into SQL Server.

Is there a faster way to import a dataset directly into a database?

The data set is created from file I, which the user provides, then I have 1 table called let say "ImportTable" containing about 14 columns. Columns correspond to columns in a DataSet.

I am using Visual Studio 2010 Professional with C #.

Thanks in advance!

Answer 1

SqlBulkCopy.

# ( ), DataSet DataTable SQL Server . , -- (RBAR)...

: - SqlDataReader SqlBulkCopy SqlDataReader SQL Server.

Answer 2

bcp. . , , , , , , bcp.

Answer 3

You can use make dataset xml with the DataSet.getXml function. and skip the input parameter for SP.

eg

Create PROCEDURE dbo.MyInsertSP
(
        @strXML varchar(1000) 
)
AS
Begin
    Insert into publishers
   Select     * from OpenXml(@intPointer,'/root/publisher',2)
    With     (pub_id char(4), pub_name varchar(40), city varchar(20), 
                  state char(2),9) country varchar(20))
    exec sp_xml_removedocument @intPointer
RETURN
End

Hope this makes sense.