When it comes to loading small sets of data into SQL Server, there are plenty of GUI-based tools available, such as SSMS, Azure Data Studio, and Visual Studio. However, what if you don’t have access to these tools and need to export/import data from/into a SQL Server instance running on Linux? In such cases, the BCP (Bulk Copy Program) utility comes to the rescue.

Installing mssql-tools

To use the BCP utility on Linux, you need to install the mssql-tools package separately. The installation steps may vary depending on the Linux distribution you are using. For example, on Ubuntu, you can install mssql-tools by following these commands:

$ curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
$ curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
$ apt-get update
$ apt-get install mssql-tools

Once the installation is complete, you will have access to the BCP utility.

Exploring Data

Before importing data, it’s always a good idea to explore the dataset. In this example, we have a CSV file called “hw_25000.csv” that contains biometric information of 25,000 individuals, including their ID, height, and weight. To get a quick overview of the data, you can use the “head” and “tail” commands to check the first and last ten rows of the file:

$ head -10 hw_25000.csv
$ tail -10 hw_25000.csv

By examining the data, you can ensure its cleanliness and verify the structure of the dataset.

Importing Data with BCP

To import data using the BCP utility, you need to provide several arguments, including the server name, SQL Server user name, password, target database, and field terminator. Here is an example command to import data from a comma-separated CSV file called “hw_25000.csv” into a table called “HW” in the “Biometrics” database:

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

After executing the command, you will see the progress and the number of rows sent to SQL Server. Once the import is complete, you can verify the data by running a simple query:

sqlcmd -S localhost -d Biometrics -U sa -P MyP@ssw0rd# -I -Q "SELECT TOP 10 * FROM HW;"

This will display the first ten rows of the “HW” table, confirming that the data has been successfully imported.

Exporting Data with BCP

Exporting data with BCP is similar to importing. You need to provide the same arguments, but this time use the “out” option to specify that you are exporting data. Here is an example command to export all the data from the “HW” table into a comma-separated file called “hw_bcp_out.csv”:

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

After executing the command, you will see the progress and the number of rows successfully exported. You can check the exported file in your current folder.

Conclusion

The BCP utility is a powerful command-line tool that allows you to import and export data in SQL Server, regardless of the operating system. Whether you are working on Windows, Linux, or Docker containers, BCP provides a reliable and efficient way to handle data transfer. By mastering this tool, you can easily manage data in SQL Server without relying on GUI-based tools.