1

I am having a simple SQL Graph Database.

Database:

CREATE TABLE Person (
  ID INTEGER PRIMARY KEY,
  name VARCHAR(100)
) AS NODE;

CREATE TABLE City (
  ID INTEGER PRIMARY KEY,
  name VARCHAR(100)
) AS NODE;

CREATE TABLE Travels AS EDGE;

INSERT INTO Person VALUES (3,'Cecil');
INSERT INTO Person VALUES (4,'Diane');
INSERT INTO Person VALUES (2,'Bob');
INSERT INTO Person VALUES (1,'Alice');

Insert into City VALUES (1, 'Almaty')
Insert into City VALUES (2, 'Bangkok')
Insert into City VALUES (3, 'Casablanca')
Insert into City VALUES (4, 'Dublin')


INSERT INTO Travels VALUES ((SELECT $node_id FROM Person WHERE ID = 1),
      (SELECT $node_id FROM City WHERE ID = 1));
INSERT INTO Travels VALUES ((SELECT $node_id FROM Person WHERE ID = 2),
      (SELECT $node_id FROM City WHERE ID = 2));
INSERT INTO Travels VALUES ((SELECT $node_id FROM Person WHERE ID = 3),
      (SELECT $node_id FROM City WHERE ID = 3));
INSERT INTO Travels VALUES ((SELECT $node_id FROM Person WHERE ID = 4),
      (SELECT $node_id FROM City WHERE ID = 4));

I would like to copy these tables to a different tables. My goal is to make it within C#, but simple TSQL solution would be good enough.

CREATE TABLE Person2 (
  ID INTEGER PRIMARY KEY,
  name VARCHAR(100)
) AS NODE;

CREATE TABLE City2 (
  ID INTEGER PRIMARY KEY,
  name VARCHAR(100)
) AS NODE;

CREATE TABLE Travels2 AS EDGE;

I can copy data from Node tables easily, if i would read only user data and not an internal column $node_id:

insert into Person2(ID, name) (select Id, name from Person)
insert into City2(ID, name) (select id, name from City)

but in table Travels, I am having just this information:

select $edge_id, $from_id, $to_id from Travels :

{"type":"edge","schema":"dbo","table":"Travels","id":0} {"type":"node","schema":"dbo","table":"Person","id":3}  {"type":"node","schema":"dbo","table":"City","id":0}

and in order to copy this, i would need to create a join over tables Person, City, Travels to get original IDS and then join it with Person2 and City2 to get new values $from_id, $to_id for Travels2

Another option would be to use functions Graph_ID_FROM_NODE_ID and NODE_ID_FROM_PARTS:

insert into Person2($node_id, ID, name) 
    (select ''+NODE_ID_FROM_PARTS(OBJECT_ID('dbo.Person2'),Graph_ID_FROM_NODE_ID($node_id)), Id, name from Person )

insert into City2($node_id, ID, name)
    (select ''+NODE_ID_FROM_PARTS(OBJECT_ID('dbo.City2'),Graph_ID_FROM_NODE_ID($node_id)), Id, name from City ) 

Insert into Travels2 ($edge_id, $from_id, $to_id) 
(select ''+EDGE_ID_FROM_PARTS(OBJECT_ID('dbo.Travels2'), Graph_ID_FROM_EDGE_ID($edge_id)), ''+NODE_ID_FROM_PARTS(OBJECT_ID('dbo.Person2'),Graph_ID_FROM_NODE_ID($from_id)), ''+NODE_ID_FROM_PARTS(OBJECT_ID('dbo.City2'),Graph_ID_FROM_NODE_ID($to_id)) from Travels
)

Probably, i don't need to copy ids of edges, so last insert would be a bit easier, but i wonder, if there is not any easier way how to copy graph tables.

Jerry Nixon
  • 31,313
  • 14
  • 117
  • 233
robert guth
  • 41
  • 2
  • I don't know SQL Graph but maybe SQL Management as menus to copy graphs. If exists maybe you could see code/script. – Nando Feb 14 '20 at 13:26
  • Thanks for suggestion. SQL Management studio/tasks/generate scripts with schema and data option checked would just generate list of values stored in those graph tables, which could replace Select * from Table query, but same changes as in my second suggestion would need to be applied for being able to work. – robert guth Feb 14 '20 at 13:35

0 Answers0