How To Save Query Results As Table In SQL Server

Recently, my team was required to save the query results into a table on an SQL server. We identified a few simple approaches to do this. In this article, I will walk you through all those approaches individually.

Approach-1: Using SELECT INTO Statement

Syntax

SELECT * INTO NewTable
FROM OldTable
WHERE condition;

Example

Let us consider a real-time example in which we copy the query results of the product table to a new table named HomeUSA. To complete this task, let us execute the query below.

SELECT * INTO HomeUSA
FROM Product

After executing the above query, I got the expected output, as shown in the screenshot below.

Let us check if the data is copied using the SQL query below. You can see below that we got the expected output.

Check out How to Get Table Row Count in SQL Server

Approach-2: Using CREATE TABLE and INSERT INTO statements

We can create a brand-new table using the CREATE TABLE statement and then insert the data using the INSERT INTO statement.

Syntax

CREATE TABLE NewTable (
    Column1 DataType,
    Column2 DataType,
    Column3 DataType,
    ...
);

INSERT INTO NewTable (Column1, Column2, Column3 ...)
SELECT Column1, Column2, Column3 ...
FROM OldTable
WHERE condition;

Example

Let us create a new table named USAHome, and then we will insert the data from the Product table.

CREATE TABLE USAHome (
    ProductName nvarchar(50),
    ProductType nvarchar(50),
	ProductID int,
    ProductBrand nvarchar(50),
	ValidFrom datetime2(7) not null,
	ValidTo datetime2(7) not null
);

The above query was executed successfully, as shown in the screenshot below.

Now, we can execute the query below to insert the data from the product table into USAHome.

INSERT INTO USAHome (ProductName, ProductType, ProductID,ProductBrand, ValidFrom,ValidTo)
SELECT ProductName, ProductType, ProductID,ProductBrand, ValidFrom,ValidTo
FROM Product

After executing the above query, I got the expected output below.

Check out How To Change Column Position In SQL Server

Approach-3: Using the Table Variables

Syntax

DECLARE @MyResultTable TABLE (
    Column1 DataType,
    Column2 DataType,
    Column3 DataType,
    ...
);

INSERT INTO @MyResultTable (Column1, Column2, Column3 ...)
SELECT Column1, Column2, Column3 ...
FROM OldTable
WHERE condition;

Example

DECLARE @MyProductResultTable TABLE (
    ProductName nvarchar(50),
    ProductType nvarchar(50),
	ProductID int,
    ProductBrand nvarchar(50),
	ValidFrom datetime2(7) not null,
	ValidTo datetime2(7) not null
);

INSERT INTO @MyProductResultTable (ProductName, ProductType, ProductID,ProductBrand, ValidFrom,ValidTo)
SELECT ProductName, ProductType, ProductID,ProductBrand, ValidFrom,ValidTo
FROM Product

After executing the above query, we got the expected output below.

Check out the expected output below.

Check out How To Find A Table In SQL Server

Approach-4: Using the Temporary Tables option

We can also use the temporary table option for this task.

Syntax

CREATE TABLE #TempTable (
    Column1 DataType,
    Column2 DataType,
    Column3 DataType,
    ...
);

INSERT INTO #TempTable (Column1, Column2, Column3 ...)
SELECT Column1, Column2, Column3 ...
FROM OldTable
WHERE condition;

Example

Let us use the below query to create the temp table #USAHome and then copy the data from the Product table to this temp table.

CREATE TABLE #USAHome (
    ProductName nvarchar(50),
    ProductType nvarchar(50),
	ProductID int,
    ProductBrand nvarchar(50),
	ValidFrom datetime2(7) not null,
	ValidTo datetime2(7) not null
);

After executing the above query, we got the expected output as shown below.

Now, let’s execute the below query to insert the data from the Product table into the #USAHome temp table.

INSERT INTO #USAHome (ProductName, ProductType, ProductID,ProductBrand, ValidFrom,ValidTo)
SELECT ProductName, ProductType, ProductID,ProductBrand, ValidFrom,ValidTo
FROM Product

After executing the above query, I got the expected output below.

Conclusion

There are several approaches to saving the query results as a table in SQL Server. You can use any of the methods according to your requirements.

You may also like following the articles below.

• How to Find Table Dependencies in SQL Server
• How to Alter Table Column to Allow NULL in SQL Server
• How To View Table In SQL Server Management Studio