Sql server error Incorrect syntax near '('

Question

create table new_temp AS
(SELECT * 
  FROM [Sales].[dbo].[SecondarySales]
  left JOIN [Sales].[dbo].[PrimarySales]
  On [Sales].[dbo].[SecondarySales].[SalesFromID]=[Sales].[dbo].[PrimarySales].[SalesToID]);

I am getting error on this query when i am trying to create table in sql server management console "Incorrect syntax near '('." which is near the AS keyword.

In addition to using SELECT INTO instead of this, you'll also need to alias any fields that appear in both, as a table can't have multiple fields with the same name. — Hart CO

Pரதீப் Pரதீப் · Accepted Answer · 2015-03-12T17:41:17

That's not the right way to create table from select statement in sql server

Use INTO clause

SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it

SELECT A.Col1,A.col2,B.Col1 as B_col1,B.col3...
INTO   new_temp
FROM   [Sales].[dbo].[SecondarySales] A
       LEFT JOIN [Sales].[dbo].[PrimarySales] B
              ON [Sales].[dbo].[SecondarySales].[SalesFromID] = [Sales].[dbo].[PrimarySales].[SalesToID];

As mentioned by HART CO in comment column names should be unique in a table if there is any column name is same in both the tables then you have to give a alias name.

Update:

SELECT a.PrimarySalesId,a.SalesFromID,a.SalesToID,a.InvoiceNumber,a.InvoiceDate,
       a.ReceiveDate,a.TotalQuantity,a.TotalAmount,a.IsAcknowledgementRequired,
       a.IsReceived,a.SalesType,a.AcknowlegeDate,a.Status,a.SalesMan,
       a.RecordCreationDate,a.ModifiedOn,a.CreatedBy,a.ModifiedBy,
       b.SecondarySalesID,b.AcknowlegeDate,
       b.CreatedBy,
       b.InvoiceDate as SecondarySales_InvoiceDate,
       b.InvoiceNumber as SecondarySales_InvoiceNumber,
       b.IsAcknowledgementRequired,
       b.IsReceived as SecondarySales_IsReceived,
       b.Modified,
       b.ModifiedOn as SecondarySales_ModifiedOn,
       b.ReceiveDate,
       b.RecordCreationDate,
       b.SalesFromID as SecondarySales_SalesFromID,
       b.SalesMan as SecondarySales_SalesMan,
       b.SalesManID,
       b.SalesToID as SecondarySales_SalesToID,
       b.SalesType,
       b.Status,
       b.TotalAmount,
       b.TotalQuantity
INTO   new_temp
FROM   [Sales].[dbo].[SecondarySales] b
       LEFT JOIN [Sales].[dbo].[PrimarySales] a
              ON b.SalesFromID = a.SalesToID

Sql server error Incorrect syntax near '('

1 Answers