How to create a table with a SQL query

Question

I have a table in SQL Server and I have written some custom query and got the result. Now I want to save the result into a new table. How can I do that? Below is my SQL query. I want to store the result data into a new table.

SELECT  
    Name, InvoiceNumber, InvoiceDate, Total,
    IIF(Name = '', '0', '100') AS Vendor_Recognition,
    IIF(InvoiceNumber = '', '0', '100') AS InvoiceNumber_Recognition,
    IIF(InvoiceDate = '', '0', '100') AS InvoiceDate_Recognition,
    IIF(Total = '', '0', '100') AS Total_Recognition
FROM 
    Invoice

@KoushikRoy: this is not supported in T-SQL / SQL Server. See the answer by Tyron down below as to how to do it in SQL Server — marc_s
– marc_s, Commented Jun 8, 2021 at 6:39
Does this answer your question? Create a table with SQL from Query result in SSMS — Dale K
– Dale K, Commented Jun 8, 2021 at 6:59

Tyron78 · Accepted Answer · 2021-06-08 06:32:29Z

3

What about a simple SELECT...INTO?

SELECT  Name, InvoiceNumber, InvoiceDate, Total,
        IIF(Name = '', '0', '100') AS Vendor_Recognition,
        IIF(InvoiceNumber = '', '0', '100') AS InvoiceNumber_Recognition,
        IIF(InvoiceDate = '', '0', '100') AS InvoiceDate_Recognition,
        IIF(Total = '', '0', '100') AS Total_Recognition
    INTO MyNewTable
    FROM Invoice

answered Jun 8, 2021 at 6:32

Tyron78

4,1973 gold badges21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ketan Kotak · Accepted Answer · 2021-06-08 06:45:36Z

You can use SELECT... INTO.. syntax.

SELECT  
    Name, InvoiceNumber, InvoiceDate, Total,
    IIF(Name = '', '0', '100') AS Vendor_Recognition,
    IIF(InvoiceNumber = '', '0', '100') AS InvoiceNumber_Recognition,
    IIF(InvoiceDate = '', '0', '100') AS InvoiceDate_Recognition,
    IIF(Total = '', '0', '100') AS Total_Recognition
INTO MyNewTable
FROM 
    Invoice

also make sure you provide enough length in case if you want to insert more queries in same table

SELECT  
        Name, InvoiceNumber, InvoiceDate, Total,
        IIF(Name = '', '0', '100') AS Vendor_Recognition,
        IIF(InvoiceNumber = '', '0', '100') AS InvoiceNumber_Recognition,
        IIF(InvoiceDate = '', '0', '100') AS InvoiceDate_Recognition,
        cast(IIF(Total = '', '0', '100')) as Decimal(10,2) AS Total_Recognition  -- as this way you can define data type for MyNewTable
    INTO MyNewTable
    FROM 
        Invoice

Thom A · Accepted Answer · 2021-06-14 15:33:16Z

0

SELECT  
    Name, InvoiceNumber, InvoiceDate, Total,
    IIF(Name = '', '0', '100') AS Vendor_Recognition,
    IIF(InvoiceNumber = '', '0', '100') AS InvoiceNumber_Recognition,
    IIF(InvoiceDate = '', '0', '100') AS InvoiceDate_Recognition,
    IIF(Total = '', '0', '100') AS Total_Recognition
INTO MyInvoice
FROM 
    Invoice

I have written query to store your result in table named 'MyInvoice'. Please note below query is not re-runnable and if you want to make it re-runnable you have to drop the table first and then execute the query given above

edited Jun 14, 2021 at 15:33

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

answered Jun 8, 2021 at 7:11

Empower Coding

764 bronze badges

Comments

Johannes Atzmüller · Accepted Answer · 2021-06-08 06:31:05Z

-1

The simplest way to insert data from an existing table is Insert Into:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

You can find elaborting information here: SQL INSERT INTO SELECT Statement

PS: Wow just saw CREATE TABLE new_table AS (SELECT * FROM old_table); in a comment. That is syntax i did not know yet. Thank you Create Table (with as explaination)

CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

answered Jun 8, 2021 at 6:31

Johannes Atzmüller

1

1 Comment

marc_s Over a year ago

CREATE TABLE ... AS SELECT ..... is not supported in T-SQL/SQL Server. You need to use SELECT (list of columns) INTO NewTableName FROM ..... instead

Rajan Patekar · Accepted Answer · 2021-06-08 06:45:49Z

-1

I think creating the table first and then inserting the result will be a good idea as it will give us an option to define indexes at the time of table creation which will be helpful in performance and catching. Thoughts?

answered Jun 8, 2021 at 6:45

Rajan Patekar

1051 silver badge8 bronze badges

Collectives™ on Stack Overflow

How to create a table with a SQL query

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related