SQL Server: BCP vs SQLCMD - what is more efficient?

Question

I'm writing a program in c# to export SQL Server data from one database and importing it in another. Since these two servers are not connected I need to choose a method such as bcp.

1. What are the differences between these two? Is one more efficient than the other? And in what scenarios?
2. What are the known limitations/compatibility issues of each?
3. What other methods exist to export data from SQL Server into files and import from them?
4. Can I enable compression in these files at the same time as creating them via a command line switch instead of zipping them afterwards?
5. Please include any other aspects which you think is important when making this decision.

Thanks in advance.

Answer 1

MS SQL Server BCP vs SQLCMD. What is more efficient?

What is the difference? You say nothing.

BCP is a tool to mass load bulk data. SQL Command is a tool to submit SQL commands - which can include issuin a command to mass load bulk data. Then there would be no difference. OTOH SQLCommand could be used to issue single insert statements, in which case it would be stupidly slow.

If I were you I would go with exporting the data and then making one SQL script to load the tables using the BULK INSERT command. This is as fast as it gets. The sscript can be executed using SQL CMD.

2.What are the known limitations/compatibility issues of each?

Documented. In the documentation.

5.Please include any other aspects which you think is important when making this decision

Hire a consultant. Stackoverflow is for answering questions, not to give you a thorouth education. "Any other aspect" goes into a lot of details including hardware and network selection and is way beyong the scope of this site.

Answer 2

Doesn't cover BCP, but I did write a blog post comparing a couple of approaches for bulk loading data into SQL Server - compared SqlBulkCopy against batched inserts via SqlDataAdapter.

SqlBulkCopy is worth checking out - the kind of process you'd use is query database 1 and retrieve an SqlDataReader. Pass that SqlDataReader to SqlBulkCopy to persist that data to database 2.