Create New Table With Subquery Select

I'm trying to create a new table from a subquery select but I get the following error: Incorrect syntax near ')'. SELECT * INTO foo FROM ( SELECT DATEPART(MONTH,a.InvoiceDate)

Solution 1:

You forgot to add alias at the end of your query.

You can do this by two methods:

1. If you have already created a table then you can do this using Insert Into like this:

INSERT into foo (CalMonth,CalYear,InvoiceDate,StockCode,QtyInvoiced,Volume)
SELECT * FROM
(
SELECT 
    DATEPART(MONTH,a.InvoiceDate) as CalMonth
    ,DATEPART(YEAR,a.InvoiceDate) as CalYear
    ,a.InvoiceDate
    ,a.StockCode
    ,a.QtyInvoiced
    ,a.Volume
FROM sales a
UNION ALL
SELECT 
    ds.CalMonth as CalMonth
    ,ds.CalYear as CalYear
    ,ds.InvoiceDate
    ,ds.StockCode
    ,ds.Cases as QtyInvoiced
    ,ds.Vol as Volume
FROM sales1 ds
) AS table1

For example see this fiddle

2. If you have not created a table then you can do this using SELECT * INTO like this:

SELECT * INTO foo from 
(
SELECT 
    DATEPART(MONTH,a.InvoiceDate) as CalMonth,
    DATEPART(YEAR,a.InvoiceDate) as CalYear,
    a.InvoiceDate,
    a.StockCode,
    a.QtyInvoiced,
    a.Volume
FROM sales a
UNION ALL
SELECT 
    ds.CalMonth as CalMonth,
    ds.CalYear as CalYear,
    ds.InvoiceDate,
    ds.StockCode,
    ds.Cases as QtyInvoiced,
    ds.Vol as Volume
FROM sales1 ds
) AS table1

For example see this fiddle

For more reference see SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

Solution 2:

Try this

select * into foo from 
(
select 
    DATEPART(MONTH,a.InvoiceDate) as CalMonth,
    DATEPART(YEAR,a.InvoiceDate) as CalYear,
    a.InvoiceDate,
    a.StockCode,
    a.QtyInvoiced,
    a.Volume
from sales a
Union All
select 
    ds.CalMonth as CalMonth,
    ds.CalYear as CalYear,
    ds.InvoiceDate,
    ds.StockCode,
    ds.Cases as QtyInvoiced,
    ds.Vol as Volume
from sales1 ds
) as TAB

Just provide an alias to your sub queried table

Solution 3:

Try this:

    INSERT into foo (CalMonth,CalYear, InvoiceDate,  StockCode, QtyInvoiced, Volume)
Select * From
    (select 
    DATEPART(MONTH,a.InvoiceDate) as CalMonth
    ,DATEPART(YEAR,a.InvoiceDate) as CalYear
    ,a.InvoiceDate
    ,a.StockCode
    ,a.QtyInvoiced
    ,a.Volume
    from sales a
    Union All
    select 
    ds.CalMonth as CalMonth
    ,ds.CalYear as CalYear
    ,ds.InvoiceDate
    ,ds.StockCode
    ,ds.Cases as QtyInvoiced
    ,ds.Vol as Volume
    from sales1 ds
    ) as t

Solution 4:

I think that you either need to add the alias that has been suggested or (more complex) to remove the parenthesis, the *, the FROM, the second SELECT and move the INTO foo:

SELECT--- removed: FROM --- removed: (--- removed: SELECT 
         DATEPART(MONTH,a.InvoiceDate) as CalMonth
        ,DATEPART(YEAR,a.InvoiceDate) as CalYear
        ,...
    INTO foo                                          --- movedFROM sales a
  UNIONALLSELECT ds.CalMonth as CalMonth
        ,...
  FROM sales1 ds ;
                                                      --- removed: )