使用基于外键关系的另一个表中的值填充新表

Question

我有一个连接到QGIS的SQL Server数据库。我正在使用SQLServerManagementStudio，试图用另一个表中的值填充新表中的列。我知道创建视图是更好的做法，但当我尝试将视图加载到QGIS时，它会返回以下错误消息：

Layer is not valid: The layer dbname='MBA-LBA_Ornaments' 
host=DESKTOP-DLA7UV8 estimatedmetadata=true 
srid=4326 type=Point disableInvalidGeometryHandling='1' 
primaryKeyInGeometryColumns='0' table="dbo".
"GoldWeight" (Geo4) is not a valid layer and can not be added to the map.
Reason: No primary key could be found on table GoldWeight

我能想到的唯一解决方法是创建一个新表，其中包含来自"装饰"表和"站点"表的字段。以下是相关表格的结构：

CREATE TABLE [dbo].[Ornaments](
[OrnID] [smallint] IDENTITY(1000,1) NOT NULL,
[OrnForm] [varchar](20),
[OrnSubtype] [varchar](20),
[FragmentYN] [char](1),
[Material] [varchar](30),
[Period] [varchar](10),
[Phase] [varchar](20),
[BasisOfDate] [varchar](50),
[SiteID] [smallint],
[DiscContext] [varchar](20),
[DiscYear] [varchar](10),
[SingleFindYN] [char](1),
[BrokenYN] [varchar](2),
[DistortedYN] [char](1),
[WeightG] [numeric](8, 2),
[SpatialPrecisionRating] [char](1),
[ImageYN] [char](1),
[Comments] [varchar](500),
CREATE TABLE [dbo].[Sites](
[SiteID] [smallint] IDENTITY(1,1) NOT NULL,
[SiteName] [varchar](50),
[SiteType] [varchar](50),
[Region] [varchar](50),
[CountyDistrict] [nvarchar](30),
[Latitude] [decimal](11, 6),
[Longitude] [decimal](11, 6),
[Comments] [varchar](500),
[Geo4]  AS (case when [Latitude] IS NOT NULL AND [Longitude] IS NOT NULL 
then [geography]::Point([Latitude],[Longitude],(4326))  end),

GoldWeight表格是使用创建的

SELECT SiteID, SUM(WeightG) AS 'Weight'
INTO GoldWeight
FROM Ornaments
WHERE Material = 'gold'
GROUP BY SiteID

然后我添加了列SiteName和Geo4使用：

ALTER TABLE GoldWeight
ADD SiteName VARCHAR(50) NULL, 
Geo4 geography NULL

并使用添加了一个外键

ALTER TABLE GoldWeight
ADD FOREIGN KEY (SiteID) REFERENCES Sites(SiteID)

我原以为添加外键会填充字段，但事实并非如此。是否有方法根据SiteID外键关系用适当的数据填充SiteName和Geo4字段？

Answer 1

这个查询应该可以达到目的，但是将数据复制到引用表不是一个好的做法。

UPDATE G
SET G.SiteName = S.SiteName,
G.Geo4 = S.Geo4
FROM GoldWeight G
INNER JOIN SITES S ON S.SiteID= G.SiteID

Answer 2

我最终找到了一个答案，那就是使用将视图中的结果选择到一个新表中

SELECT dbo.GoldWeight.SiteID, 
dbo.GoldWeight.Weight, 
dbo.Sites.SiteName, 
dbo.Sites.SiteType, 
dbo.Sites.Region, 
dbo.Sites.CountyDistrict,
dbo.Sites.Geo4
INTO GoldWeight2
FROM   dbo.GoldWeight INNER JOIN
dbo.Sites ON dbo.GoldWeight.SiteID = dbo.Sites.SiteID

然后添加一个外键：

ALTER TABLE GoldWeight2
ADD FOREIGN KEY (SiteID) REFERENCES Sites(SiteID)

然后将SiteID列设置为NOT NULL：

ALTER TABLE
QGIS_GoldWeight2
ALTER COLUMN
SiteID smallint NOT NULL;

然后将SiteID设置为主键：

ALTER TABLE QGIS_GoldWeight2
ADD PRIMARY KEY (SiteID);

我不得不取消选中"；防止保存需要重新创建表的更改"；在"工具"下>选项>设计器成功设置主键。

它创建了一个QGIS可以读取的表，但在导入到QGIS后必须删除该表，因为如果Sites表中的字段发生更改，数据不会更新。