How to create table in azure sql database

In this Azure SQL article, you will learn how to create a table in the Azure SQL database. We will discuss multiple ways that you can use to create a table in an Azure SQL database. Also, we have created some examples so that you can understand the concept better.

• Create table syntax in Azure SQL database
• Create table in Azure SQL database query
• How to create a new table in Azure SQL database
• Create table in Azure SQL database portal
• Create table in Azure SQL database using CLI
• How to create temporary table in Azure SQL database
• Create table from another table in Azure SQL database
• How to create external table in Azure SQL database

Create table syntax in Azure SQL database

The syntax to create a table in Azure SQL is the same one we use to create a table in SQL Server. We use the CREATE TABLE statement to create a table in Azure SQL. The following is the simple syntax for creating a table in Azure SQL:

CREATE TABLE <table name>
(
Column1 <Data type>,
Column2 <Data type>,
.
.
.
ColumnN <Data type>
)

Thus, you can follow this syntax to create tables in the Azure SQL database.

Read Create stored procedures in Azure SQL database

Create table in Azure SQL database query

We use the CREATE TABLE statement to create a table in the Azure SQL database. The following is an example of a query to create a table in the Azure SQL database:

• I am creating a table named Student with three columns Student ID, Student Name and Stream. I will write the query as:

CREATE TABLE dbo.Student
(
[Student ID] int PRIMARY KEY,
[Student Name] varchar(30) NOT NULL,
Stream varchar(20)
)

• The Student ID column is of integer data type and I have set it as the table’s Primary Key. The Student Name column is of varchar(30) type and I have created a NOT NULL constraint for this column. The Stream column is of varchar(20) type.

Read Backup Azure database to local SQL Server

How to create a new table in Azure SQL database

In this section, you will see how to create a table in the Azure SQL database with an example.

There are multiple ways in which you can create a new table in the Azure SQL database. We will use SQL Server management studio to connect to the Azure SQL database and create a table. You can use any other IDE also and execute the query to create a table in the Azure SQL database.

The first step is to connect to the Azure SQL database using SQL server management studio. If you do not know how to do that, you can read our article on how to connect to the Azure SQL database.

Once you have connected to the Azure SQL database, you can create a table using one of the two ways:

Using GUI:

• Expand the server in the Object Explorer Window.
• Under your database, right click on Tables, Click New and then click Table….

• Enter the column names and data types of the column. You can also set the column properties from the Column Properties window.

• Go to File, then click on Save Table to save the table. You can also use the shortcut Ctrl+S. You will be asked to choose a name for your table. Give the table a name and click OK.
• Now right click on the Tables option under your database and click Refresh to refersh the tables.

Now if you expand the tables, you can see your newly created table. You can also verify in the Azure portal that the table is created or not. Open the database in the Azure portal, login into the database, open Query Editor, and expand Tables in Object Explorer.

Read Pause and resume Azure SQL database

Using T-SQL query:

If you want to create the table using a T-SQL query, you can use the CREATE TABLE statement. Follow the below steps for using this method:

• In SQL server management studio, click on New Query or simply press Ctrl+N to open a new query editor window.

• We have deleted the table Persons that we created above. We will create the same table again using the T-SQL query:

DROP TABLE IF EXISTS [dbo].[Persons]
CREATE TABLE [dbo].[Persons](
	[First Name] [nvarchar](20) NOT NULL,
	[Last Name] [nvarchar](20) NOT NULL,
	[E-mail] [nvarchar](30) NULL,
	[Phone] [nvarchar](20) NOT NULL,
	[City] [nvarchar](20) NULL
) ON [PRIMARY]

• If you refresh the tables in the object explorer window, you can see the table is created. You can also verify the table in the Azure portal as explained in the above section.

Create table in Azure SQL database portal

In this section, we will discuss how to create a table in the Azure SQL database using the Azure portal.

The Azure portal provides a Query Editor that you can use to query the database. We will create a table by executing the CREATE TABLE statement in the Query Editor. Follow the below steps to create a table in the Azure SQL database:

