Load a huge amount of data in a Sql Server table

Last week I had to load a huge amount of data, about 500,000 rows, in a Sql Server table to make some data processing.

Using the “Sql Server Import and Export Data” tool this was an easy job.

After processing data in my own Sql Server, I had to reload the data to another Sql Server and execute the job again, but in this case I couldn’t use the “Sql Server Import and Export Data” tool.

How would you do that in a way that is as fast as using “Sql Server Import and Export Data” ?

For this purpose, you can use a command line utility called “bcp”, which stands for “bulk copy program”. This utility is usually installed along with Sql Server, but if it is not installed you can download here:

https://docs.microsoft.com/it-it/sql/tools/bcp-utility?view=sql-server-2017

So let’s start by creating some test data to make the job…

Create a test database in your Sql Server instance and execute the following script:

CREATE TABLE BCP_EXAMPLE
(
   ID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
   LAST_NAME VARCHAR(50),
   FIRST_NAME VARCHAR(50),
   PHONE_NUMBER VARCHAR(50),
   WEBSITE VARCHAR(250)
)
GO


DECLARE @I INT = 0

WHILE @I <= 500000
BEGIN
  INSERT INTO BCP_EXAMPLE(LAST_NAME, FIRST_NAME, PHONE_NUMBER, WEBSITE)
  VALUES(CONVERT(VARCHAR(50), @I), CONVERT(VARCHAR(50), @I), '+00' + CONVERT(VARCHAR(50), @I), 'http://www.' + CONVERT(VARCHAR(50), @I) + '.com')

  SET @I = @I + 1
END
GO

You will have noticed that making 500.000 inserts like in the example above takes a lot of time: on my PC takes about 2 minutes.

Now we will use bcp to export the content of the table to a file.

To do this open the Windows command prompt. The folder containing bcp is usually configured in the PATH environment variable, so you should be able to run it from wherever folder you are in, anyway for a default Sql Server 2017 installation you should find it at this location:

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn

First of all, we have to create a format file. A format file is simply a file that contains the structure of the table you are copying.

To create a format file execute the following in the command prompt:

bcp BCP_EXAMPLE format nul -f"C:\Users\dummy\OneDrive\Desktop\test_bcp\BCP_EXAMPLE.fmt" -S (local) -d TestDB -U sa -P dummypwd

Some explanations:

BCP_EXAMPLE is the name of the table you want to create the format file
format nul indicates to bcp that you want to create a format file
-fC:\Users\…\test_bcp\BCP_EXAMPLE.fmt is path and file name of the format file
as you may guess, the other parameters are needed to connect to Sql Server

Note the double quotes around the path: you have to use them if the path contains spaces, otherwise you will get an error.

When you will execute the command above, you will be asked some “questions” about the columns of your table. You can usually leave the default value (which is the one you see in the square brackets) by pressing ENTER.

If you don’t want to confirm each field, you can use the “-n” option that makes bcp generate the format file without user intervention.

Now you can execute bcp to export the data in a file with the following command:

bcp BCP_EXAMPLE out "C:\Users\dummy\OneDrive\Desktop\test_bcp\BCP_EXAMPLE.dat" -f "C:\Users\dummy\OneDrive\Desktop\test_bcp\BCP_EXAMPLE.fmt" -S (local) -d TestDB -U sa -P dummypwd

Here the difference with the previous command:

out C:\Users\…test_bcp\BCP_EXAMPLE.dat is path and file name of the file that will contains the data of the table
-f C:\Users\…\test_bcp\BCP_EXAMPLE.fmt is the format file you just created above

You should see something similar:

bcp file export

To import the data to another server, you will need the following:

tha table must exist on the destination server
the format file and the data file you just created

So to complete the example, we will empty the table BCP_EXAMPLE and reload the data from the file generated with bcp.

Empty the table with this command:

TRUNCATE TABLE BCP_EXAMPLE

then launch the bcp command to import the data:

bcp BCP_EXAMPLE in "C:\Users\dummy\OneDrive\Desktop\test_bcp\BCP_EXAMPLE.dat" -f "C:\Users\dummy\OneDrive\Desktop\test_bcp\BCP_EXAMPLE.fmt" -S (local) -d TestDB -U sa -P dummypwd

And this is the result:

bcp file import

Both export and import took less then 5 seconds on my PC. A great result that becomes more evident when working with a greater number of lines.

Two suggestions before closing:

check Microsoft documentation to learn more about bcp
if you use bcp to make a backup of a table, keep with care also the format file: if you loose it you may not be able to import your data!

Have a nice day!

Related Posts