sql - 创建关联表时从其他表创建行 - SQL Server

Question

我有三个表Strike、Fire 和StrikeFire(关联表)。我试图只存储引起数据的罢工，如果它们具有相同的日期、纬度和经度的话。

下面是我尝试用现有 ID 记录填充关联表的方法。我在 FROM 条件之后卡住了

INSERT INTO StrikeFire SELECT StrikeID, FireID FROM

下面是表格和数据

CREATE TABLE Strike (StrikeID int IDENTITY(1, 1) NOT NULL,
                     StrikeDate date NOT NULL,
                     StrikeTime time NOT NULL,
                     StrikeLatitude numeric(4, 2) NOT NULL,
                     StrikeLongitude numeric(5, 2) NOT NULL,
                     StrikeIntensity int NOT NULL,
                     CONSTRAINT PK_Strike
                         PRIMARY KEY (StrikeID));

CREATE TABLE Fire (FireID int IDENTITY(1, 1) NOT NULL,
                   FireDate date NOT NULL,
                   FireLatitude numeric(4, 2) NOT NULL,
                   FireLongitude numeric(5, 2) NOT NULL,
                   FireArea numeric(6, 2) NOT NULL,
                   CONSTRAINT PK_Fire
                       PRIMARY KEY (FireID));

CREATE TABLE StrikeFire (StrikeID int NOT NULL,
                         FireID int NOT NULL,
                         CONSTRAINT PK_StrikeFire
                             PRIMARY KEY (
                             StrikeID,
                             FireID),
                         CONSTRAINT FK_Strike
                             FOREIGN KEY (StrikeID)
                             REFERENCES Strike (StrikeID),
                         CONSTRAINT FK_Fire
                             FOREIGN KEY (FireID)
                             REFERENCES Fire (FireID));

这里是虚拟插入数据

INSERT INTO Strike (StrikeDate,
                    StrikeTime,
                    StrikeLatitude,
                    StrikeLongitude,
                    StrikeIntensity)
VALUES ('05-05-2006', '02:02', 41.34, -122.45, 6235),
       ('05-05-2006', '02:09', 40.47, -120.47, 16235),
       ('05-05-2006', '03:32', 42.14, -122.98, 7779),
       ('05-05-2006', '04:34', 38.32, -122.17, 4645),
       ('05-05-2006', '05:02', 39.04, -121.22, 8989);

INSERT INTO Fire (FireDate,
                  FireLatitude,
                  FireLongitude,
                  FireArea)
VALUES ('05-05-2006', 32.34, -122.45, 123.90),
       ('05-05-2006', 37.19, -121.66, 627.09),
       ('05-05-2006', 40.47, -120.47, 45.00),
       ('05-05-2006', 42.14, -122.98, 1774.90),
       ('05-05-2006', 37.21, -120.47, 2034.80),
       ('05-05-2006', 42.04, -126.22, 49.62);

Answer 1

试试这个:

INSERT INTO StrikeFire 
SELECT StrikeID, FireID 
FROM Fire F
Join Strike S
on F.FireDate = S.StrikeDate
and F.FireLatitude = S.StrikeLatitude
and F.FireLongitude = S.StrikeLongitude