December 8, 2013 at 12:51 pm
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'
December 9, 2013 at 1:10 am
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)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 9, 2013 at 3:01 am
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.
December 9, 2013 at 3:16 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 9, 2013 at 3:25 am
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
December 9, 2013 at 5:33 am
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.
December 9, 2013 at 5:51 am
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?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 9, 2013 at 6:03 am
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.
December 9, 2013 at 8:23 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply