How do I duplicate rows with new ID? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How do I duplicate rows with new ID?

Author

Topic

azidops
Starting Member

2 Posts

Posted - 2013-12-08 : 15:41:11

I have difficulties solving a problem. I'll try to explain the problem the best I can.

I got two tables: Table A and Table B. Due to some poor programming the tables are not normalized and this is what's causing my problem.

Table A:
- Field A ID (Unique)
- TableB.FieldA (Foreign key)
- Field X
- Field Y
- Field z
etc
(Field A and Table B ID is a joint primary key)

Table B:
- Field A (Old unique key)
- Field B (Foreign key)
- Field C (new unique key)
(Field A and Field B ID is a joint primary key)

Due to poor programming Field A and Field B were used as a joint primary key, but conceptually only Field A should be a primary key. Because of that the it sometimes happen that the same key in field A appears multiple times so I had to make a new primary key in Field C with unique keys.

Table A uses TableB.FieldA as a foreign key. Multiple rows in Table A can have the same Foreign Key. But because I had to make a new unique key in Table B (FieldC), those data in Table A with the same foreign key also belongs to the new keys made in TableB.FieldC.

Example:

quote:

Table B

Field A Field B Field C
------- ------- -------
1..........700.......9000
1..........701.......9001 (ID 1 appears 2 times in Field A. Not allowed)
2..........700.......9002
3..........700.......9003

Table A
Field A Field B (from TableB.FieldA) Field C
------- ---------------------------- ---------
1..........1........................................Some text
2..........1........................................Some text
3..........1........................................Some text
4..........2........................................Some text
5..........2........................................Some text
6..........3........................................Some text
7..........3........................................Some text
- 1 -

As you see in the first quote ID 1 appears 3 times as Foreign key in TableA.FieldB. In Table B you see that ID 1 got two new ID's (9000 and 9001). That means that for every row in Table A with the old ID 1 I have to insert two new rows for ID 9000 and 9001. And that's my problem. How do I duplicate the rows in Table A based on the number of new ID's made in TableB.FieldC?

Don't be afraid ask question to clarify the problem if needed. It was difficult to write down the problem so I don't know if I got the problem well explained. I want the result to be like in the code-quote below:

quote:

Table A
Field A Field B (from TableB.FieldA) Field C
------- ---------------------------- ---------
1..........1........................................Some text
2..........1........................................Some text
3..........1........................................Some text
4..........9001...................................Some text
5..........9001...................................Some text
6..........9001...................................Some text
7..........2........................................Some text
8..........2........................................Some text
9..........3........................................Some text
10.........3........................................Some text

Rows in bold are new rows

Code


--Create and populate temp tables
if object_id('tempdb..#tableA', 'U') is not null
	drop table #tableA

if object_id('tempdb..#tableB', 'U') is not null
	drop table #tableB

create table #tableB (
	FieldA int not null
	,FieldB int not null
	,FieldC int
	)

alter table #tableB add constraint pk_tableA primary key clustered (
	FieldA
	,FieldB
	)

create table #tableA (
	FieldA int not null
	,FieldB int not null
	,FieldC varchar(100) null
	)

alter table #tableA add constraint pk_tableB primary key clustered (FieldA)

--Put some data in there.
insert #tableB(FieldA, FieldB, FieldC) 
select '1','700','9000' union all
select '1','701','9001' union all
select '2','700','9002' union all
select '3','700','9003' 

insert #tableA(FieldA, FieldB, FieldC)
select '1','1','some text' union all
select '2','1','some text' union all
select '3','1','some text' union all
select '4','2','some text' union all
select '5','2','some text' union all
select '6','3','some text' union all
select '7','3','some text'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-09 : 01:40:57

sounds like this to me


INSERT INTO #tableA (FieldA,FieldB,FieldC)
SELECT (SELECT MAX(FieldA) FROm #tableA) + ROW_NUMBER() OVER (ORDER BY b.FieldC),
b.FieldC,a.FieldC
FROM #tableA a
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY FieldA ORDER BY FieldB) AS Rn,FieldA,FieldB,FieldC FROM #tableB)b
ON b.FieldA = a.FieldB
AND b.RN > 1 

SELECT * FROM #TableA



output
-------------------------
FieldA	FieldB	FieldC
--------------------------
1	1	some text
2	1	some text
3	1	some text
4	2	some text
5	2	some text
6	3	some text
7	3	some text
8	9001	some text
9	9001	some text
10	9001	some text

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

azidops
Starting Member

2 Posts

Posted - 2013-12-09 : 07:45:34

Yes, that seems to do the trick. Many thanks. With a few alterations this is what I was looking for


INSERT INTO #TableA (fielda, FieldB,FieldC)
SELECT (select max(fieldA) from #tableA)+ ROW_NUMBER() OVER (ORDER BY a.FieldA) , b.FieldC, a.FieldC
FROM #TableA a
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY FieldA ORDER BY FieldB) AS Rn,FieldA,FieldB,FieldC FROM #TableB)b
ON b.FieldA = a.FieldB
AND b.RN > 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-09 : 07:50:41

cool
glad that I could help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources