Posted by Khafancoder on 05/29/07 22:04

Hi again !

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* ?

Thanks

[Back to original message]