Bulk Copy - Use BCP from SQL to concat databases

  • Article

Introduction

This post’s goal is to introduce you to Bulk Copy. We’ll create a SQL query which is going to concatenate several attached databases and extract the data to an output csv file.

The first goal is to demonstrate how to use Bulk Copy as well as solving an issue that I sometime encountered for some customers, meaning, converting several databases into a single one and extract it as CSV.

To fully understand the post, you’ll need some knowledge about SQL and how to make queries.

You can download the final SQL script here.

Bulk Copy

Bulk Copy also known as BCP is a command line tool installed on your server during SQL installation and is located here:

     C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe

BCP is a tool which allows copying large amounts of data from one location to another. You may use this tool from a .NET application, using the class SqlBulkCopy, Powershell or directly in a SQL query via xp_cmdshell. We will see that bcp allows us to extract data directly from a table or through a SQL SELECT query.

You coud find more details concerning bcp at https://msdn.microsoft.com/en-us/library/ms162802.aspx.

Developing the script

Context

Our company has 2 databases saved in *.mdf files, each containing 2 tables Customer and Order. An Order is linked to a Customer through its CustomerID field (1.* relationship). 

Setting the database configuration

To be able to use BCP, we need to execute a command on the server. By default SQL server does not allow this.

To enable this feature just use the following query.

 EXECUTE sp_configure 'show advanced options', 1;RECONFIGURE WITH OVERRIDE;
 EXECUTE sp_configure 'xp_cmdshell', '1';RECONFIGURE WITH OVERRIDE;
 EXECUTE sp_configure 'show advanced options', 0;RECONFIGURE WITH OVERRIDE;

If you don't you'll receive the following error message :

 Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server.  
A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

Testing Bulk Copy

In a first time we are going to test bcp and just export the table customer to as csv file.

For test purposes just insert row into bcp_demo_1 and bcp_demo_2 : 

INSERT INTO [bcp_demo_1].[dbo].Customer (FirstName, LastName, Address) VALUES ('linvi', 'me', 'landinvi');

INSERT INTO [bcp_demo_2].[dbo].Customer (FirstName, LastName, Address) VALUES ('linvi2', 'me2', 'landinvi2');

Now we can take a look to the copy:

DECLARE @folder varchar(512); SET @folder = 'C:\temp\';

DECLARE @query varchar(512);SET @query = 'bcp "SELECT * FROM [bcp_demo_1].[dbo].Customer" queryout '

                        + @folder                        + 'result.csv -c -t, -T -S '                         + (select srvname from sys.sysservers); EXEC master..xp_cmdshell @query;

After executing this query, some information will be sent to you. They gave you some log information concerning BCP execution.

As you can see, BCP can take some parameters, for defining how the data are going to be parsed, or how it is going to render them in the external source.

-c : Allows you to specify a character format or without argument the default one
-t : Allows you to specify a special character for your separation in the csv file (here comma ",")
-T : Means that I connect to a trusted connection using integrated security.
-S : Allows me to specify the name of the database that I will connect on 

For a complete list of the parameters please visit : https://msdn.microsoft.com/en-us/library/ms162802.aspx 

To remove this output just use NO_OUTPUT as a parameter of xp_cmdshell :

EXEC master..xp_cmdshell @query, NO_OUTPUT;

Detect all existing Tables to copy

Now we will create 2 tables named : bcp_demo_1 and bcp_demo_2.

Now that we have the 2 databases, we can simply retrieving them thanks to a Regex, that will allow you to get all the tables you require to be concatenated.

DECLARE @databaseNameRegex varchar(100);SET @databaseNameRegex = '%bcp_demo_%';

DECLARE @tables TABLE (TableNb int, TableName varchar(64))INSERT  @tables                 SELECT ROW_NUMBER() OVER (ORDER BY Name ASC) AS TableNb, Name FROM sys.databases                  WHERE owner_sid <> 0x01 AND Name LIKE @databaseNameRegex;

SELECT * FROM  @tables;

