|
|
Posted by Erland Sommarskog on 05/30/07 21:47
Khafancoder (khafancoder@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, 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]
|