• Open the Azure SQL database page in your Azure portal and click on Query editor (preview) to open the query editor window.
• Write the below query to create a table in the Azure SQL database;

CREATE TABLE [dbo].[Persons](
	[First Name] [nvarchar](20) NOT NULL,
	[Last Name] [nvarchar](20) NOT NULL,
	[E-mail] [nvarchar](30) NULL,
	[Phone] [nvarchar](20) NOT NULL,
	[City] [nvarchar](20) NULL
) ON [PRIMARY]

• Refresh the query editor window by clicking on the refresh icon as shown below in the image. Expand Tables and you can see the table is created successfully.

Thus, you might have learned to create a table in the Azure SQL database in the Azure portal.

Also read, Create tables in MongoDB (Complete Guide)

Create table in Azure SQL database using CLI

In this section, you will learn how to create a table in the Azure SQ database using Azure CLI. We will use Azure PowerShell to create the table.

If you want to use your local Windows PowerShell, you have to install a utility named sqlcmd. The sqlcmd utility is already installed in the Azure PowerShell.

• The first step is to go to the Azure portal and open the Azure Shell. To access the Azure Shell, go to the link https://shell.azure.com/ or click on the icon as shown below in the image:

• Create a connection with the database using the sqlcmd utility. Execute the below command in PowerShell to connect to the database.

sqlcmd -S mysql1000.database.windows.net -U azureadmin -P Kushal@123 -d DemoDatabase

• Change the instance name, username, password and database name according to your setup.
• Then write the CREATE TABLE statement to create the table as:

CREATE TABLE [dbo].[Persons]( [First Name] [nvarchar](20) NOT NULL, [Last Name] [nvarchar](20) NOT NULL, [E-mail] [nvarchar](30) NULL, [Phone] [nvarchar](20) NOT NULL, [City] [nvarchar](20) NULL) ON [PRIMARY]

• In the next line write GO to execute the CREATE TABLE statement.

• You can verify in the Azure portal if the table is created or not.

Thus, you might have learned how you can create a table in the Azure SQL database using Azure CLI.

Read SQL Server check user permissions on table

How to create temporary table in Azure SQL database

In this section, you will learn how to create temporary tables in any Azure SQL database. Before that, you should have some basic knowledge about temporary tables in the Azure SQL database.

There are two types of temporary tables in the Azure SQL database:

1. Local temporary tables: The temporary tables that are only available for a single user session in which they are created. Once the session is over, these tables are automatially dropped.
2. Global temporary tables: The temporary tables that are available for multiple user sessions, but not for any other database. These tables are automatically dropped when the calling user sessions ends and the execution of all the procedures or tasks referencing to the tables is over.

Now let us see how to create temporary tables in Azure SQL. We use the CREATE TABLE statement for creating temporary tables. We use a prefix before the name of the table to define that the table is a temporary table.

Also read, SQL Server Create Temp Table

Create local temporary table in Azure SQL database

While creating a local temporary table, we use the prefix (#) before the name of the table in the CREATE TABLE statement.

For example, if we want the name of a local temporary table to be TempTable, we will write the name as #TempTable. Let us have a look at the syntax for creating a local temporary table:

CREATE TABLE #table_name(
column1 data_type,
column2 data-type
.
.
columnN data-type
)

Let us see an example in the Azure SQL database.

• Go to the Azure portal in your browser and open the Query Editor for your database.
• Login into the database and write the below query in the query editor and run it for creating a local temporary table:

CREATE TABLE dbo.#LocalTempTable(
[Employee ID] int IDENTITY(1,1),
[First Name] nvarchar(20),
[Last Name] nvarchar(20),
Department nvarchar(20)
)
INSERT INTO dbo.#LocalTempTable( [First Name], [Last Name], Department)
VALUES('Chris', 'Morris', 'Sales')
SELECT * FROM dbo.#LocalTempTable

