Do you know how I can resolve the error "Incorrect syntax near the keyword 'primary'? I am altering a column in a SQL Server 2008 table.

INDENTITY(1,1)  belongs to int as a specialisation of the int type as identity. Primary Key has to come after that. Yes, IDENTITY(1,1) makes it an auto-incrementing number, which in turn makes it a good candidate for a primary key, but in itself it just is a specialisation of the int type, just like NOT NULL also is a specialisation you can apply to any data type. Specification as PRIMARY KEY then can come last. The order of options is somewhat flexible, but not all combinations work.

So

...ALTER COLUMN stopID int IDENTITY(1,1) NOT NULL primary key

Select allOpen in new window

ALTER TABLE dbo.tbl_BU_StopVoidPay
ALTER COLUMN stopID int IDENTITY(1,1) NOT NULL primary key

Now I get the following response:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'IDENTITY'.

This was taken from an answer, so should have worked out fine.

Then try an alteration like this:

ALTER TABLE dbo.tbl_BU_StopVoidPay
ALTER COLUMN stopID int NOT NULL IDENTITY(1,1)  primary key

Select allOpen in new window

Are you really altering a column? What type is it now? An int without the incrementing identity property?

Bye, Olaf.

Here is what my problem was originally. I have 2 tables.

One table titled dbo.tbl_CSL_ImportSV has all the fields contained in table dbo.tbl_BU_StopVoidPay except that table dbo.tbl_BU_StopVoidPay contains 1 additional column titled stopID defined as (int, not null).

When I execute the following statement, I get the error:

INSERT INTO dbo.tbl_BU_StopVoidPay (bank, branch, chkNum, accountNum, chkDt,
typeSV, amount, dtCreated, dtCompleted,  createdBy, completedBy,  
entity, userid, howRecieved, sStatus)  
(SELECT I.bank, I.branch, I.checks, I.custBase, I.chkDt, I.typeSV, I.amount,
I.procDate, I.dtImported, I.importedBY, I.importedBY, I.entity, I.userid, I.howRecieved,
I.sStatus
FROM dbo.tbl_CSL_ImportSV AS I)


Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'stopID', table 'CSL.dbo.tbl_BU_StopVoidPay'; column does not allow nulls. INSERT fails.
The statement has been terminated.