A trusty old friend – The BCP utility

One of the requirements that springs up around data stores – regardless of if they are relational, not relational, cloud based or some other variety is (unsurprisingly) the need to access the data for various reasons such as reporting or analysis (amongst other things). Often, this requires moving the data (off-loading the data) to another environment / system where analysis or reporting can take place without the need to impact the main system.

Very often we use tools such as SQL Server Integration Services or Azure Data Factory for this sort of thing. But occasionally we might wish to fall back to a utility that’s been in SQL Server (as well as Sybase) for a long time – and that is the Bulk Copy Program, or BCP for short.

The BCP utility (bcp.exe) can be found in the SQL Server installation directory structure. On my machine it can be found at

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe

Your system may be slightly different. Regardless, the directory should be accessible via the PATH variable and so simply typing bcp or bcp -? should display a list of arguments that the bcp utility accepts.

As an aside, did you also know that you can look at the same display using the PowerShell ISE – which in some cases may provide a more pleasing display

Let’s see how easy it is. We’ll use the sys.configurations table as everybody has that.

Clear-Host;
bcp.exe "SELECT * FROM sys.configurations" queryout "C:\temp\configurations.txt" -Slocalhost -dtempdb  -T -c -t',';

If you do run the above code then you will notice that the text names and descriptions have spaces, this might be a problem. We could handle this in the bcp utility. However, I’d be more inclined to do that in the T-SQL query, but that’s just a personal choice others may prefer another method. But here’s the query I’d use – notice the use of QUOTENAME to enclose the text in double quotation marks.

USE tempdb;
GO

SELECT
	[configuration_id], 
	[name] = QUOTENAME([name], '"'),
	[value], 
	[minimum], 
	[maximum], 
	[value_in_use], 
	[description] = QUOTENAME([description] , '"'), 
	[is_dynamic], 
	[is_advanced]
FROM 
	sys.configurations AS C
ORDER BY 
	C.configuration_id ASC;

Although this is a relatively old utility, I still find it remarkably useful and use it reasonably often – hence this post.

There are other ways to accomplish the same requirement (maybe even without moving the data – but we’ll leave that for another day)

And, by the way, the data that has been extracted is now on disk – so be sure to have a plan that deals with security.

BCP can also be used to input data into a table as well. I’ll leave that example for the moment but will return to it in a later post.

Have a great day

Cheers

Marty.

T-SQL Tuesday #109 – Mentors and Mentees

G’day again,

So, it’s time for T-SQL Tuesday again, the blog party 
started by Adam Machanic (b|t) that just keeps on rolling.
I just looked at the rules – and they are quite long, hopefully, I’ve
covered them all – if not, I’m sorry 🙂
This time we’re talking about people you’re influenced to get
involved in SQL, strengthen their SQL, join the community or in
any other way.
As I regional mentor in the APAC area I often talk to community
members from SQL, Power BI, Power Platform and various other forms
of the Data Platform – Microsoft or otherwise, I’m sure I’ve influenced
a few – but I’d prefer not to name any (as I’ll possibly get that wrong)
But mentors change as interests change and as we drift between
different projects and steer different courses in life – not just work related. Staying in touch and increasing our networks is a vital skill in this day and age.
I’ve sat in many Data Platform classes and events around the world and listened to some of the well know names in our industry.
Your contribution doesn’t have to be big, taking somebody along to a user group is cool, especially one like SQL Social run by Martin Cairney (t) in Melbourne – a seemingly unique format, in a local pub surrounded by friendly members with great stories and offers of help.
Greg Low (b|t) would be one of my all time favourite presenters, mentors and all round friends.
There’s a lot to name – so I won’t, or we’ll be here all day.
One confession is that I may of had a small degree of influence in releasing the hurricane that  is the @TheHybridDBA on to the world – for that, you can judge me!

Have a great mentoring day.

Cheers

Martin.