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.