Why did this MSSQL create table statement fail

[KAMAZON]

The top CREATE TABLE Customers query did not work, but the bottom one does!

I get error:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'PA'.


CREATE TABLE Customers
(
CustomerID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY (1000, 1),
Address VARCHAR(50) NULL,
City VARCHAR(50) NULL,
State CHAR(2) NULL DEFAULT 'NY' CHECK(State IN('NY', 'NJ' 'PA')),
ZipCode VARCHAR(10) NULL,
Country VARCHAR(30) NULL
)



CREATE TABLE Customers
(CustomerID int Not Null
Primary Key
Identity(1000, 1),
Address varchar(50) Null,
City varchar(50) Null,
State char(2) Null
Default 'NY'
Check(State In('NY', 'NJ', 'PA')),
ZipCode varchar(10) Null,
Country varchar(30) Null)


Thanks in advance!

 

[Unheard]

CREATE TABLE Customers
(
CustomerID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY (1000, 1),
Address VARCHAR(50) NULL,
City VARCHAR(50) NULL,
State CHAR(2) NULL DEFAULT 'NY' CHECK(State IN('NY', 'NJ', 'PA')),
ZipCode VARCHAR(10) NULL,
Country VARCHAR(30) NULL
)

You left out a comma in the first one. (Bolded for you)

 

[tfinch2]

Originally posted by: Unheard

CREATE TABLE Customers
(
CustomerID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY (1000, 1),
Address VARCHAR(50) NULL,
City VARCHAR(50) NULL,
State CHAR(2) NULL DEFAULT 'NY' CHECK(State IN('NY', 'NJ', 'PA')),
ZipCode VARCHAR(10) NULL,
Country VARCHAR(30) NULL
)

You left out a comma in the first one. (Bolded for you)

Those commas will getcha. :thumbsup:

 

[KAMAZON]

Jeez u guys are good, thanks a lot!

 

[troytime]

just wait until your first update without a where clause 😀

 

[BoberFett]

Originally posted by: troytime
just wait until your first update without a where clause 😀

Last place I worked some nimrod consultant did that. On the production database.

He figures he's an expert, no point in testing a query on the test server, just go live with it.

I could have killed that sonofabitch.

 

[UCJefe]

haha. This is second SQL comma problem we've had in a couple days. Wonder if it was the same guy?

 

[Unheard]

Originally posted by: troytime
just wait until your first update without a where clause 😀

We've all done it. And we've all spent 5 hours restoring the data we fubar'd 🙂

 

[Furor]

Originally posted by: Unheard

Originally posted by: troytime
just wait until your first update without a where clause 😀

We've all done it. And we've all spent 5 hours restoring the data we fubar'd 🙂

I think that deserves it's own thread 🙂

 

[troytime]

Originally posted by: Furor

Originally posted by: Unheard

Originally posted by: troytime
just wait until your first update without a where clause 😀

We've all done it. And we've all spent 5 hours restoring the data we fubar'd 🙂

I think that deserves it's own thread 🙂

coworker did it about 20 minutes ago on the live db
dbadmin caught it and stopped it, but hundreds of thousands of rows are hosed

we're still trying to fix it

 

[alpha88]

I've not done an update without the where clause, but I've frequently screwed up a while loop, and then complained to others that my query was taking forever.