Merge two tables into a single table

To create a new table, there are two options: either have one row per message where the recipients are concatenated in a single column, or have multiple rows per message if there are multiple recipients (with one recipient per row). To achieve this, the following SQL query can be used:
SELECT t1.A, t1.B, t1.C, t2.D, t2.E FROM Table1 t1 JOIN Table2 t2 ON t1.A = t2.A


Question:

In MS
SQL Server
, there are two tables for the year 2012 that need to be merged into a single table. These tables are connected through the messageID column. The initial table, named “Message,” contains…

messageID
sender
date

Within the recipientInfo table, there is a second section that contains…

messageID
recipient

The primary issue is that the messageID is the unique primary key in the Message table, but not in the recipientInfo table where it can have multiple recipients. To address this, a new table could be created.

  • Each message is represented in a single row, with all recipients listed in a concatenated column.
  • When there are multiple recipients, with each recipient mentioned in a separate row, the cost per message is
    Multiple rows
    .



Solution 1:

If you are utilizing MSSQL 2005 or a more recent version, a possible alternative could be something along these lines.

CREATE TABLE NewTable
(
    messageID INT,
    sender VARCHAR(100),
    recipient VARCHAR(MAX),
    date DATETIME
)
INSERT INTO NewTable(messageID,sender,recipient,date)
SELECT
    [Message].messageID,
    [Message].sender,
    STUFF
    (
        (
            SELECT 
                ',' +recipient
            FROM
                recipientInfo
            WHERE
                recipientInfo.messageID=[Message].messageID
            FOR XML PATH('')
        )
    ,1,1,'') AS recipient,
    [Message].date
FROM
    [Message]


Solution 2:


Would you prefer to create a view while retaining the existing tables, or merge the two tables permanently?

SELECT m.messageID, m.sender, m.date, r.recipient
INTO New_Table
FROM Message m LEFT OUTER JOIN
recipientinfo r on m.messageID = r.messageID


Solution 3:

select msg.messageID, msg.sender, rep.recipient, msg.date 
from Message msg 
left outer join recipientInfo rep on msg.messageID = rep.messageID


Solution 4:

It may be necessary to arrange the spellings in this case.

  select sender,date,( select recipient+chr(10) from receipientInfo as r where r.messageid = m.messageid order by r.recipient FOR XML PATH ('')) as receipents from message as m

Frequently Asked Questions

Posted in Sql