generating seq no with in a main column

Hi all,
please see if some one can suggest a better approach for this.
i need to move 5 million rows from one table to another.
LoanID is the only clumn in Source table
Source table structure
=========-->
==============================
loanID
1
1
2
2
2
3
4
=====================================
target or detination table should look like this
=========================================
loanID Sqeuence_number_with_in_LoanID
1 1
1 2
2 1
2 2
2 3
3 1
4 1
=========================================

this is what i have tried so far
1)there are about 4 & 1/2 million out of 5 million which will appear
only once (this is one tine load) in source table.
so if i do this
----
sleect loanID
from SourceTable
group by loanID
having count(*)=1

and then i will hard code Sqeuence_number_with_in_LoanID to 1 .because
i know they will only come once.this is set based operation so this is
fast.

2)now,problem is with remainder of the 1/2 million rows.
i had to use cursor for this which is very-very slow.
other option i was thinking of is to pull it in the front end
(.net)and do it there.i am hoping that will be faster.
Database i am working on is sql server 2000.
if some could suggest me some better approach of handling this
remainder
1/2 milion rows.
thanks in advance
-siddYour target table doesn't look very meaningful as you've described it. If
you're not recording any extra information on each row then what's the point
of populating the table with lots of redundant data? Much more efficient
just to add a count column:

CREATE TABLE TargetTable (loanid INTEGER PRIMARY KEY, loancount INTEGER NOT
NULL CHECK (loancount>0))

INSERT INTO TargetTable (loanid, loancount)
SELECT loanid, COUNT(*)
FROM SourceTable
GROUP BY loanid

--
David Portas
----
Please reply only to the newsgroup
--|||Below is one method.

--create table of sequence numbers
DECLARE @.MaxCount int
SELECT @.MaxCount = MAX(loanCount)
FROM(
SELECT COUNT(*) AS loanCount
FROM SourceTable
GROUP BY loanID
) AS loanCounts
CREATE TABLE #SequenceNumbers
(
Sequence_number_with_in_LoanID int NOT NULL
PRIMARY KEY
)
WHILE @.MaxCount > 0
BEGIN
INSERT INTO #SequenceNumbers VALUES(@.MaxCount)
SET @.MaxCount = @.MaxCount - 1
END

-- load target table
SELECT loanID, Sequence_number_with_in_LoanID
INTO TargetTable
FROM (
SELECT loanID, COUNT(*) AS loanCount
FROM SourceTable
GROUP BY loanID) AS SourceTableCounts
JOIN #SequenceNumbers ON
#SequenceNumbers.Sequence_number_with_in_LoanID <=
SourceTableCounts.loanCount
DROP TABLE #SequenceNumbers
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"sidd" <siddharthkhare@.hotmail.com> wrote in message
news:af96a2d7.0312270944.2dd0ca8f@.posting.google.c om...
> Hi all,
> please see if some one can suggest a better approach for this.
> i need to move 5 million rows from one table to another.
> LoanID is the only clumn in Source table
> Source table structure
> =========-->
> ==============================
> loanID
> 1
> 1
> 2
> 2
> 2
> 3
> 4
> =====================================
> target or detination table should look like this
> =========================================
> loanID Sqeuence_number_with_in_LoanID
> 1 1
> 1 2
> 2 1
> 2 2
> 2 3
> 3 1
> 4 1
> =========================================
> this is what i have tried so far
> 1)there are about 4 & 1/2 million out of 5 million which will appear
> only once (this is one tine load) in source table.
> so if i do this
> ----
> sleect loanID
> from SourceTable
> group by loanID
> having count(*)=1
> and then i will hard code Sqeuence_number_with_in_LoanID to 1 .because
> i know they will only come once.this is set based operation so this is
> fast.
> 2)now,problem is with remainder of the 1/2 million rows.
> i had to use cursor for this which is very-very slow.
> other option i was thinking of is to pull it in the front end
> (.net)and do it there.i am hoping that will be faster.
> Database i am working on is sql server 2000.
> if some could suggest me some better approach of handling this
> remainder
> 1/2 milion rows.
> thanks in advance
> -sidd|||>> LoanID is the only column in Source table <<

Then this is not a table by definition. Please post DDL in the
future, so we do not have to make guesses. Build a table of
sequential numbers; this is a standard SQL programming trick.

CREATE TABLE Foobar (loan_id, seq_nbr)
SELECT DISTINCT S1.loan_id, seq
FROM Source S1, Sequence
WHERE seq <= (SELECT COUNT(*)
FROM SOurce AS S2
WHERE S1.loan_id = S2.loan_id);

But this is not a good design.|||Dan,
it works great!!
thanks
siddsql