SQL Server error: Incorrect syntax near 'MODIFY'. in my query

Question

This is the query that I am trying to execute

use [warz]
go

ALTER TABLE dbo.items_lootdata
MODIFY TABLE [RecordID] [int] IDENTITY(1,1) NOT NULL
go

I got the error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'MODIFY'.

Any ideas?

Answer 1

Add a new column:

use [warz]
go


ALTER TABLE dbo.items_lootdata
ADD [RecordID] [int] IDENTITY(1,1) NOT NULL
go

Modify an existing column:

use [warz]
go

-- remember you cannot alter an existing column to
-- identity, following is only for syntax
ALTER TABLE dbo.items_lootdata
ALTER COLUMN [RecordID] [int] IDENTITY(1,1) NOT NULL
go

One thing to note is that altering an existing column and that too in an Identity is rather tricky.

Edit 1: Commenting the ALTER syntax to correctly highlight the issue as pointed out by marc_s

Relevant solution can be found at this answer.

Answer 2

Looks like your MODIFY TABLE should probably be ALTER COLUMN instead.

IDENTITY can be a pain, as you're not going to have much luck adding it as a property to a column. Tables with IDENTITY columns need to have them ( the IDENTITY columns, that is ) created that way and afterwards it is not easy to get rid of that property either.

Assuming you have a table that is more or less like this:

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'items_lootdata'
                    AND type = 'U' )
BEGIN
    CREATE TABLE dbo.items_lootdata
    (                                                   
        RecordID    INTEGER NOT NULL,
        MoreData    BIT
    );
END;
GO

If you want the RecordID to be an IDENTITY enabled column, if your table is currently empty you can simply recreate the object:

    IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'items_lootdata'
                    AND type = 'U' )
BEGIN
    CREATE TABLE dbo.items_lootdata
    (                                                   
        RecordID    INTEGER IDENTITY( 1, 1 ) NOT NULL,
        MoreData    BIT
    );
END;
    GO

I prefer to perform a meta-data switch if possible, however, as it would also preserve any data currently contained in your table. This involves a rename, the creation of a new table and the switch itself. You will probably also want to set the first parameter of your IDENTITY column to something greater than or equal to the current maximum value of RecordID as well, as I assume you'll be wanting to put a primary key on that column afterwards.

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'items_lootdata'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.items_lootdata;  
    CREATE TABLE dbo.items_lootdata
    (                                                   
        RecordID    INTEGER NOT NULL, 
        MoreData    BIT
    );

    INSERT INTO dbo.items_lootdata( RecordID, MoreData )
    VALUES( 1, 1 );
END;
GO

EXECUTE dbo.sp_rename @objname = 'dbo.items_lootdata', @newname = 'items_lootdata_old'

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'items_lootdata'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.items_lootdata;  
    CREATE TABLE dbo.items_lootdata
    (                                                   
        RecordID    INTEGER IDENTITY( 1, 1 ) NOT NULL, 
        MoreData    BIT
    );
END;
GO

ALTER TABLE dbo.items_lootdata_old
SWITCH TO dbo.items_lootdata;
GO

DROP TABLE dbo.items_lootdata_old;
GO

INSERT INTO dbo.items_lootdata ( MoreData )
VALUES ( 0 );
GO

SELECT  *
FROM    dbo.items_lootdata;

Note that for the ALTER ... SWITCH to work, you may have to modify the things you actually can sync before trying it. The SWITCH will fail if the structures aren't otherwise identical ( ie: the original RecordID column is nullable ), so those things will need to be addressed.