|
|
Posted by Khafancoder on 06/01/07 21:58
Thanks to everyone :)
On Jun 1, 12:41 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Khafancoder (khafanco...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|