如何根据不同表中的多个列将多行插入到“多对多”表中

I'm performing a data migration to a new database with a very different structure. Using SQL Server & T-SQL.

我正在执行数据迁移到具有非常不同结构的新数据库。使用SQL Server和T-SQL。

Here is a SQLFiddle example of my tables and where I have gotten so far:
http://sqlfiddle.com/#!18/d179b/1

这是我的表的SQLFiddle示例,到目前为止我已经到了这里:http://sqlfiddle.com/#!18 / d179b / 1

I have four tables:

我有四张桌子:

CallNotes:

    Id, uniqueidentifier
    OldCallRecordId, int -- Used for migrating only, not in final
    Content, nvarchar(100)

CallTopics:

    Id, uniqueidentifier
    Name, nvarchar(50)

CallNoteCallTopics:

    CallNoteId, uniqueidentifier
    CallTopicId, uniqueidentifier

OldCallRecords:

    Id, int
    CallTopicOne, bit
    CallTopicTwo, bit
    CallTopicThree, bit
    CallTopicFour, bit

I am trying to get all the true CallTopic* values from OldCallRecords into CallNoteCallTopics.CallTopicId during an INSERT statement

我试图在INSERT语句期间从OldCallRecords获取所有真正的CallTopic *值到CallNoteCallTopics.CallTopicId

All the other tables already have data in them.

所有其他表中都已包含数据。

I'm currently trying with a CASE statement, but that's only getting me the first true value, which isn't what I want:

我正在尝试使用CASE语句,但这只是让我获得第一个真正的价值,这不是我想要的:

INSERT INTO CallNoteCallTopics
    SELECT
        CallNotes.Id,
        CASE
           WHEN OldCallRecords.CallTopicOne = 1 
              THEN (SELECT CallTopics.Id 
                    FROM CallTopics 
                    WHERE CallTopics.Name = 'Call Topic One')
          WHEN OldCallRecords.CallTopicTwo = 1 
             THEN (SELECT CallTopics.Id 
                   FROM CallTopics 
                   WHERE CallTopics.Name = 'Call Topic Two')
          WHEN OldCallRecords.CallTopicThree = 1 
             THEN (SELECT CallTopics.Id 
                   FROM CallTopics 
                   WHERE CallTopics.Name = 'Call Topic Three')
          WHEN OldCallRecords.CallTopicFour = 1 
             THEN (SELECT CallTopics.Id 
                   FROM CallTopics 
                   WHERE CallTopics.Name = 'Call Topic Four')
        END
    FROM 
        OldCallRecords
    LEFT JOIN 
        CallNotes ON CallNotes.OldCallRecordId = OldCallRecords.Id

1 个解决方案

#1

That happens because when one of the candidate column hits corresponding CASE WHEN then it will be output, the same row will not be checked again.

发生这种情况是因为当其中一个候选列点击相应的CASE WHEN然后它将被输出时,将不再检查相同的行。

You can try use UNION

您可以尝试使用UNION

INSERT INTO CallNoteCallTopics
 SELECT
   CallNotes.Id
   ,(SELECT CallTopics.Id FROM CallTopics WHERE CallTopics.Name = 'Call Topic One') as CallTopicId
 FROM OldCallRecords
 LEFT JOIN CallNotes
 ON CallNotes.OldCallRecordId = OldCallRecords.Id
 WHERE OldCallRecords.CallTopicOne = 1

UNION 

 SELECT
   CallNotes.Id
   ,(SELECT CallTopics.Id FROM CallTopics WHERE CallTopics.Name = 'Call Topic Two') as CallTopicId
 FROM OldCallRecords
 LEFT JOIN CallNotes
 ON CallNotes.OldCallRecordId = OldCallRecords.Id
 WHERE OldCallRecords.CallTopicTwo = 1

UNION 

 SELECT
   CallNotes.Id
   ,(SELECT CallTopics.Id FROM CallTopics WHERE CallTopics.Name = 'Call Topic Three') as CallTopicId
 FROM OldCallRecords
 LEFT JOIN CallNotes
 ON CallNotes.OldCallRecordId = OldCallRecords.Id
 WHERE OldCallRecords.CallTopicThree = 1

UNION 

 SELECT
   CallNotes.Id
   ,(SELECT CallTopics.Id FROM CallTopics WHERE CallTopics.Name = 'Call Topic Four') as CallTopicId
 FROM OldCallRecords
 LEFT JOIN CallNotes
 ON CallNotes.OldCallRecordId = OldCallRecords.Id
 WHERE OldCallRecords.CallTopicFour = 1

#1