|
|
Posted by Khafancoder on 05/26/07 07:10
Thanks,
> 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?
because copied products or parts info may needed to be changed by each
owner.
so, i will consider disabling identity insertion.
Thanks for answers
On May 26, 12:57 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Khafancoder (khafanco...@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, 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
Navigation:
[Reply to this message]
|