Using An Existing Azure Sql Table With Windows Azure Mobile Services
- 24 May 2013
You can use an existing Azure SQL table with your Mobile Services by moving the table to the schema used by your Mobile Service. This blog post will show you one technique to accomplish this.
In this sample the table we want to use with the Mobile Service is in the dbo schema. The table is aptly named ‘TableToImport’! I want to move this to the schema for my Mobile Service and use it seamlessly from Mobile Services.
There are a couple of requirements for using an Azure SQL table seamlessly with Mobile Services (at the time of this writing):
- The table must be in the schema of your Mobile Service (the schema is the name of your Mobile Service)
- The table must have an identity column called ‘id’ and it must be all lower case letters
- The column types must be correct (for example not nvarchar(50) but nvarchar(max) )
You can access your Azure SQL table in different ways. I will use the designer from the portal here:
This is the Table that I want to move over to my Mobile Service schema (note the schema is ‘dbo’ as seen in [dbo].[TableToImport]:
Note that the first column ‘ID’ is the wrong case, I can change it easily here:
The next task is to find out what schema I wish to move this to. The schema for your Mobile Service is the name of your mobile service. In my case it is ‘jsandersattachtodb’ as shown below:
To move the table from the ‘dbo’ schema to the ‘jsandersattachtodb’ schema you can click the ‘New Query’ icon and execute an SQL statement similar to this:
ALTER SCHEMA JsandersAttachToDb TRANSFER dbo.TableToImport
Then I can simply type the name of the table that I want to add and it will pick up the definition of the table from the existing table!
But something is wrong… I see that I have two records but if I click on the table to browse the data in the Mobile Service portal it in it does not display in the Portal UI!
The issue is that when I created the table, I did not set the ‘id’ column to be an identity column. There is no easy way to do this (that I could find
) so the solution is to create a table with an identical structure and ensure the ‘id’ column is set to identity. Then copy the data over!
Using the Query Window in the SQL management portal I can execute a statement to copy over the data into my new table:
SET IDENTITY_INSERT JsandersAttachToDb.NextTableToImport ON
GO
INSERT INTO JsandersAttachToDb.NextTableToImport (id, Column1, Column2) SELECT id, Column1, Column2 FROM JsandersAttachToDb.TableToImport
Now my table is integrated into my Mobile Service and works properly!
This is just one technique to using existing Azure SQL tables in your Mobile Service. As I come across different requirements from customers I will be sure to add new blogs to show how to accomplish these (or point you to existing walkthroughs). You can get more SQL Azure help for copying etc… from the Windows Azure SQL Database forum as well.
Drop me a note and let me know if you liked this or it saved you some time!
Additional Information:
Dinesh’s blog describes common datatypes in Mobile Services
<< Go Back