SQL Server Auto Number Field

[alexkeel79]

Hello,

I'm trying to import about 600 rows into a SQL Server table. I want to auto number a field but I can't seem to figure out how to do it. I know in Oracle you can create a sequence that will automatically give that field an auto number but how do you do it in SQL server? Thanks

 

[mrdenny]

You want to use what's called an identity field in SQL Server. When creating the table in Enterprise Manager, set the field type to int, then below where it says Identity Field set that to yes. Then set the seed and incriment as they should be. Default usually works.

See "create table" in BOL for exact code examples.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[alexkeel79]

Okay that works if I am going in and manually typing in information but I get an error when I try to import in Eneterprise Manager. It tells me that the field "ID" (the autonumbered field) does not allow a null value. How do I get the number to auto generate when I am importing data?

 

[hmckillop]

How are you importing the data?
Is it through BCP, or DTS or?

When using an autonumber field you need to ensure you dont specify anything going into it, i.e.
For a table like this

CREATE Table MyTable(
 MyIdentity INT Identity(1,1),
 Field1 INT
 Field2 Varchar(10)
)

INSERT INTO MyTable (field1, field2) VALUES (1, 'TestData')

"I'm living so far beyond my income that we may almost be said to be living apart

 

[alexkeel79]

I am importing through the Enterprise manager GUI. When I get to the auto numbered field I set it to <ignore>.

 

[alexkeel79]

I figured it out. When I did the data transformation step I had to uncheck the box that said "Enable Identity Import" which seems a little weird because I would think that would disable me from using the identity field.

 

[mrdenny]

Enable Identity Import allows you to feed data into the identity column instead of having it load the field for you.

Denny

--Anything is possible. All it takes is a little research. (Me)