Insert value in an auto-identity column

I'm a bit new to SQL Server, but here is the problem I am trying to get
around:

1. I have lets say an Employee table where the emp_id is an identity
column that is auto assigned by the db.
2. I wish to insert values into the tables, but sometimes I may wish to
use the value of the emp_id that i supply while inserting (and
sometimes not).

Is there any way in MS SQL to do this? For e.g. is it setup like a
constraint that I can temporarily drop. I do not want to alter the
table, so that option is ruled out.

Any other ways to do this (I know bcp is one of them that allows me to
do this, but looking for ways in the db itself using SQL if possible)

Thanks,
Bharat"Bharat" <go2bharat@.gmail.com> wrote in message
news:1112626803.244086.213160@.f14g2000cwb.googlegr oups.com...
> Xref: news.bluewin.ch comp.databases.ms-sqlserver:126921
> I'm a bit new to SQL Server, but here is the problem I am trying to get
> around:
> 1. I have lets say an Employee table where the emp_id is an identity
> column that is auto assigned by the db.
> 2. I wish to insert values into the tables, but sometimes I may wish to
> use the value of the emp_id that i supply while inserting (and
> sometimes not).
> Is there any way in MS SQL to do this? For e.g. is it setup like a
> constraint that I can temporarily drop. I do not want to alter the
> table, so that option is ruled out.
> Any other ways to do this (I know bcp is one of them that allows me to
> do this, but looking for ways in the db itself using SQL if possible)
> Thanks,
> Bharat

See SET IDENTITY_INSERT in Books Online.

Simon