Use SQL to clone data in two tables that have a 1-1 relationship in each table
- by AmoebaMan17
Using MS SQL 2005,
Table 1
ID | T1Value | T2ID | GroupID
----------------------------------
1 | a | 10 | 1
2 | b | 11 | 1
3 | c | 12 | 1
4 | a | 22 | 2
Table 2
ID | T2Value
----------------
10 | H
11 | J
12 | K
22 | H
I want to clone the data for GroupID == 1 into a new GroupID so that I result with the following:
Table 1
ID | T1Value | T2ID | GroupID
----------------------------------
1 | a | 10 | 1
2 | b | 11 | 1
3 | c | 12 | 1
4 | a | 22 | 2
5 | a | 23 | 3
6 | b | 24 | 3
7 | c | 25 | 3
Table 2
ID | T2Value
----------------
10 | H
11 | J
12 | K
22 | H
23 | H
24 | J
25 | K
I've found some SQL clone patterns that allow me to clone data in the same table well... but as I start to deal with cloning data in two tables at the same time and then linking up the new rows correctly... that's just not something I feel like I have a good grasp of.
I thought I could do some self-joins to deal with this, but I am worried in the cases where the non-key fields have the same data in multiple rows.
