|
|
Posted by Erland Sommarskog on 05/31/07 21:41
Khafancoder (khafancoder@gmail.com) writes:
> i solve the problem by using:
>
> SELECT @maxprodid = MAX(ProductId) FROM Products
>
> INSERT Products (ProductId, StoreId, ProductName,
> ProductDescription)
> SELECT @maxprodid + ROW_NUMBER() OVER (ORDER BY ProductId) ,
> @newstoreid,
> ProductName, ProductDescription
> FROM Products
> WHERE StoreId = @oldstoreid
>
>
> is it correct ?
Yes, that is a lot better solution that what I posted originally. If
we ignore the fact that it didn't work, I think I wrote it under the
assumption was you were using SQL 2000, and not had access to the
row-number function.
Fairly irrelevant discussion follows: The idea was that if you needed
to copy products 1, 7, 9, 13, and the first available ID was 101, you
would get ids 101, 107, 109 and 113 simply because this would be the easiest
way to solve it on SQL 2000. To get a contiguous series you would have
needed a temp table/table variable with an IDENTITY column.
More relevant final comment: the row_number() function is one of the
absolutely most important additions in SQL 2005.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|