insert lines into a db with Identity

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

insert lines into a db with Identity

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-01-07 : 09:01:31

i have inserted into a table with a column called id defined as Identity(Yes) line from a file.
the line in the file in the id column had a number in each row
so now in the table i have
1 some data
2 some data
....
100000 some data

the things is that some lines were missing , for example :
rows
60000
...
60159
so there is a "hole" i nthe table's id at this numbers
my question is like this :
1)if now i insert a new row will the id column will get the value 100,001 or it will recive the number of a missing line?
2) what is the exect diffrence between Identity (Yes) and Yes (Not For Replication)?

thnaks in advance
peelg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-07 : 09:29:31

The idenrity value will be allocated as the next from the current seed. This you can checck via dbcc checkident.
The value will be 100001 (probably).
The reason you get gaps is that the identity value is allocated at the begining of the insert - if the insert fails then the value is still allocated and will not be reused.
Identities are not guaranteed to be unique nor consecutive - just the next from the seed, so you always need a unique index on them if you require uniqueness.

Not for replication - have a look in bol. It means that replicated rows will rtain the identity value from the source but the identity property on the destination is used for other inserts.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-01-07 : 09:33:38

How did you inport the data (bcp?)
If that is the case, did you look at the KEEPIDENTITY option?

You can use DBCC CHECKIDENT('tablename') to see what the current identity value is.
Basically it will not insert in the "hole", but at the end of the table.

You can use SET IDENTITY_ISERT tablename ON
if you want to insert into the "hole", but you will have to provide the value for the identity column yourself.

Just check "NOT FOR REPLICATION" in BOl, it explains what it does.
It's only interesting if you are actually replicating data.

rockmoose

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-01-09 : 01:01:56

ok thanksalot all of u

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources