Редагувати

Поділитися через

Bulk copy data with bcp to SQL Server on Linux

  • Стаття

Applies to: SQL Server - Linux

This article shows how to use the bcp utility to bulk copy data between an instance of SQL Server on Linux and a data file in a user-specified format.

You can use bcp to import large numbers of rows into SQL Server tables or to export data from SQL Server tables into data files. Except when used with the queryout option, bcp requires no knowledge of Transact-SQL. The bcp command-line utility works with Microsoft SQL Server running on-premises or in the cloud, on Linux, Windows or Docker and Azure SQL Database and Azure Synapse Analytics.

This article shows you how to:

  • Import data into a table using the bcp in command
  • Export data from a table using the bcp out command

Install the SQL Server command-line tools

bcp is part of the SQL Server command-line tools, which aren't installed automatically with SQL Server on Linux. If you haven't already installed the SQL Server command-line tools on your Linux machine, you must install them. For more information on how to install the tools, select your Linux distribution from the following list:

Import data with bcp

In this tutorial, you create a sample database and table on the local SQL Server instance (localhost) and then use bcp to load into the sample table from a text file on disk.

Create a sample database and table

Let's start by creating a sample database with a simple table that is used in the rest of this tutorial.

  1. On your Linux box, open a command terminal.

  2. Copy and paste the following commands into the terminal window. These commands use the sqlcmd command-line utility to create a sample database (BcpSampleDB) and a table (TestEmployees) on the local SQL Server instance (localhost). Remember to replace the username and <your_password> as necessary before running the commands.

Create the database BcpSampleDB:

sqlcmd -S localhost -U sa -P <your_password> -Q "CREATE DATABASE BcpSampleDB;"

Create the table TestEmployees in the database BcpSampleDB:

sqlcmd -S localhost -U sa -P <your_password> -d BcpSampleDB -Q "CREATE TABLE TestEmployees (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));"

Create the source data file

Copy and paste the following command into your terminal window. We use the built-in cat command to create a sample text data file with three records save the file in your home directory as ~/test_data.txt. The fields in the records are delimited by a comma.

cat > ~/test_data.txt << EOF
1,Jared,Australia
2,Nikita,India
3,Tom,Germany
EOF

You can verify that the data file was created correctly by running the following command in your terminal window:

cat ~/test_data.txt

This should display the following in your terminal window:

1,Jared,Australia
2,Nikita,India
3,Tom,Germany

Import data from the source data file

Copy and paste the following commands into the terminal window. This command uses bcp to connect to the local SQL Server instance (localhost) and import the data from the data file (~/test_data.txt) into the table (TestEmployees) in the database (BcpSampleDB). Remember to replace the username and <your_password> as necessary before running the commands.

bcp TestEmployees in ~/test_data.txt -S localhost -U sa -P <your_password> -d BcpSampleDB -c -t  ','

Here's a brief overview of the command-line parameters we used with bcp in this example:

  • -S: specifies the instance of SQL Server to which to connect
  • -U: specifies the login ID used to connect to SQL Server
  • -P: specifies the password for the login ID
  • -d: specifies the database to connect to
  • -c: performs operations using a character data type
  • -t: specifies the field terminator. We are using comma as the field terminator for the records in our data file

Note

We aren't specifying a custom row terminator in this example. Rows in the text data file were correctly terminated with newline when we used the cat command to create the data file earlier.

You can verify that the data was successfully imported by running the following command in your terminal window. Remember to replace the username and <your_password> as necessary before running the command.

sqlcmd -S localhost -d BcpSampleDB -U sa -P <your_password> -I -Q "SELECT * FROM TestEmployees;"

This should display the following results:

Id          Name                Location
----------- ------------------- -------------------
          1 Jared               Australia
          2 Nikita              India
          3 Tom                 Germany

Export data with bcp

In this tutorial, you use bcp to export data from the sample table we created earlier to a new data file.

Copy and paste the following commands into the terminal window. These commands use the bcp command-line utility to export data from the table TestEmployees in the database BcpSampleDB to a new data file called ~/test_export.txt. Remember to replace the username and <your_password> as necessary before running the command.

bcp TestEmployees out ~/test_export.txt -S localhost -U sa -P <your_password> -d BcpSampleDB -c -t ','

You can verify that the data was exported correctly by running the following command in your terminal window:

cat ~/test_export.txt

This should display the following in your terminal window:

1,Jared,Australia
2,Nikita,India
3,Tom,Germany

Related content