Member Avatar for 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.

  • 2 Contributors
  • 2 Replies
  • 132 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by kralco626

Dani AI

Generated

’s suggestion to use a window function is the right, set-based approach and it’s good to see confirmed it worked. The notes below expand on practical concerns that often come up when you turn a simple example into production-ready SQL.

Implementing it robustly

  • Generate the per-group sequence values in a single SELECT using a window function, making the ORDER BY clause explicit so numbering is deterministic.
  • If table B may already contain rows for some IDs, compute the current max sequence per ID and add that as an offset to the generated numbers before inserting.
  • Materialize the result into a staging table (temporary or table variable) to validate before the final INSERT; this keeps the operation set-based and easy to test.

Production tips and gotchas

  • Ensure the ORDER BY used for numbering is stable and unique (include a tie-breaker) so results don’t change between runs.
  • Add supporting indexes on the grouping and ordering columns to reduce sorting and improve performance for large data sets.
  • For very large loads, insert in batches to limit log growth and locking. Wrap each batch in a transaction sized for recovery/rollback needs.
  • Concurrency: if multiple sessions might append sequences for the same ID concurrently, use a deliberate serialization strategy (for example, application locks via sp_getapplock or a brief escalation to a locking isolation) or rely on a unique PK on (ID, SEQ) and handle conflicts.

Validation and references

Recommended Answers

All 2 Replies

Member Avatar for 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!

commented: AWESOME!!!! +3
Member Avatar for kralco626

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.