Geek Answers Handbook

Create a new table with a subquery

I try to create a new table from the select subquery, but I get the following error:
Invalid syntax next to ')'.

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
source share
4 answers

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

You can do this in two ways:

1. If you have already created a table, you can do this with Insert Into as follows:

 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 script.

2. If you have not created a table, you can do this with SELECT * INTO as follows:

 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 script.

For details, see SQL SERVER - Inserting data from one table into another table - INSERT IN SELECT - SELECT IN TO TABLE

+3
source

try it

 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 put alias in your requested table

+3
source

Try the following:

  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
0
source

I think you need to add an alias that was suggested or (more complex) to remove the brackets, * , FROM , second SELECT and move INTO foo :

 SELECT --- removed: FROM --- removed: ( --- removed: SELECT DATEPART(MONTH,a.InvoiceDate) as CalMonth ,DATEPART(YEAR,a.InvoiceDate) as CalYear ,... INTO foo --- moved FROM sales a UNION ALL SELECT ds.CalMonth as CalMonth ,... FROM sales1 ds ; --- removed: )
0
source

More articles:


All Articles