generating seq no with in a main column

[sidd]

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

[David Portas]

Re: generating seq no with in a main column

Your 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
--

[Dan Guzman]

Re: generating seq no with in a main column

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 #SequenceNumber s
(
Sequence_number _with_in_LoanID int NOT NULL
PRIMARY KEY
)
WHILE @MaxCount > 0
BEGIN
INSERT INTO #SequenceNumber s VALUES(@MaxCoun t)
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 SourceTableCoun ts
JOIN #SequenceNumber s ON
#SequenceNumber s.Sequence_numb er_with_in_Loan ID <=
SourceTableCoun ts.loanCount
DROP TABLE #SequenceNumber s
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"sidd" <siddharthkhare @hotmail.com> wrote in message
news:af96a2d7.0 312270944.2dd0c a8f@posting.goo gle.com...[color=blue]
> 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[/color]

[--CELKO--]

Re: generating seq no with in a main column

>> 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.

[sidd]

Re: generating seq no with in a main column

Dan,
it works great!!
thanks
sidd