Merge data from one table into another

[chigrboy]

I have a database that I'm trying to simplify the design of but I'm struggling a little with it.

Just for illustration let's say I have two tables, TableA and TableB.

Each Row in TableA stores info for a type of document.

TableB stores info for who that document in TableA gets sent to. It allows the user to input as many people as needed per row in TableA but
after years of having this method, there are never more then two.

So I've created two fields in TableA to store that info instead of having Two Tables (this applies in many places and not just this simple Illustration).

The important columns for this illustration are is as follows:
TableA:
ID (PKEY)
SendTo_1 (Newly Created Column)
SendTo_2 (Newly Created Column)

TableB:
ID (PKEY)
TableA_ID (FKEY)
ContactID


I need to run a query that takes the ContactID data from TableB and insert it into TableA's sendTO_1 and SendTo_2 Fields respectively. It's easy if it's just one-to-one but it's not.
It's one-to-one and sometimes one-to-two depending on the doc.

How can I do this gracefully?

[ck9663]

A straight-forward JOIN with a twist will do :)

Here, tweak this:

 
;with sorted
as
(
   select rowid = row_number() over(parition by id order by id, contactid), tablea_id, contactid
   from tableB
)
insert into tablea(id, sendto_1, sendto_2)
select
   tablea_id, s1.contactid, s2.contactid
from sorted s1
left join sorted s2 on s1.tablea_id = s2.tablea_id and s1.rowid+1 = s2.rowid
 
 

Make sure your sendto_2 is NULLABLE.

Happy Coding!!!


~~ CK