• Now if you try to retrive data from the SELECT statement i.e. the SELECT statement is the only statement in the batch, you will see an error;

SELECT * FROM dbo.#LocalTempTable

This is because the scope of the local temporary table is over and the table is dropped automatically.

Thus, in this way you can create a local temporary table in Azure SQL.

Read How to execute stored procedure in SQL Server

Create global temporary table in Azure SQL database

While creating a global temporary table, we use the prefix (##) before the table name in the CREATE TABLE statement.

For example, if we want to create a global temporary table with the name TempTable, we will write the name as ##TempTable. Let us have a look at the syntax now:

CREATE TABLE ##table_name(
column1 data_type,
column2 data-type
.
.
columnN data-type
)

Now we will create a global temporary table in the Azure SQL database.

• Open the Azure portal in your browser, open the Query Editor for your database and login into the database.
• Execute the below query in the query editor to create a global temporary table:

CREATE TABLE dbo.##LocalTempTable(
[Employee ID] int IDENTITY(1,1),
[First Name] nvarchar(20),
[Last Name] nvarchar(20),
Department nvarchar(20)
)
INSERT INTO dbo.##LocalTempTable( [First Name], [Last Name], Department)
VALUES('Chris', 'Morris', 'Sales')
SELECT * FROM dbo.##LocalTempTable

In this way, you create a global temporary table.

Thus, you might have learned about the temporary tables in Azure SQL and how to create them.

Read How to get list of users in SQL Server

Create table from another table in Azure SQL database

In this section, you will learn how to create a table from another table in the Azure SQL database.

You can use the SELECT INTO statement to create a table from another table. The syntax of the SELECT INTO statement is:

SELECT column1, column2,...,columnN into NewTable FROM OldTable Where <condition>

The SELECT statement fetches the resultset from the OldTable according to the specified condition and creates a new table with the same structure and data as the resultset returned. Let us see with an example how to use this statement.

We are using the table SalesLT.Customer from the sample database in Azure SQL. We will create a new table [New Customer] from the SalesLT.Customer table

• In the Azure portal, open the query editor window for your database.
• Write the below query to create a new table and copy top 10 records from the SalesLT.Cutomer table to the New Customer.

SELECT TOP(10) * INTO dbo.[New Customer] FROM SalesLT.Customer
SELECT * FROM [dbo].[New Customer]

• You can see that the new table is created from the old table. Also, the specified data is copied.

Thus, you might have learned how to create a table from another table in the Azure SQL database.

Read Loop in SQL Server stored procedure

How to create external table in Azure SQL database

In this section, you will learn how to create an external table in an Azure SQL database. We are assuming that you have already created an external data source.

We use the CREATE EXTERNAL TABLE statement to create external tables in Azure SQL databases. The general syntax is:

CREATE EXTERNAL TABLE <external table name>
( column1 DataType,
  column2 DataType,

  .
  .
  columnN DataType)
WITH
<Data Source Options>

• For example, if we want to create an external table named TableExternal with the data source named SourceExternal, we will write the query as:

CREATE EXTERNAL TABLE [dbo].TableExternal
( [Employee ID] [int] NOT NULL,
  [Employee Name] [varchar](50) NOT NULL,
  [Department] [varchar](50) NOT NULL)
WITH
( DATA_SOURCE = SourceExternal)

• In this way, you can create external tables in the Azure SQL database.

You may like the following SQL server tutorials:

• SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’
• The configuration option ‘xp_cmdshell’ does not exist or it may be an advanced option
• Msg 567: Unable to retrieve data for this section of the report
• Cannot open server requested by the login. client is not allowed to access the server

Thus, we have learned how to create tables in the Azure SQL database.

• Create table syntax in Azure SQL database
• Create table in Azure SQL database query
• How to create a new table in Azure SQL database
• Create table in Azure SQL database portal
• Create table in Azure SQL database using CLI
• How to create temporary table in Azure SQL database
• Create table from another table in Azure SQL database
• How to create external table in Azure SQL database