{"id":813,"date":"2010-06-04T10:11:00","date_gmt":"2010-06-04T10:11:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/cesardelatorre\/2010\/06\/04\/importingexporting-data-to-sql-azure-databases-using-bcp-and-sql-scripts\/"},"modified":"2010-06-04T10:11:00","modified_gmt":"2010-06-04T10:11:00","slug":"importingexporting-data-to-sql-azure-databases-using-bcp-and-sql-scripts","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/importingexporting-data-to-sql-azure-databases-using-bcp-and-sql-scripts\/","title":{"rendered":"Importing\/Exporting data to SQL Azure databases using BCP and SQL Scripts"},"content":{"rendered":"

\"image\"<\/a> <\/h4>\n

The basic way: SQL Scripts + BCP<\/h4>\n

There are also other methods you can use to export\/import or even sync data like SQL Azure Migration Wizard, SQL Azure Data Sync,<\/em> and even SSIS (SQL Server Integration Services).<\/em><\/p>\n

Also, instead of creating database schema using SQL Scripts, another new option is using the new DACPAC (Data Tier Packages), in SQL Server 2008 R2, SQL Azure and Visual Studio 2010. <\/em><\/p>\n

But, in this post I’m going to show the most basic way, but also, a way that you can instrument from execution scripts , etc.<\/p>\n

First of all, we need a SQL Script to apply to our empty SQL Azure<\/em><\/strong> database. SQL Server 2008 R2 Management Studio<\/strong><\/em> has a new convenient feature: “Generate Scripts for SQL Azure<\/i>”.<\/p>\n

So, right-click on your SQL Server database and select Tasks –> Generate Scripts… option:<\/p>\n

\"clip_image002\"<\/a><\/p>\n

Then, you’ll be able to set the ‘Scripting Options’:<\/p>\n

\"clip_image004\"<\/a><\/p>\n

But, especially, we want to specify that the SQL Script must be generated for SQL Azure compatibility, therefore, you have to enter into the Advanced Settings<\/i>, pressing the ‘Advanced’ button:<\/p>\n

\"clip_image006\"<\/a><\/p>\n

So now, we have the SQL Script file containing our database schema, but specifically made for SQL Azure. We’ll have something like the following script: <\/p>\n\n\n\n
\n

\/****** Object: Table [dbo].[Product] Script Date: 06\/01\/2010 15:16:21 ******\/<\/p>\n

SET ANSI_NULLS ON<\/p>\n

GO<\/p>\n

SET QUOTED_IDENTIFIER ON<\/p>\n

GO<\/p>\n

CREATE TABLE [dbo].[Product](<\/p>\n

[ProductId] [int] IDENTITY(1,1) NOT NULL,<\/p>\n

[ProductDescription] [nvarchar](100) NULL,<\/p>\n

[UnitPrice] [money] NULL,<\/p>\n

[UnitAmount] [nvarchar](50) NULL,<\/p>\n

[Publisher] [nvarchar](200) NULL,<\/p>\n

[AmountInStock] [smallint] NULL,<\/p>\n

CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED<\/p>\n

… <\/p>\n

… <\/p>\n

… <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Now, we’re able to create our Database in SQL Azure and apply this SQL Script to it, so we’ll have our tables.<\/p>\n

Go to Windows Azure portal and then to your SQL Azure site and create an empty database. In my case, I created a new database called “NLayerApp”:<\/p>\n

\"clip_image008\"<\/a><\/p>\n

Now we can connect to SQL Azure from SQL Server 2008 R2 Management Studio, providing the DNS server name:<\/p>\n

\"clip_image009\"<\/a><\/p>\n

And therefore, we can see our SQL Azure database already created:<\/p>\n

\"clip_image011\"<\/a><\/p>\n

Next step would be to execute our SQL Script, in order to create our tables and database objects. We create a new query and paste our file content within it:<\/p>\n

\"clip_image012\"<\/a><\/p>\n

We paste the script and then we execute it:<\/p>\n

\"clip_image014\"<\/a><\/p>\n

So, at the end, we can see our tables within the SQL Azure database:<\/p>\n

\"clip_image015\"<\/a><\/p>\n

<\/b><\/p>\n

Using BCP.EXE to EXPORT\/IMPORT data<\/b><\/p>\n

The bcp utility<\/a><\/strong> is a command line utility that ships with Microsoft SQL Server. It bulk copies data between SQL Azure (or SQL Server) and a data file in a user-specified format. The bcp utility that ships with SQL Server 2008 R2 is fully supported by SQL Azure. <\/p>\n

You can use BCP to backup and restore your data on SQL Azure database.<\/b><\/p>\n

You can import large numbers of new rows into SQL Azure tables or export data out of tables into data files by using bcp. <\/p>\n

The bcp utility is not a migration tool. It does not extract or create any schema or format information from\/in a data file or your table. This means, if you use bcp<\/strong> to back up your data, make sure to create a schema or format file somewhere else to record the schema of the table you are backing up. bcp<\/strong> data files do not include any schema or format information, so if a table or view is dropped and you do not have a format file, you may be unable to import the data. The bcp utility has several command line arguments. For more information on the arguments, see SQL Server Books Online<\/a> documentation. For more information on how to use bcp with views, see Bulk Exporting Data from or Bulk Importing Data to a View<\/a>. <\/p>\n

Now, we can export data from our SQL Server database<\/strong>, using BCP.EXE, in this case using integrated security: <\/p>\n\n\n\n
\n

bcp NLayerApp.dbo.Customer out “C:\\MyFolderPath\\Customer.txt” -T -c -S WIN7VS2010RC1\\SQLEXPRESS<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\"clip_image017\"<\/a><\/p>\n

Then, we’ll have a generated file with all the table’s data.<\/p>\n

Now, we import that data into SQL-Azure database<\/strong>: <\/p>\n\n\n\n
\n

bcp TestDB.dbo.Customer in “C:\\Users\\cesardl\\Desktop\\Arquitectura Marco .NET DPE Spain\\_Windows Azure V\\BCP\\Customer.txt” -c -U mysqlazureuser@mysqlazureservername -S tcp:mysqlazureservername.database.windows.net -P mypassword<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\"clip_image019\"<\/a><\/p>\n

Now, we can check that data is already within the SQL Azure database:<\/p>\n

\"clip_image021\"<\/a><\/p>\n

Easy! \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"

The basic way: SQL Scripts + BCP There are also other methods you can use to export\/import or even sync data like SQL Azure Migration Wizard, SQL Azure Data Sync, and even SSIS (SQL Server Integration Services). Also, instead of creating database schema using SQL Scripts, another new option is using the new DACPAC (Data […]<\/p>\n","protected":false},"author":362,"featured_media":12806,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[90,91,114],"class_list":["post-813","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cesardelatorre","tag-sql-azure","tag-sql-server-2008","tag-window-azure"],"acf":[],"blog_post_summary":"

The basic way: SQL Scripts + BCP There are also other methods you can use to export\/import or even sync data like SQL Azure Migration Wizard, SQL Azure Data Sync, and even SSIS (SQL Server Integration Services). Also, instead of creating database schema using SQL Scripts, another new option is using the new DACPAC (Data […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/posts\/813","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/users\/362"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/comments?post=813"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/posts\/813\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/media\/12806"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/media?parent=813"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/categories?post=813"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/tags?post=813"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}