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.

Is NoSQL still a thing?

G’day,

Looking back, perhaps one of the (many) mistakes that I’ve made in my career was to initially ignore the N0SQL movement.

I’ve focused my career on Relational Database systems – predominantly Microsoft SQL Server – and so using a NoSQL database felt ‘wrong’, for a number of reasons.

Firstly, the name itself – NoSQL – felt like the message was that the SQL language was in someway being “devalued” and ‘kicked to the kerb”

Secondly, the lack of transaction support in NoSQL databases also felt wrong. I assumed SQL implied relational.

I think now-a-days it’s probably better to think of SQL as simply a language to manipulate data (SELECT, INSERT, UPDATE, DELETE etc). (Event if this is a deviation from the technical ANSI definition) Nearly all vendors have their own propriety extensions (Microsoft have T-SQL, Oracle have PL/SQL, Sybase also have T-SQL but it is diverging from the Microsoft language of the same name where it originally has its roots) and pure ANSI standard SQL is hard to find.

We see lots of database (relational or not) using derivatives of SQL.

We also see relational databases using non-relational functionality and non-relational databases starting to use relational functionality.

And so, both relational and non-relational have their place in today’s world and so I wonder how long the term NoSQL will continue to be used.

Quite sometime ago I started to look at CosmosDB – which a lot of people still talk about in 2020 as being the ‘new kid on the block’ – but it’s been evolving for 10 years now and is gaining a good foothold in the market.

If you’ve not seem it before it would be worth a look and although it shares some familiar terms with relational database it is not one and approaching it with the same mindset may land you in a mess – but once you start to get to grips with how it operates and the different models it employs I think it’ll start to grow on you.

Have a great day.

Cheers

Martin.

Generating Insert Statements

G’day,

I been asked a few times lately the best way to generate INSERT statements.

I’d always replied to use SSMS.

I know SSMS has this feature, it’s just not something that I have the need to use on a regular basic. And – as we all know – when you don’t use something regularly you tend to forget where to find it, or – as happens reasonably regularly nowadays – the feature, or tool, gets an update and you find yourself re-learning something you thought that you were already familiar with.

Fortunately, the feature to generate INSERT statements was pretty much where I expected – but it did take a few minutes to find the exact spot, so I thought that I’d mention it here – just in case anybody else is looking too, or you just need a reminder that SSMS is very capable of performing this task.

Simply right click on the database that contains the table – or tables – that you need to generate INSERT statements for and select TASKS

Select TASKS

From the resulting menu choose GENERATE SCRIPTS

Select Generate Scripts

Then it’s just a simply case of following the Wizard through.

If you’ve not started this Wizard before then you’ll see the Introduction page – you can safely tick “Don’t show this page again”, so you basically don’t see this page again the next time you start the same Wizard.

You probably don’t want to see this page every time you start the Wizard.

Then, on the next page, either select either all objects to script – or just the specific one(s) that you want. In my case I’m interested in one particular table.

Select the object(s) that you want to script.

On the next page select the location where you want the file to go. I’m selecting a new query editor window, but you could select a file location or the clipboard.

Then hit the “Advanced” button

Selecting the Advanced button is important

Clicking the Advanced button will show the “Advanced Scripting Options” – scroll down until you find “Types of data to script” and select your choice from the drop down. I’ve chose to script “Schema and data” – but you can also select just Data or only schema.

Select the option that best suits your need.

The next page will give you a summary

Hit Next on the summary page

The it’s just a case of hitting Next and then your script is generated. Simply hit finish on the next screen

You’ll see a summary and hit Finish

And you should see your script. Save it to a location of your choice.

Finally – you see your nice INSERT script

I hope this was useful.

Have a great day

Cheers

Marty.