Incorrect Syntax near '-' .(Microsoft SQL Server Native Client 10.0) [closed]

Question

While doing SSIS job I got an error in my create table query

Incorrect Syntax near '-' .(Microsoft SQL Server Native Client 10.0)

SQL statement:

CREATE TABLE Staging_ACD_Precision_Queue 
(
    PrecisionQueueID int,
    BucketIntervalID int,
    EnterpriseName varchar(32),
    AgentOrdering int,
    CallOrdering int,
    Description varchar(255),
    ServiceLevelThreshold int,
    ServiceLevelType smallint,
    ForceExpandingQueue varchar(1),
    Deleted varchar(1),
    ChangeStamp int,
    Partner varchar(4),
    Center varchar(4),
    Partner-Center varchar(9),
    LOB varchar(4),
    Circle varchar(4),
    TypeOfBusiness varchar(4)
)

I tried the above query. I guess from the error message that, the issue is in Partner-Center varchar(9) but I failed to fix this error. Since I'm new to SSIS and SQL queries I don't know to fix the issue.

Answer 1

You need to put square brackets around Partner-Center, e.g. [Partner-Center]. Embedded spaces or special characters are not allowed in field names, but you can escape them by putting the field name within square brackets.

CREATE TABLE Staging_ACD_Precision_Queue (
    PrecisionQueueID int,
    BucketIntervalID int,
    EnterpriseName varchar(32),
    AgentOrdering int,
    CallOrdering int,
    Description varchar(255),
    ServiceLevelThreshold int,
    ServiceLevelType smallint,
    ForceExpandingQueue varchar(1),
    Deleted varchar(1),
    ChangeStamp int,
    Partner varchar(4),
    Center varchar(4),
    [Partner-Center] varchar(9),
    LOB varchar(4),
    Circle varchar(4),
    TypeOfBusiness varchar(4)
)

As others have stated here, I too would like to recommend you avoid using a hyphen in the field name and go with either Partner_Center or PartnerCenter as the field name instead of Partner-Center.

Answer 2

SQL Server does not allow - in bare field names. However, you can "escape" it by surrounding the field name with square brackets ([]):

CREATE TABLE Staging_ACD_Precision_Queue (
    PrecisionQueueID int,
    BucketIntervalID int,
    EnterpriseName varchar(32),
    AgentOrdering int,
    CallOrdering int,
    Description varchar(255),
    ServiceLevelThreshold int,
    ServiceLevelType smallint,
    ForceExpandingQueue varchar(1),
    Deleted varchar(1),
    ChangeStamp int,
    Partner varchar(4),
    Center varchar(4),
    [Partner-Center] varchar(9),
    LOB varchar(4),
    Circle varchar(4),
    TypeOfBusiness varchar(4)
)