sql - 使用子查询选择创建新表

Question

我正在尝试从子查询选择创建一个新表，但出现以下错误:')' 附近的语法不正确。

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
)

Answer 1

您忘记添加 alias在您的查询结束时。

您可以通过两种方法执行此操作:

1. 如果您已经创建了一个表，那么您可以使用 Insert Into 来执行此操作。像这样:

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

例如 see this fiddle

2. 如果您还没有创建表，那么您可以使用 SELECT * INTO 来完成。像这样:

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

例如 see this fiddle

更多引用见 SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE