Help with NOT using cursors

[kralco626]

I am trying to do this with sql code without using a cursor.

I have table A with two cols and no PK
ID
Data

I have table B with three cols the first two make up the PK
ID PK
SEQ PK
Data

If I have the following data in Table A
1000 myData1
1000 myData2
1000 myData3
1001 myData4
1001 myData5
1002 myData6

I want this in table B

1000 1 myData1
1000 2 myData2
1000 3 myData3
1001 1 myData4
1001 2 myData5
1002 1 myData6

Without using cusors, I want to be able to do it in a set based manner.

Thanks for any help.

[sknake]

You want to use ROW_NUMBER() with OVER on the grouping columns. I don't have access to a SQL Server at the moment but you can see how to do this at:
http://www.4guysfromrolla.com/webtech/010406-1.shtml

This is the example you will want to look at. Its roughly half way down the page:

SELECT c.Name, o.DateOrdered, tab.TotalOrderAmount, 
       ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY TotalOrderAmount DESC) AS BestCustomer
FROM vwTotalAmountBilledPerOrder AS tab 
       INNER JOIN Orders AS o ON 
           o.OrderID = tab.OrderID 
       INNER JOIN Customers AS c ON 
           c.CustomerID = o.CustomerID

If you're still having trouble I will help you more this evening. Good luck!

[kralco626]

Awesome!!!!!!! it worked perfectly exectly what i wanted!!!! your awesome!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!