Posted by Khafancoder on 05/30/07 22:31

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 -

[Back to original message]