Can you create an SQL database table with columns via an Azure ARM Template?

Copper Contributor

I was wondering whether it was possible to create an Azure SQL database table, with the columns and the columns data types included? I know you can create the table, as I have done so successfully already, but when trying to include the columns and when deploying the template, I keep getting errors.

Any help is appreciated.

Thanks,

5 Replies

Hello @AlexGazz,

Yes, it is possible to create an Azure SQL database table with columns using an Azure Resource Manager (ARM) template. 


Here's just an example of how you can define an Azure SQL database table with columns in an ARM template:


{
"$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"databaseName": {
"type": "string",
"metadata": {
"description": "The name of the Azure SQL database."
}
}
},
"resources": [
{
"type": "Microsoft.Sql/servers/databases/tables",
"apiVersion": "2019-06-01-preview",
"name": "[concat(parameters('databaseName'), '/MyTable')]",
"properties": {
"columns": [
{
"name": "Id",
"type": "int",
"isNullable": false,
"isIdentity": true,
"isPrimaryKey": true
},
{
"name": "Name",
"type": "nvarchar(50)",
"isNullable": false
},
{
"name": "Email",
"type": "nvarchar(100)",
"isNullable": true
}
]
}
}
]
}

In the above template, you can see the "columns" property within the "properties" section. Here, you can define an array of objects, where each object represents a column in the table. You can specify the column name, data type, and other properties such as whether the column is nullable, an identity column, or a primary key.


You can also use this link:
Azure Resource Manager: Create an Azure SQL Managed Instance - Azure SQL Managed Instance | Microsof...

If my answer helped you, you can click on Mark as best response.

Kindest regards

Leon Pavesic

@LeonPavesic Thank you for coming back to me, do you know if you could include the table creation in the same template as creating an SQL server and database, so it creates all 3 resources via one template?

Hi @AlexGazz,

thanks for the update and your question.

It is possible to create a SQL server, database, and table in the same Azure Resource Manager (ARM) template. Here is an example how you can try to do it:

{
"$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"serverName": {
"type": "string",
"metadata": {
"description": "The name of the Azure SQL server."
}
},
"databaseName": {
"type": "string",
"metadata": {
"description": "The name of the Azure SQL database."
}
}
},
"resources": [
{
"type": "Microsoft.Sql/servers",
"apiVersion": "2019-06-01-preview",
"name": "[parameters('serverName')]",
"location": "[resourceGroup().location]",
"properties": {
"administratorLogin": "your-admin-username",
"administratorLoginPassword": "your-admin-password"
},
"resources": [
{
"type": "databases",
"apiVersion": "2019-06-01-preview",
"name": "[concat(parameters('serverName'), '/', parameters('databaseName'))]",
"location": "[resourceGroup().location]",
"dependsOn": [
"[parameters('serverName')]"
],
"properties": {
"collation": "SQL_Latin1_General_CP1_CI_AS",
"edition": "Basic",
"maxSizeBytes": "1073741824"
},
"resources": [
{
"type": "tables",
"apiVersion": "2019-06-01-preview",
"name": "[concat(parameters('serverName'), '/', parameters('databaseName'), '/MyTable')]",
"location": "[resourceGroup().location]",
"dependsOn": [
"[parameters('databaseName')]"
],
"properties": {
"columns": [
{
"name": "Id",
"type": "int",
"isNullable": false,
"isIdentity": true,
"isPrimaryKey": true
},
{
"name": "Name",
"type": "nvarchar(50)",
"isNullable": false
},
{
"name": "Email",
"type": "nvarchar(100)",
"isNullable": true
}
]
}
}
]
}
]
}
]
}


You need to provide the appropriate values for the "administratorLogin" and "administratorLoginPassword" properties to set the admin credentials for the SQL server.

If you think my answer helped you, you can click on Mark as best response

Kindest regards

Leon Pavesic

Yes, you can. ARM templates can have multiple resources.
ARM template syntax is a little bit complex, for that reason I use Bicep for coding and then export the ARM template or deploy it directly from Azure CLI

@LeonPavesic thank you for your message, unfortunately, the template you sent didn't work either. The deployment always fails when trying to create the table. I think I will try it another way: I'll make all the other resources via the template and then import the table using the import database functionality. Or I'm looking into using the BICEP files as these seem more user-friendly.