Using generated identity value in another column 1

[BomberMan2K]

Hi,

I'm want to take a generated identity value in a given record, and in the same insert operation put that value in another column.

For example - I have the ID column, and I want to insert a new record and put the ID value generated in the NEWID column as well.

Is there any way to accomplish this?

thank you,
Roman.

 

[SQLSister]

look at scope_identity in Books onLine
under no circumstances use @@Identity

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

You were advised yesterday to use Scope_Identity(), so I can only presume you are having problems implementing it. If this is the case, please show some sample code where you are having a problem. It'll make it easier for us to help you.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BomberMan2K]

Hi, thank you for your replies.

I managed to use Scope_Identity very well, it did work but for other purposes.
Now I wish to make the following:
Assuming I have a table as follows:
UserID [Identity PK]
UserName [varchar]
UserNumber [varchar]

Now, I wish each time I create a new record using INSERT, that the field "UserNumber" will automatically have the value generated in UserID.
Also, there must be a possibility to change it afterwards to something that is not UserID. i.e - I can't use a computed field.

Any Ideas?

Thanks a lot,
Roman.

 

[r937]

the only way you're going to get your scheme to work is if UserName is unique (i.e. a candidate key)

and since a computed column can't be used, you have to do it like this --

INSERT INTO daTable ( UserName ) VALUES ( 'Todd' )
UPDATE daTable SET UserNumber = UserID WHERE UserName = 'Todd'



r937.com | rudy.ca

 

[bborissov]

DECLARE @Temp Table (UserId int IDENTITY(1,1), UserName varchar(200), UserNumber varchar(200) DEFAULT CAST(SCOPE_IDENTITY() as varchar(200)))
INSERT INTO @Temp (UserName) VALUES ('Test1')
INSERT INTO @Temp (UserName) VALUES ('Test2')
INSERT INTO @Temp (UserName) VALUES ('Test3')
INSERT INTO @Temp (UserName) VALUES ('Test4')
INSERT INTO @Temp (UserName) VALUES ('Test5')
INSERT INTO @Temp (UserName) VALUES ('Test6')
INSERT INTO @Temp (UserName) VALUES ('Test7')


SELECT * from @Temp

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[BomberMan2K]

WOW. This works like a charm Thanks a lot.

But how can I update an existing table to do this? Using Server Management studio?

Tried to put this - CAST(SCOPE_IDENTITY() as varchar(200)) in the "default value or binding" parameter for the field in server management. doesn't work

Roman.

 

[r937]

nice one, boris

r937.com | rudy.ca

 

[ESquared]

Borisov,

Your solution is clever and I will remember it, but it doesn't work.

When I try this code I get a NULL UserNumber for UserName Test1 and all other rows have a UserNumber one less than the UserID. And when I try a multi-row insert, all the rows get the same UserID value.

A trigger sounds like the animal needed here to solve the problem.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[BomberMan2K]

Yeah, you're right. The code has some problems, but is the best idea so far. maybe it can be "fixed" to make it work?
Or any other ideas to solve this problem?

What approach can be used for the trigger?
My idea is to use the trigger each time a record is added, so it goes to the table and updates the UserNumber according to the UserID.
But it complicates the whole process, which I guess can be much easier.

Ideas for simplification?

 

[r937]

Ideas for simplification?

INSERT INTO daTable ( UserName ) VALUES ( 'Todd' )
UPDATE daTable SET UserNumber = UserID WHERE UserName = 'Todd'




r937.com | rudy.ca

 

[bborissov]

When I tested this, maybe before that I ran something else that have IDENTITY field. That is why I got the correct result in my result set. If I copy the coe in new query window I got the same result as ESquared. DAMN!
I thought about something like:

DECLARE @Temp Table (UserId int IDENTITY(1,1),
                     UserName varchar(200),
                     UserNumber varchar(200) DEFAULT CAST(ISNULL(SCOPE_IDENTITY(),0)+1 as varchar(200)))
INSERT INTO @Temp (UserName) VALUES ('Test1')
INSERT INTO @Temp (UserName) VALUES ('Test2')
INSERT INTO @Temp (UserName) VALUES ('Test3')
INSERT INTO @Temp (UserName) VALUES ('Test4')
INSERT INTO @Temp (UserName) VALUES ('Test5')
INSERT INTO @Temp (UserName) VALUES ('Test6')
INSERT INTO @Temp (UserName) VALUES ('Test7')


SELECT * from @Temp

But that wouldn't work also, just because the assumption that the first record has ID value of 1 is not right. Check this:

DECLARE @Temp Table (UserId int IDENTITY(75,1),
                     UserName varchar(200),
                     UserNumber varchar(200) DEFAULT CAST(ISNULL(SCOPE_IDENTITY(),0)+1 as varchar(200)))
INSERT INTO @Temp (UserName) VALUES ('Test1')
INSERT INTO @Temp (UserName) VALUES ('Test2')
INSERT INTO @Temp (UserName) VALUES ('Test3')
INSERT INTO @Temp (UserName) VALUES ('Test4')
INSERT INTO @Temp (UserName) VALUES ('Test5')
INSERT INTO @Temp (UserName) VALUES ('Test6')
INSERT INTO @Temp (UserName) VALUES ('Test7')


SELECT * from @Temp

and you will saw what I mean.


So I am with ESquared. Make a trigger for INSERT:

CREATE TRIGGER trg_Test
   ON  dbo.YourTable
   FOR INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE YourTable SET UserNumber = CAST(Inserted.UserId as varchar(20))
           FROM YourTable
            INNER JOIN Inserted ON YourTable.UserId = Inserted.UserId
END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[ESquared]

DECLARE @Temp Table (
   UserId int IDENTITY(1,1),
   UserName varchar(200),
   NewUserNumber varchar(20) NULL,
   UserNumber AS IsNull(NewUserNumber, Convert(varchar(11), UserID))
)

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[BomberMan2K]

Hmm.. interesting.
But why do you need NewUserNumber? it only holds null value.

 

[ESquared]

no it doesn't. It holds the overriding user number value. Then all code uses the UserNumber calculated column and doesn't care what's going on with UserID vs. NewUserNumber.

You can even put an index on the calculated column and make it materialize, so calculations are done at update and insert and queries are fast.

For what it's worth I should have used Coalesce instead of IsNull... trying to break that old habit.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]