Help us understand the problem. What is going on with this article?

【SQL】結合したテーブルを基に、複数レコードをテーブルに挿入する(ROW_NUMBER))

More than 1 year has passed since last update.

結合したテーブルの複数レコードを、別テーブルに挿入する方法

売上表を時系列データを持つ売上明細表と結合させ、8月の売上表レコードを抽出して別テーブルに挿入
INSERT INTO August_SalesDetails(Id,SalesId,ProductId,Quantity,UnitPrice,Price,Note)

/*SELECTで抽出した表が挿入される*/
SELECT
ROW_NUMBER() over(order by D.id) +  (select max(Id) from SalesDetails)
,D.SalesId
,D.ProductId
,D.Quantity
,D.UnitPrice
,D.Price
,D.Note

/*以下で表を結合。8月の売上データ表を生成*/
FROM
SalesDetails D
join
Sales Sa
On D.SalesId = Sa.Id
Where MONTH(Sa.SalesAt) = 8 and YEAR(Sa.SalesAt) = 2019  

主要キーであるIdカラムには抽出したフィールドを挿入することができないので、ROWNUMBER()を使って、順番にIdを振り直す。

Obakeya
普段はブクログに本の感想を書いています。 C# 2019/7~ (案件の10%ぐらいVB.NET) ・Windows Communication Foundation SQL Server 2019/8~ Git Lab 2019/8~ Source Tree 2019/8~ Trello 2019/11~
http://tanosikeizaigaku.hatenablog.jp
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした