Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to duplicate existing rows? Expand / Collapse
Author
Message
Posted Sunday, December 8, 2013 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 6:09 AM
Points: 5, Visits: 15
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:

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:

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'

Post #1520883
Posted Monday, December 9, 2013 1:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 5,024, Visits: 11,754
As you're new here, I have been generous and done some set-up coding for you. In future, please see the link in my signature for an excellent article on how to do this for yourself.

If you run the code below, you'll see that it creates two temp tables and populates them.

--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)
values
(1,700,9000),
(1,701,9001),
(2,700,9002),
(3,700,9003)

insert #tableA(FieldA, FieldB, FieldC)
values
(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')

Would you please validate this code and then add a final select which gives exactly the results which you would like to see, based on the test data?

What I mean is something like this

select * 
from (
values
(1,9000,'some text'),
(2,9001,'some text'),
(3,9000,'some text')
) x
(FieldA,FieldB,FieldC)




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1520940
Posted Monday, December 9, 2013 3:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 6:09 AM
Points: 5, Visits: 15
Hi Phil.

Thanks for instructing on how to make a good post. My apologies for using your time to making my post better. I've corrected and validated your code as instructed. See first post

In regards to the Select-statement you requested I believe if I could construct it by myself I would also be able to solve my problem. Please let me know if I misunderstood your request.
Post #1520976
Posted Monday, December 9, 2013 3:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 5,024, Visits: 11,754
chholm (12/9/2013)
Hi Phil.

Thanks for instructing on how to make a good post. My apologies for using your time to making my post better. I've corrected and validated your code as instructed. See first post

In regards to the Select-statement you requested I believe if I could construct it by myself I would also be able to solve my problem. Please let me know if I misunderstood your request.


Aha - sorry - I forgot that VALUES as a table constructor doesn't work in 2005, sorry about that.

You did misunderstand a little.

I just wanted you to write a select statement that returned the data you require, excluding any calculations. That's because I am assuming that the results you posted are not exactly right.

If I am wrong in that, you will need to explain your logic in more detail, including answers to the following:

1) Why did you select 9001 rather than 9000 for new rows 4,5 and 6 in desired-results?
2) In desired-results rows 1-3 and 7-10, why aren't you returning values from TableB.FieldC? As it stands, it looks like this column contains mixed data - not good.






Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1520979
Posted Monday, December 9, 2013 3:25 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:32 AM
Points: 682, Visits: 1,226

1) Why did you select 9001 rather than 9000 for new rows 4,5 and 6 in desired-results?
2) In desired-results rows 1-3 and 7-10, why aren't you returning values from TableB.FieldC? As it stands, it looks like this column contains mixed data - not good.


+1
Post #1520984
Posted Monday, December 9, 2013 5:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 6:09 AM
Points: 5, Visits: 15
It was meant to improve readability, but I now see how it can be confusing. The correct result would be like this.

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
8..........9001...................................Some text
9..........9001...................................Some text
10..........9001...................................Some text


Rows in bold are new rows


The following explanation may be confusing but that's because I have to work with old data sets. I don't want to remove the exsisting ID's in tableA.fieldB because the will ruin the integrity of the database. I only want to add new rows in TableB if table A has duplicate ID's in TableA.FieldA.

So if I got multiple identical ID's in Table A, two ID #1 this example, I would like one of them to be represented with ID #1 and the second with ID #9001 in Table B. Why? Because I remove the ID #1 reference in Table B data integrity will collapse elsewhere in the database.

Happily I think I got the right solution from another forum. This seems to work for me:
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

The select statement in this insert will give this result which is acceptable

Field A Field B (from TableB.FieldA) Field C
------- ---------------------------- ---------
8..........1........................................Some text
9..........1........................................Some text
10..........1........................................Some text



Thanks for your help and time.
Post #1521037
Posted Monday, December 9, 2013 5:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 5,024, Visits: 11,754
Confusing - yes it is!

OK, I won't spend any more time on the T-SQL side. But I haven't quite finished.

Your FieldB in TableA appears to be an FK to either of FieldA and FieldC in TableB - that's pretty bad design & a little more difficult to enforce than it needs to be

Why not create a new column on TableA which is a pure FK to TableB.FieldC?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1521041
Posted Monday, December 9, 2013 6:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 6:09 AM
Points: 5, Visits: 15
That's because I've only told you part of the story. This is part of a converting project. I'm moving all of the data from one database to another, but I need to keep some sort of track of history in the source database. Those systems are not equal so I have to align metadata from source to destination system. When the data is transferred to the final databases I merge column A and C in table B into the same column and then there is only one PK.

I could probably solve this otherwise but that's how it works for me.
Post #1521043
Posted Monday, December 9, 2013 8:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 5,024, Visits: 11,754
chholm (12/9/2013)
That's because I've only told you part of the story. This is part of a converting project. I'm moving all of the data from one database to another, but I need to keep some sort of track of history in the source database. Those systems are not equal so I have to align metadata from source to destination system. When the data is transferred to the final databases I merge column A and C in table B into the same column and then there is only one PK.

I could probably solve this otherwise but that's how it works for me.


Enough said - sounds like you know what you are doing - good luck.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1521120
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse