identity - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

identity

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-19 : 09:23:16

jumoke writes "I have 2 tables ownerDet_tb with ownerDet_id and ownerDet_name

ownerDet_id ownerDet_name
1 INDIVIDUAL
2 COMPANY
3 JOINT:COUPLE
4 JOINT:IND_COMPANY
5 EMBASSY
6 LOCAL GOVERNMENT
7 STATE GOVERNMENT
8 FEDERAL GOVERNMENT

The Second table is the owner table which will have ownerDet_id.

Hi based on the table above ,I am to autogenerate owner_id based in ownerDet_Desc.
E.g if ownerDet_Desc is embassy……….the owner_id shld be say emb001
And individual is ind001 etc etc tec
You get idea?"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-19 : 09:37:26

[code]
declare @ownerDet_tb table
(
ownerDet_id int,
ownerDet_name varchar(20)
)
insert into @ownerDet_tb
select 1, 'INDIVIDUAL' union all
select 2, 'COMPANY' union all
select 3, 'JOINT:COUPLE' union all
select 4, 'JOINT:IND_COMPANY' union all
select 5, 'EMBASSY' union all
select 6, 'LOCAL GOVERNMENT' union all
select 7, 'STATE GOVERNMENT' union all
select 8, 'FEDERAL GOVERNMENT'

declare @owner table
(
owner_id varchar(10),
ownerDet_id int
)

insert into @owner
select owner_id = lower(left(ownerDet_name, 3)) +
right('000' + convert(varchar(3), (select count(*) from @ownerDet_tb x
where left(x.ownerDet_name, 3) = left(o.ownerDet_name, 3)
and x.ownerDet_name <= o.ownerDet_name)), 3),
ownerDet_id
from @ownerDet_tb o
order by left(ownerDet_name, 3)

select * from @owner
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources