• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Adding a row keyed by an IDENTITY

Hi,
I have an integer key that is an IDENTITY starting at 100.  Can I INSERT a row and specify the identity key value if it's less than 100?  Can I ever specify the IDENTITY value in an INSERT?

Thanks,
hefterr
0
hefterr
Asked:
hefterr
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, you can, using the SET IDENTITY_INSERT syntax:
http://msdn.microsoft.com/en-us/library/ms188059.aspx
0
 
ThinkPaperCommented:
never tried it, but looks like you can with IDENTITY_INSERT
http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server
0
 
chapmandewCommented:
set identity_insert tablename on

insert into tablename(field1, field2, idfield)
select 'val', 'val', 3

set identity_insert off

--VERY important to set it back to off
0
 
hefterrAuthor Commented:
Hey Thanks.  One more question.  If I insert a row higher than the identity counter for my table, will that identity value be skipped ny SQL Server in the future or will I end up with a duplicate key situation?

Thanks again.
hefterr
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no, the value will not be skipped.
but the insert trying to use that value will fail IF the field is defined as primary key.
it will NOT retry a second time with the same value after that (the identity value has then been "consumed"

check also out: DBCC CHECKIDENT: http://msdn.microsoft.com/library/ms176057.aspx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now