April 3, 2002 at 1:19 pm
Hi, we are porting our software from oracle to sql server. In one of our tables in oracle db, we have 2 sequences. How can I achieve the same thing in sql server? I can't create two identities in one table in sql server.
But since a lot of our frontend code is written based on two sequences in that table, and we don't have time to change the frontend code. What can I do in this situation at sql server side.
This table needs to be inserted and selected quite frequently.
Thanks.
Abby Zhang
April 3, 2002 at 2:24 pm
You can only have one identity col. Could you just copy the ident val to the other col when it's inserted, would that help? What do you use to seed the second col?
Andy
April 3, 2002 at 3:20 pm
Thanks for the reply. The two columns' values normally are not the same. I think the easiest way to do is to let the two columns share one identity.
My table is like this:
parent_issue_id, issue_id, f1, f2, f3
( Don't ask me why parent_issue_id was sequence in the oracle version )
Can I do this:
set issue_id column as identity.
then, for the parent_issue_id value,
declare @mv_parentid
set @mv_parentid = IDENT_CURRENT ('mv_table' )
then do an insert:
insert into mv_table(parent_issue_id, f1, f2, f3 )
values ( @mv_parentid, @f1, @f2, f3)
The issue_id column will be automatically set as a value larger then @mv_aprentid, is that true?
Thanks.
April 3, 2002 at 5:17 pm
Yes, that should be right. You essentially are setting the parentid = to the last records issue_id, but due to other transactions it could possible not happen that way (I have never tested).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 3, 2002 at 5:46 pm
It works for me. Thank you very much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply