如何更好地复制 SQL Server 中的一组数据 [英] How to better duplicate a set of data in SQL Server
问题描述
我有几个相关的表，我希望能够在更新引用时复制某些行.
I have several related tables that I want to be able to duplicate some of the rows while updating the references.
我想复制 Table1 中的一行，以及它所有来自 Table2 和 Table3 的相关行，我正在尝试找出一种有效的方法来避免遍历行.
I want to duplicate a row in Table1, and all of it's related rows from Table2 and Table3, and I'm trying to figure out an efficient way of doing it short of iterating through rows.
例如，我有一张篮子表:
So for example, I have a table of baskets:
+----------+---------------+
| BasketId | BasketName |
+----------+---------------+
| 1 | Home Basket |
| 2 | Office Basket |
+----------+---------------+
每个篮子都有水果:
+---------+----------+-----------+
| FruitId | BasketId | FruitName |
+---------+----------+-----------+
| 1 | 1 | Apple |
| 2 | 1 | Orange |
| 3 | 2 | Mango |
| 4 | 2 | Pear |
+---------+----------+-----------+
而且每种水果都有一些属性:
And each fruit has some properties:
+------------+---------+--------------+
| PropertyId | FruitId | PropertyText |
+------------+---------+--------------+
| 1 | 2 | Is juicy |
| 2 | 2 | Hard to peel |
| 3 | 1 | Is red |
+------------+---------+--------------+
在此示例中，我的属性特定于单个水果行，这些苹果"属性不是所有篮子中所有苹果的属性，而是特定篮子中特定苹果的属性.
For this example, my properties are specific to the individual fruit row, these "apple" properties aren't properties of all apples in all baskets, just for that specific apple in that specific basket.
我想做的是复制一个篮子.所以给定篮子 1，我想创建一个新篮子，复制它包含的水果行，并复制指向这些水果的属性.最后我希望有这样的数据:
What I want to do is duplicate a basket. So given basket 1, I want to create a new basket, duplicate the fruit rows it contains, and duplicate the properties pointing to those fruits. In the end I'm hoping to have data like so:
+----------+---------------+
| BasketId | BasketName |
+----------+---------------+
| 1 | Home Basket |
| 2 | Office Basket |
| 3 | Friends Basket|
+----------+---------------+
+---------+----------+-----------+
| FruitId | BasketId | FruitName |
+---------+----------+-----------+
| 1 | 1 | Apple |
| 2 | 1 | Orange |
| 3 | 2 | Mango |
| 4 | 2 | Pear |
| 5 | 3 | Apple |
| 6 | 3 | Orange |
+---------+----------+-----------+
+------------+---------+--------------+
| PropertyId | FruitId | PropertyText |
+------------+---------+--------------+
| 1 | 2 | Is juicy |
| 2 | 2 | Hard to peel |
| 3 | 1 | Is red |
| 4 | 6 | Is juicy |
| 5 | 6 | Hard to peel |
| 6 | 5 | Is red |
+------------+---------+--------------+
复制篮子和它的水果非常简单，但在我看来复制水果的属性会导致遍历行，我希望 TSQL 中有更好的解决方案.
Duplicating the basket and it's fruit were pretty straightforward, but duplicating the properties of the fruit seems to me to lead to iterating over rows and I'm hoping there's a better solution in TSQL.
有什么想法吗?
推荐答案
为什么不加入 FruitName 以获取包含新旧 FruitId 的表?考虑到信息会同时添加......这可能不是最好的选择，但您不会使用任何周期.
Why dont you join on the FruitName to get a table with old and new FruitId's? Considering information would be added at the same time.... it may not be the best option but you wont be using any cycles.
INSERT INTO BASKET(BASKETNAME)
VALUES ('COPY BASKET')
DECLARE @iBasketId int
SET @iBasketId = @@SCOPE_IDENTITY;
insert into Fruit (BasketId, FruitName)
select @iBasketId, FruitName
from Fruit
where BasketId = @originalBasket
declare @tabFruit table (originalFruitId int, newFruitId int)
insert into @tabFruit (originalFruitId, newFruitId)
select o.FruitId, n.FruitId
from (SELECT FruitId, FruitName from Fruit where BasketId = @originalBasket) as o
join (SELECT FruitId, FruitName from Fruit where BasketId = @newBasket) as n
on o.FruitName = n.FruitName
insert into Property (FruitId, PropertyText)
select NewFruitId, PropertyText
from Fruit f join @tabFruit t on t.originalFruitId = f.FruitId
这篇关于如何更好地复制 SQL Server 中的一组数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
