In this SQL Server tutorial, we will learn How to create a table from view in SQL Server. Here we will illustrate this implementation using an example. Moreover, we will also cover the following topics.
- Create a table from view in SQL Server
- Create table script from view in SQL Server
- Create table structure from view in SQL Server
- Create a table from another view in SQL Server
- Create temp table from view in SQL Server
- Create pivot table from view in SQL Server
- SQL Server Management Studio create table from view
Create a table from view in SQL Server
In this section, we will learn to create a table using a view in SQL Server. Generally, first, we need to define the table structure using CREATE TABLE statement. After this, we have to use the INSERT statement to insert data into that table.
But, in SQL Server, there is a shortcut to copy data from one table to another. In the shortcut, we have to use the SELECT and INTO statements. Now, a view is just like a virtual table. So, we can also use view to create a table without even using CREATE TABLE statement.
Let’s, understand the syntax of this implementation, and then we will execute an example.
USE database
SELECT column1, column2, ...
INTO new_tabale FROM view_nameIn the above syntax, first, we have to use the SELECT statement to select the required columns. After this, we have to use the INTO table statement to specify the new table. And then we are also using the FROM clause and here we have to use the required view.
Now, let’s, use this syntax and execute a simple example.
USE [sqlserverguides]
GO
SELECT * INTO USA_Table FROM dbo.USA_CustomerViewIn the above example, we are creating a table with the name USA_Table. Moreover, we are using the USA_CustomerView to create and add data to the table. Now, if we query the table, we can observe that the table holds data returned from the view.
Create table structure from view in SQL Server
In this section, we will learn how to create table structure from a view in SQL Server. In SQL Server, to create any table, first, we need to define or create a table structure using CREATE TABLE statement.
However, in the previous section, we have seen how to create a table using a view where we were not required to use the CREATE TABLE statement. But, in the previous section, we copied the complete view data and structure to create a new table.
Now, what if we just want to copy the view striction into a table. In that case, we need to specify a false condition with the SELECT INTO statement. Let’s understand the whole implementation using an example.
USE [sqlserverguides]
GO
SELECT * INTO USA_Table FROM dbo.USA_CustomerView
WHERE 1=2In the above example, we are using the same SELECT INTO statement to create a table using a view. However, this time, we are also using a WHERE clause. And in the WHERE clause, we have specified a false condition (1=2). Now, due to this false condition, only the structure of the view is copied into the new table.
Therefore, this query will create an empty table with the same structure of the specified view.
Read How to see view definition in SQL Server
Create temp table from view in SQL Server
In this section, we will learn to create a temporary table using a view in SQL Server. A temporary table in SQL Server is a database table that exists only temporarily on the database server.
Moreover, these tables are either dropped automatically when the session that created them ends, or they can be deliberately dropped by users.
For more details related to different temporary tables and how to create them, refer to the SQL Server Create Temp Table tutorial. Now, let’s understand how to use a view to create a temporary table with an example.
USE [sqlserverguides]
GO
SELECT * INTO #USA_Table FROM dbo.USA_CustomerViewIn the above example, we are creating a temporary table with the name #USA_Table. Moreover, we are using the USA_CustomerView to create and add data to this table. Now, if we query this temp table, we can observe that the table holds data returned from the view.
Read Comparison Operators in SQL Server
Create pivot table from view in SQL Server
In this section, we will learn to create a pivot table using a view in SQL Server. Now, to create a pivot table in SQL Server, we have to use the PIVOT operator.
The PIVOT operator in SQL Server spins a table-valued expression. It divides the output into numerous columns based on the unique values in one column and then conducts aggregations on the rest column values.
First, let’s, understand how to create a pivot table in SQL Server using a PIVOT operator on an existing table. After this, we will see how to create a pivot table using view.
For the illustration of the pivot table consider the following syntax of using the PIVOT operator in SQL Server.
SELECT <non-pivoted col>,
<list of pivoted_cols>
FROM
(<SELECT query to produce some data>)
AS <alias_name>
PIVOT
(
<aggregation function>(<col_name>)
FOR
[<col_name that become column headers>]
IN ( [list of pivoted_cols])
) AS <alias name for pivot table> By using this PIVOT operator, the table will be converted from row-level to column level. Let’s understand the syntax using an example, and we will use the following Customers table.
Now, for the example demonstration, we will use the values of the country column from the table. And fetch the count of customers from each country. Therefore, the query for this implementation is as follows.
USE [sqlserverguides]
GO
SELECT * FROM
( SELECT customer_name, country FROM Customers
) AS t
PIVOT(
COUNT (customer_name)
FOR country IN ( [United States], [Canada], [New Zealand] )
) AS Pivot_tableIn the above example, we have taken names of different countries as columns. Moreover, we are using the COUNT function to get the count of customers from each country. Here is the output of the above query.
Example
Now, in the above example, we have used a PIVOT operator on a table and generated a pivoted resultset. Similarly, we can also use a PIVOT operator on a view. And then, instead of using the SELECT statement, we can use the SELECT INTO statement to create a new pivoted table.
Let’s understand the whole implementation using an example. For this task, first, we will create a view in SQL Server.
USE [sqlserverguides]
GO
CREATE VIEW [dbo].[CustomerView]
( [Customer Name], [Customer City], [Customer Country] )
AS
SELECT customer_name, city, country
FROM Customers
GOIn the above example, we created a simple view that fetches customer names, city, and country columns from the Customers table. Next, we will use this view and the SELECT INTO statement to create a pivot table.
USE [sqlserverguides]
GO
SELECT * INTO CountCustomers FROM
( SELECT [Customer Name], [Customer Country] FROM CustomerView
) AS t
PIVOT(
COUNT ([Customer Name])
FOR [Customer Country] IN ( [United States], [Canada], [New Zealand] )
) AS Pivot_tableSo, as stated earlier, we are using the SELECT INTO statement to create a new pivoted table using the result of a view. Therefore, when we query this new table, we will get the following result.
Read How to delete a view in SQL Server
Create table script from view in SQL Server
In SQL Server, a table script generally includes the CREATE TABLE statement and various columns for that table. However, there is no direct way to generate a table script using a view in SQL Server.
Still, we can use a view to create a table structure or refer to it as an empty table. Now to do so, we can easily follow the following given syntax.
USE database
GO
SELECT * INTO new_table FROM view_name
WHERE 1=2In the above, we are using the same SELECT INTO statement to create an empty table from a view. Moreover, we have also used a WHERE clause but we specified a false condition in it. As a result, the query will only create a table without any data.
To understand the syntax better, you can also refer to the “Create table structure from view in SQL Server” topic in this tutorial.
Read SQL Server bulk insert from CSV file
SQL Server Management Studio create table from view
in SQL Server Management Studio, there is no method to create a table from a view in a database. The only possible way is to use the SELECT INTO statement in a query editor in the management studio.
However, we can use SQL Server Management Studio to create a table in SQL Server and create a view in SQL Server separately.
So, in this tutorial, we have learned How to create a table from view in SQL Server. Here we have illustrated this implementation using an example. Moreover, we have also covered the following topics.
- Create a table from view in SQL Server
- Create table script from view in SQL Server
- Create table structure from view in SQL Server
- Create a table from another view in SQL Server
- Create temp table from view in SQL Server
- Create pivot table from view in SQL Server
- SQL Server Management Studio create table from view
Related SQL Server tutorials:
- SQL Server function return table
- Arithmetic operators in SQL Server
- SQL Operand data type real is invalid for modulo operator
- Could not find stored procedure in SQL Server
- Difference between table and view in SQL Server
- Remote procedure call failed in SQL Server Configuration Manager
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.