|
|
Posted by Erland Sommarskog on 05/25/07 21:57
Khafancoder (khafancoder@gmail.com) writes:
> Thanx,
> here is the db schema :
> http://i12.tinypic.com/4v5qfbb.gif
>
> (PrimaryKey fields are identity too)
> (SQL2005)
Permit me to remark that the designe does not look good to me. I don't
see why you would copy products and parts from one store to another,
and give them new ids. If you have a product "Widgets", would it not
be the same product in each store?
Had you not used the IDENTITY property, it would have been an easy thing:
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
Since you use IDENTITY, things become far more cumbersome, and you are
probably best off changing the design so that you are at least not
using identity at all. (I would also prefer a key in Parts that
has ProductId as the first column.)
You could use SET IDENTITY_INSERT and then use the above, but that
requires permissions that a plan user may not have. And it would really
serve to stress that you are using IDENTITY when you shouldn't.
Had PartName and ProductName been known to unique, you could have used
the new OUTPUT clause in SQL 2005, but since they permit NULL, it
does not seem a good idea to use them.
insert into Products n (ProductId, StoreId, ProductName)
select o.ProductId, @NewStoreId, o.ProductName
from Products o
where o.StoreId = @OldStoreId
--
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]
|