using SqlbulkCopy to do data migration

Question

using SqlbulkCopy to do data migration

lovelanjunping

SSC Veteran

Points: 217
More actions
July 5, 2011 at 7:56 am

#253209

does anybody ever used the SqlbulkCopy Class in .net framework to do data migration ?
i am trying to do it , but i found that when get the datasource from database . the sql statement is
"select * from tablename". i have a concern about that. if the datasource table have huge data .
if there has performance issue.
thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i've used SQLBulkCopy for moving data from other sources to SQL, and i like it a lot...it is really fast.

i suspect the SELECT * stuff you are seeing is probably just getting the column names, and not downloading the data fromt he server; my example below just puts data into SQL.

in my case, I've got a DataTable(dt) in memory that i've loaded and massaged from other sources, and i'm putting data into the table on SQL whether it exists or not.

'no errors

'now use SQLBulk Copy to get the data into the server, instead of RBAR:

'note my connection string is based on String.Repalce of this:

'Private Const SqlConnectionFormat As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Application Name=MyApplicationName.exe;"

'Private Const SqlTrustedConnectionFormat As String = "data source={0};initial catalog={1};Trusted_Connection=True;Application Name=MyApplicationName.exe;"

Try

Dim myConn As New SqlConnection(Me.ConnectionString)

myConn.Open()

Using myBulkCopy As New SqlBulkCopy(myConn)

myBulkCopy.DestinationTableName = "[" & DestinationTable & "]"

myBulkCopy.WriteToServer(dt)

End Using

Catch ex As Exception

Debug.Print(ex.Message)

End Try

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!

lovelanjunping SSC Veteran Points: 217 More actions · Answer 2

Lowel, thanks for your reply.

yes , i only got the column i needed. if the data source table has 10 millions data ,i will using the

select column1,column2... from tablename catch the data from it.

i concern that it would be have high pressure to db server and App Server

now i call the sqlbulkcopy in my .net project , it raised memory issue when i did test.

i am not sure if the data would be loaded into the App Server Memrory in advance, and then will be written into database .

thanks