From: phdate on 12 Dec 2009 11:18
Erland Sommarskog wrote:
> Say that you need to insert a number of rows in a table, with each
> row being assigned a unique, sequential, id, the typical construct is:
> Whether that fits in the context you are working, I don't know.
> Furthermore, this solution requires SQL 2005 or higher.
Unfortunately not. I use this technique elsewhere but what I need is
the ability to generate such numbers one at a time. And sometimes, I
need 2 or 3 of these at a time, depending on the results of the formulas
I use in the package.
From: phdate on 12 Dec 2009 11:26
Plamen Ratchev wrote:
> Here is another example:
Thanks but beyond the details of the author's specific case, I think it
is identical to the one I posted.
From: Erland Sommarskog on 12 Dec 2009 15:36
phdate (drscrypt(a)gmail.com) writes:
> Unfortunately not. I use this technique elsewhere but what I need is
> the ability to generate such numbers one at a time. And sometimes, I
> need 2 or 3 of these at a time, depending on the results of the formulas
> I use in the package.
Instead of row_number, you can use a correlated subquery with COUNT(*):
SELECT O.OrderID, O.CustomerID, O.OrderDate,
FROM Orders O2
WHERE O2.CustomerID = O.CustomerID
AND O2.OrderID <= O.OrderID) AS OrdnoForCustomer
FROM Orders O
ORDER BY O.CustomerID, O.OrderID
The performance for larger result sets is awful, but for 2-3 it should
not be a problem.
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx