使用子查询select创建新表
使用子查询select创建新表
提问于 2012-08-09 19:39:58
我试图从子查询select创建一个新表，但得到以下错误：
')‘附近的语法不正确。
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
)回答 4
Stack Overflow用户
回答已采纳
发布于 2012-08-09 19:52:07
您忘记在查询的末尾添加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 table12.如果您还没有创建表，则可以使用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有关更多参考信息，请参阅SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE
Stack Overflow用户
发布于 2012-08-09 19:43:40
尝尝这个
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只需提供到子查询表的alias
Stack Overflow用户
发布于 2012-08-09 19:44:03
试试这个：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11882610
复制相关文章
相似问题
腾讯云开发者
