Create SQL Server table error

Question

I'm trying to create a new table with the results from a query. I'm working in SQL Server 2012

CREATE TABLE [Service Contract Data].[dbo].[filtered_data] AS 
(
    SELECT * 
    FROM [Service Contract Data].[dbo].[New Data]
    LEFT JOIN [Service Contract Data].[dbo].[OctMktSeg] ON [Service Contract Data].[dbo].[New Data].[Customer ID] = [Service Contract Data].[dbo].[OctMktSeg].[Account Number]
    LEFT JOIN [Service Contract Data].[dbo].[tblMktSeg&MPI] ON [Service Contract Data].[dbo].[New Data].[Customer ID] = [Service Contract Data].[dbo].[tblMktSeg&MPI].[Customer ID1]
    WHERE 
        (MKTSEG LIKE '%Repair%' OR MKTSEG LIKE 'Auto Glass' OR 
         MKTSEG LIKE 'IA - Individual' OR MKTSEG IS NULL)
        AND (MPI LIKE 'N' OR MPI IS NULL)
        AND [Item Category] LIKE '%Term'
        AND [Month] >= '2015-01-01 00:00:00.000')
    ;

It keeps telling me there's "Incorrect syntax" near the first open parentheses and I have no idea why. Thoughts?

Are there columns in the joined table that overlap in the query? — JB King
– JB King, Commented Jan 13, 2016 at 22:36

chancrovsky · Accepted Answer · 2016-01-13 22:39:03Z

1

I don't know if that syntax is valid in SQL SERVER, Have you tried this before ? This is valid syntax in order to create a new table:

SELECT 
-- SPECIFY THE NAME OF THE COLUMNS IN THE SELECT LIST, AVOID *
* 
--INSERT DATA INTO NEW TABLE
INTO [Service Contract Data].[dbo].[filtered_data]
from [Service Contract Data].[dbo].[New Data] t1
left join [Service Contract Data].[dbo].[OctMktSeg] t2 ON t1.[Customer ID] = t2.[Account Number]
left join [Service Contract Data].[dbo].[tblMktSeg&MPI] t3 on t1.[Customer ID] = t3.[Customer ID1]
WHERE (MKTSEG like '%Repair%' or MKTSEG like 'Auto Glass' or MKTSEG like 'IA - Individual' or MKTSEG is null)
    and (MPI like 'N' or MPI is null)
    and [Item Category] like '%Term'
    and [Month] >= '2015-01-01 00:00:00.000'

answered Jan 13, 2016 at 22:39

chancrovsky

5803 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wes Larson · Accepted Answer · 2016-01-13 22:40:18Z

1

It looks like you're missing an open peren ( right after your WHERE

WHERE ((MKTSEG like '%Repair%' or MKTSEG like 'Auto Glass' or MKTSEG like 'IA - Individual' or MKTSEG is null)
and (MPI like 'N' or MPI is null)
and [Item Category] like '%Term'
and [Month] >= '2015-01-01 00:00:00.000')

answered Jan 13, 2016 at 22:40

Wes Larson

1,0709 silver badges17 bronze badges

1 Comment

Emma Over a year ago

The query executes correctly on it's own so I don't think it's an issue with the select query

Collectives™ on Stack Overflow

Create SQL Server table error

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related