BCP Utility - Usage

Question

BCP Utility - Usage

SQL!$@w$0ME

SSChampion

Points: 12349
More actions
August 10, 2016 at 8:02 am

#329174

Hi Folks,
Why should we use BCP Utility. Any one can explain the scenarios where this tool is useful.
BCP or BULK INSERT? which is quicker and/or resource efficient?
Looking for bulk load from OLTP to Azure SQL datawarehouse.
Thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

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

Phil Parkin SSC Guru Points: 246716 More actions · Answer 1

SQL!$@w$0ME (8/10/2016)
Hi Folks,
Why should we use BCP Utility. Any one can explain the scenarios where this tool is useful.
Thanks!

Not sure what sort of answer you are looking for here.

BCP is just another way of importing or exporting data between SQL Server and a data file.

So the scenarios would be where you have data you want to export to a file, or to import from a file ...

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

I haven't tested, but I've been told that it needs less resources and for that reason it run faster.

Each system has different needs, so it shouldn't be considered better or worse than something else.

In a previous company, we used it to prevent giving access to production systems when we needed to export information to the data warehouse. Permissions were only given to a repository where the OLTP system would leave the files and the ETL team would take the information without having to access the DB Servers.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 3

For bulk load from OLTP to Azure SQL datawarehouse, is this tool helpful or any other option?

Thanks!

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 4

SQL!$@w$0ME

SSChampion

Points: 12349

August 10, 2016 at 8:46 am

#1894812

Thanks!

Phil Parkin SSC Guru Points: 246716 More actions · Answer 5

SQL!$@w$0ME (8/10/2016)
For bulk load from OLTP to Azure SQL datawarehouse, is this tool helpful or any other option?
Thanks!

Not really helpful, because neither of these is a data file.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

Phil Parkin (8/10/2016)
SQL!$@w$0ME (8/10/2016)
For bulk load from OLTP to Azure SQL datawarehouse, is this tool helpful or any other option?
Thanks!
Not really helpful, because neither of these is a data file.

I wouldn't say that. Depending on the connection speed, it might be preferable to export from OLTP to a file, optionally move the file and then import from the file into Azure SQL. This might reduce locking time for the tables involved.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246716 More actions · Answer 7

Luis Cazares (8/10/2016)
Phil Parkin (8/10/2016)
SQL!$@w$0ME (8/10/2016)
For bulk load from OLTP to Azure SQL datawarehouse, is this tool helpful or any other option?
Thanks!
Not really helpful, because neither of these is a data file.
I wouldn't say that. Depending on the connection speed, it might be preferable to export from OLTP to a file, optionally move the file and then import from the file into Azure SQL. This might reduce locking time for the tables involved.

Yeah ... I did consider this possibility when I wrote that comment, but as the OP did not even understand the concept of BCP, I assumed that a three-stage export/transport/import BCP-powered process would not be considered 'helpful'. Nonetheless, good point.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 8

Thanks Luis. Is there a better option instead of BCP for bulk load from OLTP to Azure SQL datawarehouse.

SSIS?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 9

SQL!$@w$0ME (8/10/2016)
Thanks Luis. Is there a better option instead of BCP for bulk load from OLTP to Azure SQL datawarehouse.
SSIS?

There's no best option. You have to define it based on your environment.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 10

Thanks. Also, BCP or BULK INSERT? which is quicker and/or resource efficient?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 11

SQL!$@w$0ME (8/10/2016)
Thanks. Also, BCP or BULK INSERT? which is quicker and/or resource efficient?

Test 😉

Also, be aware that permissions are handled different between both options.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2