CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1129400
user1129400

Reputation: 25

Sql server error Incorrect syntax near '('

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.

Upvotes: 0

Views: 2801

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

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

Upvotes: 5

Related Questions