"systems_newbie" <ccrupper@hotmail.com> wrote in message
news:5939e7a7.0308201121.4aca153b@posting.google.com...
> 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!

"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.
>

> *** 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.

"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.earthlink.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.
>

> --
> 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.)

"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.earthlink.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
> >
>
>