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 FilesMicrosoft SQL ServerClient SDKODBC170ToolsBinnbcp.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:tempconfigurations.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.