Inserting record numbers - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Inserting record numbers

Author

Topic

IKU
Starting Member

11 Posts

Posted - 2009-07-08 : 16:16:24

Here is my problem:
I'm trying to insert data into a table which has primary key of 3 columns. Data that I'm trying to insert only have two columns, so that third column should be a kind of identity, but it would have to reset on change of first two columns.
Example:
This is the current state of data
col1 col2
---------- -----
22-15-09 0003
22-15-09 0003
22-15-09 0003
22-15-09 0005
22-15-09 0006
22-15-09 0006
22-15-09 0006
22-15-09 0006
22-15-09 0006
22-15-09 0006

result should be:
col1 col2
---------- ----- col3
22-15-09 0003 1
22-15-09 0003 2
22-15-09 0003 3
22-15-09 0005 1
22-15-09 0006 2
22-15-09 0006 3
22-15-09 0006 4
22-15-09 0006 5
22-15-09 0006 6
22-15-09 0006 7

IKU
Starting Member

11 Posts

Posted - 2009-07-08 : 17:27:26

I've made an error;
result should be:
col1 col2
---------- ----- col3
22-15-09 0003 1
22-15-09 0003 2
22-15-09 0003 3
22-15-09 0005 1
22-15-09 0006 1
22-15-09 0006 2
22-15-09 0006 3
22-15-09 0006 4
22-15-09 0006 5
22-15-09 0006 6

sorry

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-09 : 01:51:31

[code]
if u dont have any identity column then try this
declare @tab table (col1 varchar(32), col2 varchar(32))
insert into @tab select
'22-15-09', '0003' union all select
'22-15-09', '0003' union all select
'22-15-09', '0003' union all select
'22-15-09', '0005' union all select
'22-15-09', '0006' union all select
'22-15-09', '0006' union all select
'22-15-09', '0006' union all select
'22-15-09', '0006' union all select
'22-15-09', '0006' union all select
'22-15-09', '0006'

drop table #temp

select identity(int,1,1)as id , * into #temp from @tab

select *,(select count(1) from #temp where col1 = t.col1 and col2 = t.col2 and id <=t.id)as col3
from #temp t
[/code]
if u have any identity column then dont use #temp table directly use ur table and inplace of id use identity column

IKU
Starting Member

11 Posts

Posted - 2009-07-09 : 02:28:27

Thank you, it seems to be working

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-10 : 01:16:00

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources