Forum Discussion
Can you create an SQL database table with columns via an Azure ARM Template?
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 | Microsoft Learn
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?
- 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 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 PavesicLeonPavesic 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.
