大家好, 在我的数据库中我有这三张桌子 1.商店2.Products 3.Parts 他们的结构如下: 商店----产品----零件 商店 ------- --------- StoreId,StoreName 产品 ------------- --- ProductId,StoreId,ProductName 零件 ---------------- PartId,ProductId,PartName 现在,在我的应用程序中我想实现批量复制操作所以 用户可以复制从一个商店到另一个商店的产品,当一个 产品被复制到新商店时; 它的所有部分也应该复制。 实际上我需要一种方法在Products表中插入Product项目和 同步将它的部分复制到Parts表中并重复此步骤 直到所有的产品都被复制了。 如果没有游标或循环,我怎么能这样做? 谢谢
解决方案Khafancoder写道: 我的数据库中的我有这三个表 1.商店2.Products 3.Parts 他们的结构如下: 商店----产品展示----零件 商店 ---------------- StoreId,StoreName 产品 ---------------- ProductId,StoreId,ProductName 零件 ---------------- PartId,ProductId,PartName 现在,在我的应用程序中我想实现批量复制操作所以 用户可以从一个商店复制产品另一个产品,当一个 产品复制到新店时; 它的所有零件也应该复制。 实际上我需要一种方法在Products表中插入Product项目和 同步将它的部分复制到Parts表中并重复此步骤eps 直到复制完所有产品。 如果没有游标或循环,我怎么能这样做?
为什么你需要这样做?您似乎只需要 来执行以下操作: 插入产品n(ProductId,StoreId,ProductName) 选择o.ProductId,@ NewStoreId,o.ProductName 来自产品o 其中o.StoreId = @OldStoreId
那么零件记录呢?他们也需要复制! 5月24日晚上7点10分,Ed Murphy< emurph ... @ socal.rrwrote:
Khafancoder写道: 我的数据库中的我有这三个表1.商店2.Products 3.Parts
他们的结构类似于:
商店----产品----零件
商店 ---------------- StoreId,StoreName
产品 ---------------- ProductId,StoreId,ProductName
零件 ---------------- PartId,ProductId,PartName 现在,在我的应用程序中,我想实现批量复制操作,所以 用户可以将产品从一个商店复制到另一个商店,当一个 产品复制到新店;
它的所有部分也应该复制。 其实我需要一种方法来插入一个Product项目在Products表格和 中同步将它的部分复制到Parts表中并重复这个步骤 直到复制了所有产品。
如果没有游标或循环,我怎么能这样做?
为什么你需要这样做?您似乎只需要 来执行以下操作: 插入产品n(ProductId,StoreId,ProductName) 选择o.ProductId,@ NewStoreId,o.ProductName 来自产品o 其中o.StoreId = @ OldStoreId-隐藏引用文字 - - 显示引用的文字 -
Ed Murphy(em*******@socal.rr)写道: 为什么你需要这样做?您似乎只需要 来执行以下操作: 插入产品n(ProductId,StoreId,ProductName) 选择o.ProductId,@ NewStoreId,o.ProductName 来自产品o 其中o.StoreId = @OldStoreId
我怀疑Khafancoder的问题可能是ProductId是一个 唯一密钥而不是StoreID的密钥。后者可能或者 可能不是更好的设计,具体取决于业务要求。 我想Khafancode会告诉我们它不是。我希望他还能 给我们更多关于他的桌子的信息:哪些是钥匙, 如果有任何IDENTITY栏。以及他正在使用哪个版本的SQL Server 。 - Erland Sommarskog,SQL Server MVP, es **** @ sommarskog.se SQL Server 2005联机丛书 www.microsoft。 com / technet / pro ... ads / books.mspx SQL Server 2000联机丛书 www.microsoft/sql/prodinf...ons/books.mspx
Hi guys, in my db i have these three tables 1.Stores 2.Products 3.Parts their structure is something like : Stores ----Products ----Parts Stores ---------------- StoreId, StoreName Products ---------------- ProductId, StoreId, ProductName Parts ---------------- PartId, ProductId, PartName now, in my application i wanna to implement a bulk-copy operation so user can copy products from one store to another one and when a product copied to new store; all of it''s parts should copy too. in fact i need a method to insert a Product item in Products table and synchronously copy it''s parts into Parts table and repeat this steps until all of proucts copied. how can i do that without cursors or loops ? Thanks
解决方案 Khafancoder wrote: in my db i have these three tables 1.Stores 2.Products 3.Parts their structure is something like : Stores ----Products ----Parts Stores ---------------- StoreId, StoreName Products ---------------- ProductId, StoreId, ProductName Parts ---------------- PartId, ProductId, PartName now, in my application i wanna to implement a bulk-copy operation so user can copy products from one store to another one and when a product copied to new store; all of it''s parts should copy too. in fact i need a method to insert a Product item in Products table and synchronously copy it''s parts into Parts table and repeat this steps until all of proucts copied. how can i do that without cursors or loops ?Why do you need to do that at all? It seems like you simply need to do the following: insert into Products n (ProductId, StoreId, ProductName) select o.ProductId, @NewStoreId, o.ProductName from Products o where o.StoreId = @OldStoreId
So what about parts records ? they need to copied too ! On May 24, 7:10 pm, Ed Murphy <emurph...@socal.rrwrote: Khafancoder wrote: in my db i have these three tables1.Stores 2.Products 3.Parts
their structure is something like :
Stores ----Products ----Parts
Stores ---------------- StoreId, StoreName
Products ---------------- ProductId, StoreId, ProductName
Parts ---------------- PartId, ProductId, PartName
now, in my application i wanna to implement a bulk-copy operation so user can copy products from one store to another one and when a product copied to new store;
all of it''s parts should copy too. in fact i need a method to insert a Product item in Products table and synchronously copy it''s parts into Parts table and repeat this steps until all of proucts copied.
how can i do that without cursors or loops ?
Why do you need to do that at all? It seems like you simply need to do the following: insert into Products n (ProductId, StoreId, ProductName) select o.ProductId, @NewStoreId, o.ProductName from Products o where o.StoreId = @OldStoreId- Hide quoted text - - Show quoted text -
Ed Murphy (em*******@socal.rr) writes: Why do you need to do that at all? It seems like you simply need to do the following: insert into Products n (ProductId, StoreId, ProductName) select o.ProductId, @NewStoreId, o.ProductName from Products o where o.StoreId = @OldStoreId
I suspect that Khafancoder''s problem may be that ProductId is a unique key and not a key together with StoreID. The latter may or may not be a better design depending on the business requirements. I guess Khafancode will tell us it is not. I hope then he also gives us more information about his tables: which are the keys, if there are any IDENTITY column. And also which version of SQL Server he is using. -- Erland Sommarskog, SQL Server MVP, es****@sommarskog.se Books Online for SQL Server 2005 at www.microsoft/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at www.microsoft/sql/prodinf...ons/books.mspx
更多推荐
同步批量复制到两个表中
发布评论