Lesson Learned #77: Importing data from bacpac using bcp command utility

This post has been republished via RSS; it originally appeared at: MSDN Blogs.

Hello Team,

Today, I worked on a case that our customer needs to load some data from a bacpac to an Azure SQL Database, for different and urgent situations was not possible to export and create again this bacpac.

Our customer needs only import some tables and I suggested to perform the following task to import them.

  1. At the end, a bacpac is a compressed file and changing the extension from bacpac to zip and extracting it, we are able to get access the content.
  2. In the Model.Xml we have the structure of every table, stored procedure, etc...
  3. For every table that was exported we're going to have a subfolder within the data folder that contains multiple *.BCP with the data exported.
  4. So, I followed this example we were able to import the data of these tables using bcp

  • I created the table following the Origin.XML
CREATE TABLE [dbo].[TableToExport1](
	[OrderHeaderID] [int] NOT NULL,
	[City] [nvarchar](54) NOT NULL,
	[ProductId] [int] NOT NULL,
	[Total] [money] NOT NULL,
	[Ord_Description] [nvarchar](400) NOT NULL,
 CONSTRAINT [PK_TableToExport1] PRIMARY KEY CLUSTERED 
(
	[OrderHeaderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
  • I created the format file for bcp utility and I named it Example.fmt, even, if I need, for example, don't import a specific column.

11.0
5
1 SQLINT 0 4 "" 1 OrderHeaderID ""
2 SQLNCHAR 2 54 "" 2 City SQL_Latin1_General_CP1_CI_AS
3 SQLINT 1 4 "" 3 ProductID ""
4 SQLMONEY 1 8 "" 4 Total ""
5 SQLNCHAR 2 400 "" 5 Ord_description SQL_Latin1_General_CP1_CI_AS

 

  • Finally, I test the bcp, using a Windows Command Prompt, executing the following command: bcp <tablename> in <filename.BCP> -S <servername> -U <username> -d <databasename> -b 100 -q -f Example.fmt -N -P password

 

  • As we have multiple BCP files I created a batch script to import all of them, running this command: for %f in (*.bcp) do bcp <tablename> in %f -S <servername> -U <username> -d <databasename> -b 100 -q -f Example.fmt -N -P <password>

 

Enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.