This script list the databases which name is LIKE '%bcp_demo_%'; and create a temporary TABLE of this databases name to be able to query them later, one by one.

Push all data in one table

To simplify this script we are first going to push all the data into a temporary table

 -- List of tables to concatenate

DECLARE @databaseNameRegex varchar(100);SET @databaseNameRegex = '%bcp_demo_%';

DECLARE @tables TABLE (TableNb int, TableName varchar(64))INSERT  @tables                 SELECT ROW_NUMBER() OVER (ORDER BY Name ASC) AS TableNb, Name FROM sys.databases                  WHERE owner_sid <> 0x01 AND Name LIKE @databaseNameRegex;

SELECT * FROM  @tables; 
 -- Create temporary Table with all infosDECLARE @i int;SET @i = 1;DECLARE @tablesCount int;SET @tablesCount = (SELECT COUNT(*) FROM @tables);IF OBJECT_ID('[tempdb]..[##data]') IS NOT NULL  BEGIN      DROP TABLE [tempdb].[##data];  END CREATE TABLE [tempdb].[##data] (  [id]                [nchar](20)  NOT NULL, [FirstName]   [nchar](10)  NULL,   [LastName]  [nchar](10)  NULL,   [Address]   [nchar](10)  NULL);
 INSERT INTO [tempdb].[##data] (id, FirstName, LastName, Address)  VALUES ('Id', 'Firstname', 'LastName', 'Address');
 WHILE (@i <= @tablesCount)      BEGIN            DECLARE @table varchar(64);            SET @table = (SELECT TableName FROM @tables WHERE TableNb = @i);            INSERT [tempdb].[##data] EXEC('SELECT * FROM [' + @table + '].dbo.Customer');            SET @i = @i + 1;      ENDSELECT * FROM [tempdb].[##data];

Full script

Having all the data in one table will now allow us to move all this data thanks to BCP that we previously saw.

Here is the full script.

 -- List of tables to concatenate

DECLARE @databaseNameRegex varchar(100);SET @databaseNameRegex = '%bcp_demo_%';

DECLARE @tables TABLE (TableNb int, TableName varchar(64))INSERT  @tables                 SELECT ROW_NUMBER() OVER (ORDER BY Name ASC) AS TableNb, Name FROM sys.databases                  WHERE owner_sid <> 0x01 AND Name LIKE @databaseNameRegex;

 -- Create temporary Table with all infosDECLARE @i int;SET @i = 1;DECLARE @tablesCount int;SET @tablesCount = (SELECT COUNT(*) FROM @tables);IF OBJECT_ID('[tempdb]..[##data]') IS NOT NULL  BEGIN      DROP TABLE [tempdb].[##data];  END CREATE TABLE [tempdb].[##data] (  [id]                [nchar](20)  NOT NULL, [FirstName]   [nchar](10)  NULL,   [LastName]  [nchar](10)  NULL,   [Address]   [nchar](10)  NULL);
 INSERT INTO [tempdb].[##data] (id, FirstName, LastName, Address)  VALUES ('Id', 'Firstname', 'LastName', 'Address');
 WHILE (@i <= @tablesCount)      BEGIN            DECLARE @table varchar(64);            SET @table = (SELECT TableName FROM @tables WHERE TableNb = @i);            INSERT [tempdb].[##data] EXEC('SELECT * FROM [' + @table + '].dbo.Customer');            SET @i = @i + 1;      ENDSELECT * FROM [tempdb].[##data];

DECLARE @folder varchar(512); SET @folder = 'C:\temp\';

-- Execute BCP extraction

DECLARE @query varchar(512);SET @query = 'bcp "SELECT * FROM [tempdb].[##data]" queryout '

                        + @folder                        + 'result.csv -c -t, -T -S '                         + (select srvname from sys.sysservers); EXEC master..xp_cmdshell @query, NO_OUTPUT;

And here is the generated file in the C:\temp 

I hope this will be helpful to some of you.

Linvi

BCP - Bulk Copy.sql