ALTER to an Int Identity(1,1)

Question

ALTER to an Int Identity(1,1)

jaguarxj12l

Old Hand

Points: 393
More actions
January 14, 2008 at 9:35 am

#185431

Hi,
I created a piece of code that stores the data from an Excel sheet into SQL Server 2005. I use the below listed code:
strSQL = "SELECT * INTO " & sTabel & " FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Projects\Inspectieformulier\Data + datamodel.xls', 'SELECT * FROM [" + sSheet + "$]')"
ConnExcel.Execute strSQL, lngRecsAff, adExecuteNoRecords
This code however insists on creating the table itself. That is no problem to me but it 'invents' the data types also by itself.
The columns named ID however HAVE to be Identity(1, 1).
How can I - afterwards - still change the columns ID? All the others column are no problem.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

The table is being created because that's what SELECT INTO does. If you were to use INSERT INTO SELECT, you could precreate the table.

Do you want to add identity property to the ID column after the table is created? If so, use ALTER TABLE ALTER COLUMN. The syntax is specified in BooksOnLine.

Greg

jaguarxj12l Old Hand Points: 393 More actions · Answer 2

Thanks Greg,

That did it for me, I completely worked it out now. I prefer to pre-create the table because of the reserved lengths, etc. If I correct afterwards it's almost the same amount of work as pre-creating.

Thanks again!

ccimi SSC Rookie Points: 42 More actions · Answer 3

I am using SQL SERVER 2005, tried using the script for adding the identity property to the already existing column.

ALTER TABLE test.test1 ALTER COLUMN ID INT IDENTITY(1,1)

I refered the Books online and wrote this query. Getting an error like this

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'IDENTITY'.

Help is highly appreciated.Its very urgent.

Cimi

SQLZ SSChampion Points: 12872 More actions · Answer 4

I don't believe you can alter an existing column and make it an IDENTITY. You can add an identity column to an existing table.

Karl
source control for SQL Server

ccimi SSC Rookie Points: 42 More actions · Answer 5

ccimi

SSC Rookie

Points: 42

February 15, 2008 at 3:44 am

#778780

Thank You Karl!

Cimi

diderot.britto SSC Enthusiast Points: 155 More actions · Answer 6

SQLZ (2/15/2008)
I don't believe you can alter an existing column and make it an IDENTITY. You can add an identity column to an existing table.

Using Object Explorer in SQL Server Management Studio, select the column you want to modify, right click and choose modify. Then on Column Properties modify (Is Identity) to Yes.

It worked.

_______________

Diderot