Best practice for transferring a large amount of data. Using Stored Procedure or SQL Bulk Copy

Question

I have a huge amount of data from one server's different databases and tables to be transferred into another server's different databases and tables. I've created a stored procedure and my C# application doing is just passing a parameter into my stored procedure.

Lately, I discovered the SQL Bulk which is used by many programmers for transferring huge amount of data.

So which of this two is the best practice to do for transferring huge amount of data and why?

Answer 1

Personally, rather than go through bulk copy, if the data is of a reasonable size I'd do the following:

1. Copy the tables into a new (empty) database,
2. Disconnect and move the new database to the other server,
3. Connect the database to the new server,
4. Load your data into whatever destination tables you'd like.

Of course, if this is something you need to do more than once, that's likely not optimal. For a one-off, though, it should do the trick without having to fiddle with other methods.

Answer 2

Bulk Insert is blazing fast! I don't think anything is faster than doing something like this.

BULK INSERT EmployeeDB.dbo.Employees
FROM 'C:\Data\EmployeeData_c.dat'
WITH 
  (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\r\n'
  );

See the link below for more details.

https://www.simple-talk.com/sql/learn-sql-server/bulk-inserts-via-tsql-in-sql-server/