插入到 SQL Server 2017 中的边缘表中

Question

目前，我正在尝试学习SQL Server 2017的图形数据库功能，为此，我正在尝试将一组当前数据加载到一组节点和边缘表中。

我没有遇到从SELECT查询加载到节点表中的问题。但是我在尝试将数据加载到我的边缘表中时遇到了麻烦。

我有一个名为kontomappingDetaljeret的表，其中包含kontoNummer和refNr

我也有包含这些功能作为节点的节点表。我正在尝试映射 kontoNummer 和 refNr 列之间的关系。

到目前为止，我有这个查询没有执行

INSERT INTO brugtIRef 
VALUES 
    ((SELECT $node_id FROM o90Konto WHERE kontoNummer = konti.konto),
     (SELECT $node_id FROM refNummer WHERE refNr = konti.refNr))
FROM
    KontomappingDetaljeret konti
INNER JOIN
    o90Konto ON konti.konto = o90Konto.kontoNummer
INNER JOIN
    refNummer ON konti.refNr = refNummer.refNr

编辑

当运行以下查询时，我得到这个结果

SELECT TOP (10)
    (SELECT $node_id FROM dbo.o90Konto WHERE kontoNummer = konti.konto) AS KontoNr,
    (SELECT $node_id FROM dbo.refNummer WHERE refNr = konti.refNr) AS refNr
FROM
    KontomappingDetaljeret konti
INNER JOIN
    dbo.o90Konto
ON 
    konti.konto = o90Konto.kontoNummer
INNER JOIN
    dbo.refNummer
ON 
    konti.refNr = refNummer.refNr

结果在

KontoNr refNr
{"type":"node","schema":"dbo","table":"o90Konto","id":180657}   {"type":"node","schema":"dbo","table":"refNummer","id":0}
{"type":"node","schema":"dbo","table":"o90Konto","id":211723}   {"type":"node","schema":"dbo","table":"refNummer","id":1}
{"type":"node","schema":"dbo","table":"o90Konto","id":216400}   {"type":"node","schema":"dbo","table":"refNummer","id":2}
{"type":"node","schema":"dbo","table":"o90Konto","id":205844}   {"type":"node","schema":"dbo","table":"refNummer","id":3}
{"type":"node","schema":"dbo","table":"o90Konto","id":388939}   {"type":"node","schema":"dbo","table":"refNummer","id":4}
{"type":"node","schema":"dbo","table":"o90Konto","id":536204}   {"type":"node","schema":"dbo","table":"refNummer","id":5}
{"type":"node","schema":"dbo","table":"o90Konto","id":130331}   {"type":"node","schema":"dbo","table":"refNummer","id":6}
{"type":"node","schema":"dbo","table":"o90Konto","id":83983}    {"type":"node","schema":"dbo","table":"refNummer","id":7}
{"type":"node","schema":"dbo","table":"o90Konto","id":344522}   {"type":"node","schema":"dbo","table":"refNummer","id":7}
{"type":"node","schema":"dbo","table":"o90Konto","id":523743}   {"type":"node","schema":"dbo","table":"refNummer","id":7}

我只需要一些方法将此结果插入到边缘表中

Answer 1

终于想通

了

INSERT INTO 
    dbo.brugtIRef
    SELECT 
        (SELECT DISTINCT $node_id FROM o90Konto WHERE kontoNummer = konti.konto) AS KontoNr,
        (SELECT DISTINCT $node_id FROM refNummer WHERE refNr = konti.refNr) AS refNr
    FROM
        (SELECT DISTINCT konto, refNr FROM KontomappingDetaljeret) konti
    INNER JOIN
        (SELECT DISTINCT * FROM o90Konto) o90Konto
    ON 
        konti.konto = o90Konto.kontoNummer
    INNER JOIN
        (SELECT DISTINCT * FROM refNummer) refNummer 
    ON 
        konti.refNr = refNummer.refNr

我现在可以针对此图运行查询