SQL Server How to insert multiple rows in MS SQL from a second table

ui7jx7zq 于 2023-03-07 发布在其他

关注(0)|答案(1)|浏览(170)

I have a sql table called InputTable with the following elements:

CaseID    Note1    Note2    UpdatedBy    UpdatedDate

I need to copy the data from InputTable into a second table (called OutputTable) that has the following elements:

CaseKey   Note   LastModifiedBy   LastModifiedDate

My current script looks like this:

INSERT INTO OutputTable (CaseKey, Note, LastModifiedBy, LastModifiedDate)
SELECT CaseID, Note1, UpdatedBy, UpdatedDate
FROM InputTable

How can I modify the script so it will write 2 records to OutputTable? The first record should pick up InputTable.CaseID, InputTable.Note1, InputTable.UpdatedBy, and InputTable.UpdatedDate and the second record should pick up InputTable.CaseID, InputTable.Note2, InputTable.UpdatedBy, and InputTable.UpdatedDate.

Thank you.

sql-server

来源：https://stackoverflow.com/questions/75561740/how-to-insert-multiple-rows-in-ms-sql-from-a-second-table

1条答案

按热度按时间

6jygbczu1#

You can do it with a cross apply:

INSERT INTO OutputTable (CaseKey, Note, LastModifiedBy, LastModifiedDate)
SELECT CaseID, x.note, UpdatedBy, UpdatedDate
FROM InputTable i
cross apply (
  select Note1 AS note
  union all
  select Note2
) x

This creates two rows from one with Note1 and Note2 as the only difference.

If you want to supress duplicate Note1 and Note2, you can change UNION ALL to just UNION or change select Note2 to select Note2 WHERE Note2 <> Note1

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server How to insert multiple rows in MS SQL from a second table

1条答案

相关问题

热门标签

最新问答