autonumbering question - newbie

I'm just getting into SQL server, so I apologize if this is a basic
question. Most of my experience with DBs comes from Oracle, so that's
the reference from which I'm working.

I'm trying to use the identity property in MS SQL Server to autonumber
a unique identifier field, and I'm wondering if it's possible to
manually assign a value for this field in my insert statement, or if
using the identity property ties me to the sequence. I know in
Oracle, since it uses sequences, one can choose whether or not to use
the next number in the sequence to assign a value, or to do it
manually. Since the insert statement for SQL Server doesn't allow a
value (or a reference or any kind of placeholder) of any sort, I'm at
a loss as to how to manually assign a value. Is it possible?

If it's not possible, could somebody lead me in the direction of
another way to address the situation? My main concern is having to
import data from another DB x years from now and that I'll want to
keep the unique contraints and all referential integrity associated
with the importing database.

Any help anybody could give me would be greatly appreciated. Thanks!You can manually assign a value to an identity column by turning on
IDENTITY_INSERT and explicitly specifying a column list. For example:

CREATE TABLE MyTable
(
MyIdentityColumn int NOT NULL IDENTITY(1, 1),
MyData int NOT NULL
)
GO
SET IDENTITY_INSERT MyTable ON
GO
INSERT INTO MyTable (MyIdentityColumn, MyData) VALUES(1, 1)
INSERT INTO MyTable (MyIdentityColumn, MyData) VALUES(2, 1)
INSERT INTO MyTable (MyIdentityColumn, MyData) VALUES(3, 1)
GO
SET IDENTITY_INSERT MyTable OFF
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"systems_newbie" <ccrupper@.hotmail.com> wrote in message
news:5939e7a7.0308201121.4aca153b@.posting.google.c om...
> I'm just getting into SQL server, so I apologize if this is a basic
> question. Most of my experience with DBs comes from Oracle, so that's
> the reference from which I'm working.
> I'm trying to use the identity property in MS SQL Server to autonumber
> a unique identifier field, and I'm wondering if it's possible to
> manually assign a value for this field in my insert statement, or if
> using the identity property ties me to the sequence. I know in
> Oracle, since it uses sequences, one can choose whether or not to use
> the next number in the sequence to assign a value, or to do it
> manually. Since the insert statement for SQL Server doesn't allow a
> value (or a reference or any kind of placeholder) of any sort, I'm at
> a loss as to how to manually assign a value. Is it possible?
> If it's not possible, could somebody lead me in the direction of
> another way to address the situation? My main concern is having to
> import data from another DB x years from now and that I'll want to
> keep the unique contraints and all referential integrity associated
> with the importing database.
> Any help anybody could give me would be greatly appreciated. Thanks!|||It worked perfectly! Thank you very much for the solution and for the
prompt reply.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Charles Crupper" <ccrupper@.hotmail.com> wrote in message
news:3f43df99$0$200$75868355@.news.frii.net...
> It worked perfectly! Thank you very much for the solution and for the
> prompt reply.

I'll just point out a possible "flaw" in Dan's suggestion.

If two clients try to update the row with the same ID, you'll have to handle
that.

And that could cause issues if a client tries to do an insert and use the
Identity column vs. a client not using it.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||> I'll just point out a possible "flaw" in Dan's suggestion.
> If two clients try to update the row with the same ID, you'll have to
handle
> that.
> And that could cause issues if a client tries to do an insert and use
the
> Identity column vs. a client not using it.

Good point. I should have mentioned that IDENTITY_INSERT should be
used for infrequent data migration or import and not part of routine
processing.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
news:R5S0b.122271$wk4.47486@.twister.nyroc.rr.com.. .
> "Charles Crupper" <ccrupper@.hotmail.com> wrote in message
> news:3f43df99$0$200$75868355@.news.frii.net...
> > It worked perfectly! Thank you very much for the solution and for
the
> > prompt reply.
> I'll just point out a possible "flaw" in Dan's suggestion.
> If two clients try to update the row with the same ID, you'll have to
handle
> that.
> And that could cause issues if a client tries to do an insert and use
the
> Identity column vs. a client not using it.
>
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!|||"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:y4U0b.3512$sV.364@.newsread1.news.atl.earthlin k.net...
> > I'll just point out a possible "flaw" in Dan's suggestion.
> > If two clients try to update the row with the same ID, you'll have to
> handle
> > that.
> > And that could cause issues if a client tries to do an insert and use
> the
> > Identity column vs. a client not using it.
> Good point. I should have mentioned that IDENTITY_INSERT should be
> used for infrequent data migration or import and not part of routine
> processing.

I'll admit I had to test something first and was pleasantly surprised by the
results.

I wasn't sure if the seed was updated if you did IDENTITY_INSERTs, but
apparently it is, which is nice.

(I still have nightmares about SQL 6.5 and how easily it would fubar it's
IDENTITY Seed.)

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||> (I still have nightmares about SQL 6.5 and how easily it would fubar
it's
> IDENTITY Seed.)

Yea, the whole identity assignment method was re-written for SQL 7 and
above. I have no qualms about using IDENTITY is later releases.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------

"Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
news:bXU0b.122319$wk4.13930@.twister.nyroc.rr.com.. .
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:y4U0b.3512$sV.364@.newsread1.news.atl.earthlin k.net...
> > > I'll just point out a possible "flaw" in Dan's suggestion.
> > > > If two clients try to update the row with the same ID, you'll have
to
> > handle
> > > that.
> > > > And that could cause issues if a client tries to do an insert and
use
> > the
> > > Identity column vs. a client not using it.
> > Good point. I should have mentioned that IDENTITY_INSERT should be
> > used for infrequent data migration or import and not part of routine
> > processing.
> I'll admit I had to test something first and was pleasantly surprised
by the
> results.
> I wasn't sure if the seed was updated if you did IDENTITY_INSERTs, but
> apparently it is, which is nice.
> (I still have nightmares about SQL 6.5 and how easily it would fubar
it's
> IDENTITY Seed.)
>
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP