同步批量复制到两个表中 [英] Synchronous Bulk-Copy into two table
问题描述
大家好，
在我的数据库中我有这三张桌子
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
< blockquote>那么零件记录呢？他们也需要复制！
5月24日晚上7点10分，Ed Murphy< emurph ... @ socal.rr.comwrote：
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.com）写道：
为什么你需要这样做？您似乎只需要
来执行以下操作：
插入产品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联机丛书
http://www.microsoft。 com / technet / pro ... ads / books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/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.rr.comwrote: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- Hide quoted text -
- Show quoted text -
Ed Murphy (em*******@socal.rr.com) 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 = @OldStoreIdI 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
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
这篇关于同步批量复制到两个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
