Use generated identity for another column within the same insert

Question

Post reply

Use generated identity for another column within the same insert

vba-dev

Right there with Babe

Points: 717
More actions
May 19, 2010 at 12:47 pm

#219180

I've studied some posts which deal with this question. Usually, the solutions were too advanced for me to figure out.
I'd like to keep this as simple as possible because I'm an old JET guy and have only been working with SQL Server for a few years.
Simply put:
I would like to get the value generated for an auto-increment column and use it to populate another column during the same insert operation.
The desired effect would look like this, if it were possible:
INSERT INTO PONumber (PONumber, CreateUser, CreateDate)
VALUES ('SomePrefix' + SCOPE_IDENTITY(), SUSER_SNAME(), GETDATE())
SCOPE_IDENTITY() is, of course, not available yet. Yes, I realize I'd need to do some casting for the concatenation to work - I just want to keep the psuedo-code as simple as possible. I'm hoping that including that bit helps convey the intended usage, which is to generate sequential numbers for purchase orders, jobs, invoices, etc.
Is there some simple way to accomplish this?
I've seen some usage of the Output clause which doesn't quite get there, and some combinations of Triggers and the Output clause, which I didn't quite follow. I'd rather avoid building a lot of extra objects, if possible.
I do know that, unlike JET, SQL Server auto-generated values are not created until the SQL for the insert is executed, but I saw some promising examples which I couldn't grasp well enough to implement. So, I'd like to find out from the pros if it is, in fact, possible to do what I'm asking.
Thanks for any help.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

one easy way is instead of allowing a vlaue to be entered for your PONumber, make it a calcualted column instead:

CREATE TABLE Example(

ID int identity(1,1) primary key,

PONumber AS 'SomePrefix' + CONVERT(varchar,ID), --<<calculated

PONumber2 AS 'PO-' + RIGHT('0000' + CONVERT(varchar,ID),5) PERSISTED, --with preceeding zeros? ie PO-00002

Other Columns varchar(30) )

the persisted will make the column exist in the table, so you can index it

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!

vba-dev Right there with Babe Points: 717 More actions · Answer 2

Thanks, that seems pretty straightforward.

I guess it's time to complicate the waters, though! The prefix is user-defined, and stored in a different table. I've got a suffix column in that table as well.

Can a calculated column make use of values from another table/fields?

P.S. Just saw your edit after posting this reply...studying it again.

Lowell SSC Guru Points: 323518 More actions · Answer 3

still not so bad, but the same solution: the end users will have to enter/select the prefix and suffix, which could even have defaults on them, that's all:

CREATE TABLE EXAMPLE(

id int identity(1,1) primary key,

Prefix varchar(5) DEFAULT 'PO',

suffix varchar(5) DEFAULT 'SFX',

PONumber AS

ISNULL(Prefix,'PO') --in case it was left blank?

+ '-' --dashes in there?

+ RIGHT('0000' + CONVERT(varchar,ID),5)

+ '-' --dashes in there?

+ ISNULL(Suffix,'XX') PERSISTED ) --different from the Default, you notice that?

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!

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 4

Use a separate sequence table ("Seq" in this example) to maintain the IDENTITY column and then populate the PO table like this:

INSERT INTO Seq DEFAULT VALUES; -- Seq has IDENTITY column

INSERT INTO PONumber (PONumber) VALUES ('PO'+CAST(SCOPE_IDENTITY() AS VARCHAR(10)));

In SQL Server 2008 and later you can do this in one neat statement:

INSERT INTO PONumber (PONumber)

SELECT ('PO'+CAST(id AS VARCHAR(10)))

FROM

(INSERT INTO Seq

OUTPUT inserted.$IDENTITY

DEFAULT VALUES

)t(id);

vba-dev Right there with Babe Points: 717 More actions · Answer 5

Lowell,

That's pretty cool. I had not explored computed columns until now.

What do you typically do when, for example, the number of PO's rises above 99,999?

I used to do something very similar in JET. When I left the company, the PO count was above 70,000. No telling where they are now, 3 yrs later 😀

vba-dev Right there with Babe Points: 717 More actions · Answer 6

David,

Thanks very much. I was actually trying to implement something very similar to this, but I got hung up. I get the prefix and suffix from whatever table they live in and put them into local variables.

The problem is that I'd have to create a sequence table for each type of number I want to generate, which I was hoping to avoid. Admittedly, I have no better reason for this decision than the fact that I'm picky and I don't like clutter in my dbs. Necessity rules, however.

Now, the Output clause method here - I saw examples in several places, none of which used it quite like you are doing here. Does this basic syntax work in 2005?

INSERT INTO PONumber (PONumber)

SELECT ('PO'+CAST(id AS VARCHAR(10)))

FROM

( INSERT INTO Seq

OUTPUT inserted.$IDENTITY

DEFAULT VALUES

)t(id);

The '$IDENTITY' I have not seen - I suppose that's 2008 specific - what about the rest?

Also, the 't(id)' portion - I'm not following that.