SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How do I duplicate rows with new ID?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

azidops
Starting Member

Norway
2 Posts

Posted - 12/08/2013 :  15:41:11  Show Profile  Reply with Quote
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'

Edited by - azidops on 12/09/2013 05:13:41

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/09/2013 :  01:40:57  Show Profile  Reply with Quote
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

Edited by - visakh16 on 12/09/2013 06:49:22
Go to Top of Page

azidops
Starting Member

Norway
2 Posts

Posted - 12/09/2013 :  07:45:34  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/09/2013 :  07:50:41  Show Profile  Reply with Quote
cool
glad that I could help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000