Introduction

Loading smalls set of data into SQL Server has been always easy on Windows. Moreover if you like GUI based tools, there are plenty of options. We have SSMS, Azure Data Studio, Visual Studio (SSDT), SSIS, Toad for SQL Server, RapidSQL to name a few. If you are interested to learn how to use Azure Data Studio, I recommend you look at my previous article.

Now, let's take a step back and imagine you have no access to any of these GUI based tools for a moment. To make things complex, one of your clients is asking you to export/import data from/into a SQL Server instance running on Linux (VM or container).

Luckily for us, the BCP (Bulk Copy Program) utility is available for Linux as part of the command line tools package called mssql-tools.

Installing mssql-tools

The mssql-tools package comes installed by default when using a Docker container image of SQL Server, unfortunately, this is not what happens when SQL Server is manually installed on a Linux VM or bare metal machine. We have to install mssql-tools separately.

I will be using Ubuntu for this article, this is very important to note because the installation steps are a little bit different if you want to use RedHat.

The first step is to register the public repository key used by apt to authenticate the packages, then we just simply register Microsoft's official repository for Ubuntu, here are the commands:

[dba mastery] $ curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
[dba mastery] $ curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

Now that we have the repository ready, we have to update the source list and then proceed with the installation of the mussel-tools package as follows:

[dba mastery] $ apt-get update 
[dba mastery] $ apt-get install mssql-tools

Once the installation is complete, we have sqlcmd and bcp ready to use. Both command-line utilities will be installed under this path: /opt/mssql-tools/bin/. As an additional step, you can add this folder as part of the $PATH environment variable, but I will skip that part for now.

Exploring data

There are multiple sources to get CSV files with sample data all over the internet, however, in this example I will use the filename "hw_25000.csv". The file in question is composed of biometric information from 25,000 individuals separated in three columns: ID, height and weight.

Because I will be using command line base tools I want to make sure my data is clean, so the very first thing I want to do is to learn more about my dataset. Checking the first and last ten rows of the file will be the easiest way to do a quick check, I will use the head and tail commands to make that happen.

I would check the first ten rows first using the "head -10" command followed by the filename:

[dba mastery] $ head -10 hw_25000.csv 
1, 65.78331, 112.9925
2, 71.51521, 136.4873
3, 69.39874, 153.0269
4, 68.2166, 142.3354
5, 67.78781, 144.2971
6, 68.69784, 123.3024
7, 69.80204, 141.4947
8, 70.01472, 136.4623
9, 67.90265, 112.3723
10,66.78236,120.6672

At glance, the first column data corresponds to an ID or some kind of correlative number. The second column displays height data, while the third columns holds the weight data. I can confirm, I don't have dirty data in the first ten rows.

Let's move to check the last ten rows of the file now. This is pretty much the same as the "head" command, specifying the number of rows we want to see but in reverse. That means we will see the last ten rows of the file:

[dba mastery] $ tail -10 hw_25000.csv 
24991, 69.97767, 125.3672
24992, 71.91656, 128.284
24993, 70.96218, 146.1936
24994, 66.19462, 118.7974
24995, 67.21126, 127.6603
24996, 69.50215, 118.0312
24997, 64.54826, 120.1932
24998, 64.69855, 118.2655
24999, 67.52918, 132.2682
25000, 68.87761, 124.8742

This concludes, the first and last ten rows of my file has accurate and clean data. The most important, I was able to confirm the ID, height and weight structure is looking good.

Let's move on to import some data, using the "hw_25000.csv" source file with BCP.

Pre-requisites

• Have a database created before the import process
• Have a target table created before the import process

Importing data with BCP

I will provide you with the basic example of using BCP to import data in Linux. There are many more complex scenarios, for what I strongly recommend to check BCP's utility documentation at Microsoft Docs. The BCP utility requires a few arguments when importing data. Let's take a look at each one of them:

• -S: The server name or IP address to connect
• -U: SQL Server user name, this is the login we will use to connect
• -P: Password of the SQL Server login used for the connection
• -d: The target database
• -c: It specifies the operation is made using a character data type (optional)
• -t: It specifies the field terminator, it can be a comma or a tab
• in: Specifies we are importing data into a database

Here is how the BCP command looks like when importing data into a table called "HW" that belongs to the "Biometrics" database using a comma-separated CSV file called hw_25000.csv:

bcp HW in hw_25000.csv -S localhost -U sa -P MyP@ssw0rd# -d Biometrics -c -t  ','

Right after executing the command I can see the output on screen:

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
1000 rows sent to SQL Server. Total sent: 7000
1000 rows sent to SQL Server. Total sent: 8000
1000 rows sent to SQL Server. Total sent: 9000
1000 rows sent to SQL Server. Total sent: 10000
1000 rows sent to SQL Server. Total sent: 11000
1000 rows sent to SQL Server. Total sent: 12000
1000 rows sent to SQL Server. Total sent: 13000
1000 rows sent to SQL Server. Total sent: 14000
1000 rows sent to SQL Server. Total sent: 15000
1000 rows sent to SQL Server. Total sent: 16000
1000 rows sent to SQL Server. Total sent: 17000
1000 rows sent to SQL Server. Total sent: 18000
1000 rows sent to SQL Server. Total sent: 19000
1000 rows sent to SQL Server. Total sent: 20000
1000 rows sent to SQL Server. Total sent: 21000
1000 rows sent to SQL Server. Total sent: 22000
1000 rows sent to SQL Server. Total sent: 23000
1000 rows sent to SQL Server. Total sent: 24000
1000 rows sent to SQL Server. Total sent: 25000
25000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 693    Average : (36075.0 rows per sec.)

It took 693 milliseconds to import 25,000 records, not bad at all. I'm using a SQL Server 2017 container with one CPU and two gigabytes of RAM.

We can see from the output that there were no errors, however, I would like to verify the data running a simple query to check the first ten rows of the HW table:

sqlcmd -S localhost -d Biometrics -U sa -P MyP@ssw0rd# -I -Q "SELECT TOP 10 * FROM HW;"
Id          Height           Weight          
----------- ---------------- ----------------
          1 65.78331         112.9925
          2 71.51521         136.4873
          3 69.39874         153.0269
          4 68.2166          142.3354
          5 67.78781         144.2971
          6 68.69784         123.3024
          7 69.80204         141.4947
          8 70.01472         136.4623
          9 67.90265         112.3723
         10 66.78236         120.6672
(10 rows affected)

These first ten rows match perfectly with the ones I checked using the "head -10" command, this is a really good indicator my data looks as expected.

Exporting data with BCP

Exporting data is pretty straightforward, in this example, I will "dump" all the data from the table created in the previous section. The arguments are basically the same with a slight change, we are exporting data (out):

• out: Specifies we are exporting data into a database

Once again, I will use the same database, table this time exporting the data into a comma-separated file called hw_bcp_out.csv. Here is how the BCP command looks:

bcp HW out hw_bcp_out.csv -S localhost -U sa -P MyP@ssw0rd# -d Biometrics -c -t  ','

Right after executing the command I can see the output on screen:

Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
1000 rows successfully bulk-copied to host-file. Total received: 4000
1000 rows successfully bulk-copied to host-file. Total received: 5000
1000 rows successfully bulk-copied to host-file. Total received: 6000
1000 rows successfully bulk-copied to host-file. Total received: 7000
1000 rows successfully bulk-copied to host-file. Total received: 8000
1000 rows successfully bulk-copied to host-file. Total received: 9000
1000 rows successfully bulk-copied to host-file. Total received: 10000
1000 rows successfully bulk-copied to host-file. Total received: 11000
1000 rows successfully bulk-copied to host-file. Total received: 12000
1000 rows successfully bulk-copied to host-file. Total received: 13000
1000 rows successfully bulk-copied to host-file. Total received: 14000
1000 rows successfully bulk-copied to host-file. Total received: 15000
1000 rows successfully bulk-copied to host-file. Total received: 16000
1000 rows successfully bulk-copied to host-file. Total received: 17000
1000 rows successfully bulk-copied to host-file. Total received: 18000
1000 rows successfully bulk-copied to host-file. Total received: 19000
1000 rows successfully bulk-copied to host-file. Total received: 20000
1000 rows successfully bulk-copied to host-file. Total received: 21000
1000 rows successfully bulk-copied to host-file. Total received: 22000
1000 rows successfully bulk-copied to host-file. Total received: 23000
1000 rows successfully bulk-copied to host-file. Total received: 24000
1000 rows successfully bulk-copied to host-file. Total received: 25000
25000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 123    Average : (203252.0 rows per sec.)

Nice! The 25,00 rows were copied into a comma-separated file that contains all the data from the HW table. Let's check my current folder to make the hw_bcp_out.csv file exists using the list (ls -l) with details command:

[dba mastery] $ ls -l
total 1212
-rw-r--r-- 1 root root 608306 Dec 13 04:38 hw_25000.csv
-rw-r--r-- 1 root root 608307 Dec 13 05:37 hw_bcp_out.csv

I can see two files, the one I used during the import example (hw_25000.csv) and the other created by the BCP out (hw_bcp_out.csv). In case you want to go the extra mile, you can check the first and last ten rows using the "head" and "tail" commands as before, but I will skip that for now.

Conclusion

The BCP utility is a very powerful cross-platform command-line tool we can use to import/export data into/from a SQL Server instance running on any environment (Windows, Linux, Docker containers).