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.
- 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.
- In the Model.Xml we have the structure of every table, stored procedure, etc...
- 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.
- 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!