Identify column

Question

Post reply

Identify column

Jpotucek

SSCoach

Points: 18583
More actions
March 20, 2006 at 1:56 pm

#167345

I have a table with an identify column. What happens if you manually insert new rows in that table and increment the identify column manually.. When a new row gets inserted by the application, will it automatically pick up at the next highest number ?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

To do this you need to use the set identity_insert Tablename on.

Check out Identity_insert in Books online.

Lowell SSC Guru Points: 323518 More actions · Answer 2

yes, it will get the next value; here's an example:

create table #tmp (tmpid int identity(1,1) not null primary key,tmptext varchar(30) )

insert into #tmp(tmptext) values('SQL')

insert into #tmp(tmptext) values('SERVER')

insert into #tmp(tmptext) values('ROCKS')

set identity_insert #tmp on

insert into #tmp(tmpid,tmptext) values(99,'YOUR')

insert into #tmp(tmpid,tmptext) values(105,'SOCKS')

set identity_insert #tmp off

insert into #tmp(tmptext) values('OFF')

select * from #tmp

tmpid tmptext

----------- ------------------------------

1 SQL

2 SERVER

3 ROCKS

99 YOUR

105 SOCKS

106 OFF

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jpotucek SSCoach Points: 18583 More actions · Answer 3

Thanks much. Basically what I'm doing is DTSing 33 rows from a .csv file to my table - so it doesn't look like I need to set identity insert on.. I just wasn't sure what would happen after the fact : ) but it sounds like I'm good to go