|
|
Posted by Khafancoder on 05/30/07 23:07
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 ?
Thnx
On May 31, 1:31 am, Khafancoder <khafanco...@gmail.com> wrote:
> Thanks for reply,
>
> i think it won't be a right logic for creating new id s ; or may be i
> didn't understand the algorithm correctly
>
> > SELECT @maxprodid = MAX(ProductId) FROM Products
> > SELECT @minoldprodid = MIN(ProductId)
> > FROM Stores
> > WHERE StoredId = @oldstoreid
>
> > INSERT Products (ProductId, StoreId, ProductName, ProductDescription)
> > SELECT @maxprodid + 1 + @minprodid - ProductId, @newstoreid,
> > ProductName, ProductDescription
> > FROM Products
> > WHERE StoreId = @oldstoreid
>
> Suppose Product Ids are from 1 to 100
>
> so
> @maxprodid=100
> @minoldprodid = 1
>
> and so, if we gonna to copy a record with id=50 it will be :
>
> @maxprodid + 1 + @minprodid - ProductId = 100 + 1 + 1 - 50
> = 52
>
> and 52 is id of an existing product record
>
> Thanks in Advance
>
> On May 31, 12:47 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
>
>
>
> > Khafancoder (khafanco...@gmail.com) writes:
> > > i finally decided to disable identity insertion and do the copy
> > > operation by using temporary Map tables which maps Old Ids and New Ids
> > > so :
> > > --------------------------------------------------------------------
> > > CREATE TABLE #MapProducts (SourceProductId bigint, DestProductId
> > > bigint)
>
> > > INSERT INTO #MapProducts (SourceProductId, DestProductId)
> > > SELECT ProductId, CASE WHEN
> > > ((SELECT COUNT(*) FROM #MapProducts) > 0) THEN (SELECT
> > > MAX(DestProductId) + 1 FROM #MapProducts)
> > > ELSE (SELECT MAX(ProductId) + 1 FROM Products) END
> > > FROM Products WHERE StoreId=@SourceStoreId
> > > --------------------------------------------------------------------
>
> > > but another problem, this line :
> > > --------------------------------------------------------------------
> > > CASE WHEN ((SELECT COUNT(*) FROM #MapProducts) > 0) THEN (SELECT
> > > MAX(DestProductId) + 1 FROM #MapProducts)
> > > --------------------------------------------------------------------
> > > won't be executed because sql engine calculate COUNT before do the
> > > insert operation.
>
> > > how could i solve that ?
> > > is it possible to force INSERT command to calculate COUNT after
> > > inserting *each record* ?
>
> > That won't fly, as you have noticed. You need to compute the new
> > id:s from what is given before you start inserting. And this should
> > not be impossible. Please review this piece of code, which is the
> > same I posted a couple of days back:
>
> > BEGIN TRANSACTION
>
> > SELECT @newstoreid = colaesce(MAX(storeid), 0) + 1
> > FROM Stores WITH (UPDLOCK)
>
> > INSERT Stores (StoredId, StoreName)
> > VALUES(@newstoreid, @newstorename)
>
> > SELECT @maxprodid = MAX(ProductId) FROM Products
> > SELECT @minoldprodid = MIN(ProductId)
> > FROM Stores
> > WHERE StoredId = @oldstoreid
>
> > INSERT Products (ProductId, StoreId, ProductName, ProductDescription)
> > SELECT @maxprodid + 1 + @minprodid - ProductId, @newstoreid,
> > ProductName, ProductDescription
> > FROM Products
> > WHERE StoreId = @oldstoreid
>
> > SELECT @maxpartid = MAX(PartId) FROM Parts
> > SELECT @minpartid = MIN(Pa.PartId)
> > FROM Parts Pa
> > JOIN Products Pr ON Pa.ProductId = Pr.ProductID
> > WHERE Pr.StoreID = @oldstoreid
>
> > INSERT Parts(PartId, ProductId, Partname)
> > SELECT @maxpartid + 1 + @minpartid - Pa.PartId,
> > @maxprodid + 1 + @minprodid - Pr.ProductId, Pa.Partname
> > FROM Parts Pa
> > JOIN Products Pr ON Pa.ProductId = Pr.ProductID
> > WHERE Pr.StoreID = @oldstoreid
>
> > COMMIT TRANSACTION
>
> > --
> > 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-Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Navigation:
[Reply to this message]
|