{"id":89726,"date":"2021-01-29T19:18:24","date_gmt":"2021-01-29T19:18:24","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=89726"},"modified":"2026-05-08T10:17:19","modified_gmt":"2026-05-08T10:17:19","slug":"using-a-modeling-tool-to-draw-and-import-a-graph-into-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/using-a-modeling-tool-to-draw-and-import-a-graph-into-sql-server\/","title":{"rendered":"SQL Server Graph Tables: Drawing Graphs in yEd and Importing Nodes and Edges"},"content":{"rendered":"

SQL Server’s graph tables (node tables and edge tables, introduced in 2017) let you model and query graph-structured data natively, but populating them with realistic test data often means typing dozens or hundreds of INSERT statements by hand. <\/b><\/p>\n

This article shows a faster workflow: draw the graph visually in yEd – a free graph modeling tool – export it as TGF (Trivial Graph Format, yEd’s simple text export), and parse the TGF file in T-SQL to generate INSERT statements for both the node table and the edge table automatically. <\/b><\/p>\n

The approach reduces a tedious manual task to a few-minutes workflow and keeps the visual representation and the database representation in sync. Suitable for creating demo data for presentations, loading reference graphs from published datasets, or bootstrapping a graph database from an existing diagram.<\/b><\/p>\n

As I am starting to prep for writing about graphs in SQL Server, I have reached the most challenging part of the process\u2026 demo data. That\u2019s right, the code, the words, etc., all so much easier than making an example that demonstrates everything you want it to and has an easy way to present it (and is malleable for when you realize you forgot about a case that you meant to cover but have spent 10 hours honing your point already). For some of the data in the book, I have created data generators that will spit out huge amounts of data to test performance, but most of the sample data needs to be interesting to the reader to go beyond the technical details and into the problem being solved and give them a story to hand the concept on.<\/p>\n

To visualize graphs for my Database Design book and blogs, I have been using a tool name yEd (https:\/\/www.yworks.com\/products\/yed<\/a>) that does a pretty great job of letting you visualize a graph. For example, say I have drawn the following graph to cover the fairly simple test case:<\/p>\n

<\/p>\n

The problem was, if I wanted to recreate this graph in data, I had to type in a bunch of SQL statements (something I generally enjoy to a certain point, but one of my sample files cover the geography of Disney World, and it would take a very long time to manually type that into a database as it took quite a while just to do one section of the park).<\/p>\n

So I went hunting for a tool to do this for me, but ended right back with yEd. The default file type when you save in yEd is GraphML, which is basically some pretty complex XML that was well beyond my capabilities using XML in SQL or Powershell. Realistically I don\u2019t care that much about anything other than just the nodes and edges, and what I found was that you can save graphs in the tool a format named Trivial Graph Format (TGF). TGF is a very simple format that you can read more about here (https:\/\/en.wikipedia.org\/wiki\/Trivial_Graph_Format<\/a>) but from that article, but format is simply:<\/p>\n

1 First node\n2 Second node\n#\n1 2 Edge between the two<\/pre>\n
Be careful to not save the graph as TGF only, unless you are fine having to reformat constantly. This is only the nodes and edges with name data, so when you open the TGF file in yEd, all the nodes will be on top of each other.<\/p>\n
The node and edge names are optional, and while I would not desire to have empty node names (and my importer will fail if that occurs), my plan for the a model will be that, all nodes will be the same type, but edge types could vary. For example, say you have this basic structure:<\/p>\n
<\/p>\n
Each of these edges represents a containership, so I don’t want to enumerate that on the diagram. However, if I add another municipality to the graph, I might want to say it is adjacent to another node, so I model the following, giving the edge a name.:<\/p>\n
<\/p>\n
To do this extra metadata, I will use a filename pattern of \u201cNodeType-DefaultEdgeType-Description or Uniqueifier\u201d (the Uniqueifier basically is there to make because we might have duplicate node types and edges, but all the file names obviously have to be unique.<\/p>\n
Going back to my original model, it is stored in the TGF file as:<\/p>\n
1 1\n2 2\n3 1\n4 3\n5 4\n6 Node Name\n7 7\n8 6\n9 5\n10 Possesssive's Node Name\n11 8\n#\n4 5\n3 5\n2 3\n2 4\n1 3 Possessive's Edge Name\n6 3\n5 9\n8 9\n7 8\n7 10\n10 10 Self Reference\n10 11<\/pre>\n
Simple enough, but it does have a few complexities in the parsing. All of the fields in this file are space delimited, but the names have spaces in them\u2026 Not a terribly large deal, but it did make it more interesting.<\/p>\n
To solve the problem, I decided to make some reasonably primative Powershell that could take a directory of TGF files and import them into SQL Server. I stopped short of automatically importing the data, but instead made the code generate a set of SQL Files that create a staging table and insert the data. I didn\u2019t make it a module or function, but just a simple ps1 script to run interactively (hardcoding directories) as this is the kind of process that if you need to do something similar, you probably will make a change or two. (And adding automatic inserting of the data would not be terribly difficult, but typically wouldn’t be necessary).<\/p>\n
I will include the Powershell code at the end of the post (and provide a link to github to fetch it), but the output is most interesting. The first block creates the tables if they don\u2019t exist in your database (a USE statement is added and is parameterizable in the ps1 file), and a DELETE statement removes data from a file of the same name (for when you are running this over and over getting it just right):<\/p>\n
Note: the code was formatted using SQL Prompt, it is a lot more spread out from the generator.<\/em><\/p>\n
USE tempdb;\nGO\nSET NOCOUNT ON;\nGO\n--create the schema and tables if they have not been created in the schema you chose\nIF NOT EXISTS (   SELECT *\n                  FROM   sys.schemas\n                  WHERE  schemas.name = 'NodeStaging')\n    EXEC('CREATE SCHEMA NodeStaging');\nGO\nIF NOT EXISTS (   SELECT *\n                  FROM   sys.tables\n                  WHERE  tables.object_id = OBJECT_ID('NodeStaging.Node'))\n    CREATE TABLE NodeStaging.Node\n    (\n        Filename nvarchar(200) NOT NULL,\n        NodeId   int           NOT NULL,\n        Name     nvarchar(100) NOT NULL,\n        NodeType nvarchar(100) NOT NULL,\n        PRIMARY KEY(\n            Filename,\n            NodeId)\n    );\nGO\nIF NOT EXISTS (   SELECT *\n                  FROM   sys.tables\n                  WHERE  tables.object_id = OBJECT_ID('NodeStaging.Edge'))\n    CREATE TABLE NodeStaging.Edge\n    (\n        Filename   nvarchar(200) NOT NULL,\n        FromNodeId int           NOT NULL,\n        ToNodeId   int           NOT NULL,\n        EdgeType   varchar(100)  NULL\n    );\nGO\n--delete previous data staged from this filename to let this run repeatedly\nDELETE FROM NodeStaging.Node\nWHERE Node.Filename = 'NodeType-DefaultEdgeType-Sample';\nGO\nDELETE FROM NodeStaging.Edge\nWHERE Edge.Filename = 'NodeType-DefaultEdgeType-Sample';\nGO<\/pre>\n
Next there are two sets of inserts. Nodes:<\/p>\n
--Nodes\nINSERT INTO NodeStaging.Node (FileName, NodeId,Name,NodeType) \nVALUES ( 'NodeType-DefaultEdgeType-Sample',1,'1','NodeType');\nINSERT INTO NodeStaging.Node (FileName, NodeId,Name,NodeType) \nVALUES ( 'NodeType-DefaultEdgeType-Sample',2,'2','NodeType');\nINSERT INTO NodeStaging.Node (FileName, NodeId,Name,NodeType) \nVALUES ( 'NodeType-DefaultEdgeType-Sample',3,'1','NodeType');\n\u2026<\/pre>\n
And then edges:<\/p>\n
--Edges\nINSERT INTO NodeStaging.Edge (FileName, FromNodeId, ToNodeId, EdgeType) \nVALUES ('NodeType-DefaultEdgeType-Sample',4,5, COALESCE (NULL,'DefaultEdgeType'));\nINSERT INTO NodeStaging.Edge (FileName, FromNodeId, ToNodeId, EdgeType) \nVALUES ('NodeType-DefaultEdgeType-Sample',3,5, COALESCE (NULL,'DefaultEdgeType'));\nINSERT INTO NodeStaging.Edge (FileName, FromNodeId, ToNodeId, EdgeType) \nVALUES ('NodeType-DefaultEdgeType-Sample',2,3, COALESCE (NULL,'DefaultEdgeType'));\n\u2026<\/pre>\n
The final bit of code shows you the data for your imported nodes:<\/p>\n
--Queries to output the nodes that have been created\nSELECT *\nFROM   NodeStaging.Node\nWHERE  Node.Filename = 'NodeType-DefaultEdgeType-Sample'\nSELECT *\nFROM   NodeStaging.Edge\nWHERE  Edge.Filename = 'NodeType-DefaultEdgeType-Sample'\nORDER BY Edge.FromNodeId,Edge.ToNodeId\nGO<\/pre>\n
Run this code with the sample file, the output is:<\/p>\n
<\/p>\n
Now I can use this data to easily load a set of nodes in a tree, using SQL Graph or an adjacency list.<\/p>\n
The following code (and two sample files, one that is the first graph in the blog, the second a subgraph of that graph) is available here: https:\/\/github.com\/drsqlgithub\/TGFImport\/<\/a>:<\/p>\n
#######################################################################################\n##\u00a0Louis\u00a0Davidsson\u00a0drsql@hotmail.com\n##\u00a0Use\u00a0at\u00a0your\u00a0own\u00a0risk.\u00a0Produces\u00a0SQL\u00a0script\u00a0files\u00a0from\u00a0TGF\u00a0files.\n##\u00a0Parameters\n$directory\u00a0=\u00a0\"E:\\TGF\u00a0Files\\\"\u00a0#Location\u00a0of\u00a0the\u00a0TGF\u00a0files\u00a0for\u00a0importing\n$outputDirectory\u00a0=\u00a0\"E:\\TGF\u00a0Files\u00a0Output\\\"\u00a0#directory\u00a0where\u00a0the\u00a0.SQL\u00a0files\u00a0are\u00a0sent\n#Configuration\n$StagingDatabase\u00a0=\u00a0'Tempdb'\u00a0#Defaulted\u00a0to\u00a0tempdb\u00a0so\u00a0someone\u00a0doesn't\u00a0add\u00a0objects\u00a0to\u00a0master\u00a0or\u00a0some\u00a0other\u00a0database\u00a0and\u00a0\n#it\u00a0be\u00a0my\u00a0fault\n$StagingSchema\u00a0=\u00a0'NodeStaging'\n$filter\u00a0=\u00a0\"*.TGF\"\u00a0#used\u00a0to\u00a0limit\u00a0to\u00a0certain\u00a0file\u00a0names\n#Debugging\u00a0is\u00a0easier\u00a0with\u00a0a\u00a0lot\u00a0of\u00a0output,\u00a0but\u00a0mute\u00a0normally\n$VerboseMode\u00a0=\u00a0$True\n$VerboseCountAnnounce\u00a0=\u00a0100\n#########################################################################################\n$Files\u00a0=\u00a0Get-ChildItem\u00a0$directory\u00a0-Filter\u00a0$Filter\n\"Processing\u00a0directory:\u00a0$directory\u00a0for\u00a0'.TGF'\u00a0files\"\nif\u00a0($VerboseMode)\u00a0{\n\u00a0\u00a0\u00a0\u00a0\"\"\n\u00a0\u00a0\u00a0\u00a0\"Import\u00a0start\u00a0time:\u00a0$(Get-Date)\"\u00a0\n}\nfor\u00a0($i\u00a0=\u00a00;\u00a0$i\u00a0-lt\u00a0$files.Count;\u00a0$i++)\u00a0{\n\u00a0\u00a0\u00a0\u00a0$Request\u00a0=\u00a0$files[$i].FullName\u00a0#file\u00a0to\u00a0be\u00a0processed\n\u00a0\u00a0\u00a0\u00a0$BaseName\u00a0=\u00a0$files[$i].BaseName\u00a0#filename\u00a0without\u00a0path\u00a0or\u00a0extension\u00a0to\u00a0identity\u00a0data\u00a0in\u00a0import\n\u00a0\u00a0\u00a0\u00a0#filename\u00a0should\u00a0include\u00a0NodeType-DefaultEdgeType-Unique,\u00a0identifying\u00a0info.tgf\n\u00a0\u00a0\u00a0\u00a0$NodeType,\u00a0$EdgeType\u00a0=\u00a0$BaseName.split('-')[0,1]\u00a0\n\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0#some\u00a0output\u00a0for\u00a0testing\n\u00a0\u00a0\u00a0\u00a0if\u00a0($VerboseMode)\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"\"\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"Processing\u00a0file:\"\u00a0+\u00a0$Request\n\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0#sql\u00a0file\u00a0that\u00a0will\u00a0be\u00a0put\u00a0out\u00a0for\u00a0the\u00a0import\n\u00a0\u00a0\u00a0\u00a0$OutputFile\u00a0=\u00a0$outputDirectory\u00a0+\u00a0$BaseName\u00a0+\u00a0'.SQL'\n\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0#code\u00a0to\u00a0create\u00a0the\u00a0staging\u00a0table\u00a0if\u00a0required.\u00a0#filename\u00a0is\u00a0included\u00a0so\u00a0you\u00a0can\u00a0have\u00a0>\u00a01\u00a0copy\u00a0of\u00a0the\u00a0same\u00a0\n\u00a0\u00a0\u00a0\u00a0#graph\u00a0imported\n\u00a0\u00a0\u00a0\u00a0$WriteThis\u00a0=\u00a0\n\u00a0\u00a0\u00a0\u00a0\"\nUSE\u00a0$StagingDatabase;\nGO\nSET\u00a0NOCOUNT\u00a0ON;\nGO\n--create\u00a0the\u00a0schema\u00a0and\u00a0tables\u00a0if\u00a0they\u00a0have\u00a0not\u00a0been\u00a0created\u00a0in\u00a0the\u00a0schema\u00a0you\u00a0chose\nIF\u00a0NOT\u00a0EXISTS\u00a0(SELECT\u00a0*\u00a0FROM\u00a0sys.schemas\u00a0where\u00a0name\u00a0=\u00a0'$StagingSchema')\n\u00a0\u00a0\u00a0\u00a0EXEC\u00a0('CREATE\u00a0SCHEMA\u00a0$StagingSchema')\nGO\nIF\u00a0NOT\u00a0EXISTS\u00a0(SELECT\u00a0*\u00a0FROM\u00a0sys.tables\u00a0where\u00a0object_id\u00a0=\u00a0OBJECT_ID('$StagingSchema.Node'))\n\u00a0\u00a0\u00a0\u00a0CREATE\u00a0TABLE\u00a0$StagingSchema.Node\u00a0(Filename\u00a0nvarchar(200)\u00a0NOT\u00a0NULL,\u00a0NodeId\u00a0int\u00a0NOT\u00a0NULL,\u00a0Name\u00a0nvarchar(100)\u00a0NOT\u00a0NULL,\u00a0NodeType\u00a0nvarchar(100)\u00a0NOT\u00a0NULL,\u00a0PRIMARY\u00a0KEY\u00a0(FileName,\u00a0NodeId))\nGO\nIF\u00a0NOT\u00a0EXISTS\u00a0(SELECT\u00a0*\u00a0FROM\u00a0sys.tables\u00a0where\u00a0object_id\u00a0=\u00a0OBJECT_ID('$StagingSchema.Edge'))\n\u00a0\u00a0\u00a0\u00a0CREATE\u00a0TABLE\u00a0$StagingSchema.Edge\u00a0(Filename\u00a0nvarchar(200)\u00a0NOT\u00a0NULL,\u00a0FromNodeId\u00a0int\u00a0NOT\u00a0NULL,\u00a0ToNodeId\u00a0int\u00a0NOT\u00a0NULL,\u00a0EdgeType\u00a0varchar(100)\u00a0NULL)\nGO\n--delete\u00a0previous\u00a0data\u00a0staged\u00a0from\u00a0this\u00a0filename\u00a0to\u00a0let\u00a0this\u00a0run\u00a0repeatedly\nDELETE\u00a0FROM\u00a0$StagingSchema.Node\u00a0WHERE\u00a0Filename\u00a0=\u00a0'$BaseName'\nGO\nDELETE\u00a0FROM\u00a0$StagingSchema.Edge\u00a0WHERE\u00a0Filename\u00a0=\u00a0'$BaseName'\nGO\n--Nodes\"\n\u00a0\u00a0\u00a0\u00a0#Write\u00a0the\u00a0start\u00a0of\u00a0the\u00a0file,\u00a0clobbering\u00a0existing\u00a0file\u00a0\n\u00a0\u00a0\u00a0\u00a0$WriteThis\u00a0|\u00a0Out-File\u00a0-FilePath\u00a0$OutputFile\u00a0#-NoClobber\n\u00a0\u00a0\u00a0\u00a0$RowCount\u00a0=\u00a00;\u00a0$Section\u00a0=\u00a0\"Nodes\";\u00a0#RowCount\u00a0is\u00a0just\u00a0for\u00a0progress\u00a0monitoring\u00a0if\u00a0it\u00a0is\u00a0a\u00a0very\u00a0large\u00a0file.\u00a0\n\u00a0\u00a0\u00a0\u00a0#The\u00a0first\u00a0section\u00a0of\u00a0the\u00a0TGF\u00a0file\u00a0is\u00a0the\u00a0nodes.\u00a0The\u00a0second\u00a0is\u00a0edges,\u00a0denoted\u00a0by\u00a0a\u00a0row\u00a0with\u00a0\"#\"\n\u00a0\u00a0\u00a0\u00a0#read\u00a0in\u00a0the\u00a0file,\u00a0row\u00a0by\u00a0row\n\u00a0\u00a0\u00a0\u00a0$reader\u00a0=\u00a0[System.IO.File]::OpenText($Request)\n\u00a0\u00a0\u00a0\u00a0while\u00a0($null\u00a0-ne\u00a0($line\u00a0=\u00a0$reader.ReadLine()))\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#in\u00a0the\u00a0TGF\u00a0file,\u00a0it\u00a0has\u00a0nodes\u00a0first,\u00a0then\u00a0edges.\u00a0This\u00a0changes\u00a0us\u00a0to\u00a0edges\u00a0when\u00a0we\u00a0reach\u00a0#\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if\u00a0($line\u00a0-eq\u00a0'#')\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if\u00a0($VerboseMode)\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"Changing\u00a0to\u00a0Edges\"\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$Section\u00a0=\u00a0\"Edges\"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$WriteThis\u00a0=\u00a0\"`r`n--Edges\"\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$WriteThis\u00a0|\u00a0Out-File\u00a0-FilePath\u00a0$OutputFile\u00a0-Append\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0else\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$line\u00a0=\u00a0$line\u00a0+\u00a0\"\u00a0\"\u00a0*\u00a0100\u00a0#added\u00a0100\u00a0space\u00a0characters\u00a0to\u00a0make\u00a0the\u00a0substring\u00a0easier\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if\u00a0($Section\u00a0-eq\u00a0\"Nodes\")\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#pull\u00a0the\u00a0node\u00a0name\u00a0out\u00a0of\u00a0the\u00a0string\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$NodeName\u00a0=\u00a0$line.Substring($line.indexOf('\u00a0'),\u00a0100\u00a0).trim()\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#Make\u00a0name\u00a0safe\u00a0for\u00a0output\u00a0if\u00a0it\u00a0has\u00a0an\u00a0'\u00a0in\u00a0it\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$NodeName\u00a0=\u00a0$NodeName.Replace(\"'\",\"''\");\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$NodeType\u00a0=\u00a0$NodeType.Replace(\"'\",\"''\");\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#write\u00a0the\u00a0Node\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$WriteThis\u00a0=\u00a0\"INSERT\u00a0INTO\u00a0$StagingSchema.Node\u00a0(FileName,\u00a0NodeId,Name,NodeType)\u00a0`r`n\"\u00a0+\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"VALUES\u00a0(\u00a0'$BaseName',\"\u00a0+\u00a0$line.Substring(0,\u00a0$line.indexOf('\u00a0')).trim()\u00a0+\u00a0\",'\"\u00a0+\u00a0$NodeName\u00a0+\u00a0\"','\"\u00a0+\u00a0$NodeType\u00a0+\u00a0\"');\"\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$WriteThis\u00a0|\u00a0Out-File\u00a0-FilePath\u00a0$OutputFile\u00a0-Append\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0else\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#Write\u00a0the\u00a0Edge\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#Parsing\u00a0this\u00a0line\u00a0is\u00a0not\u00a0as\u00a0simple\u00a0as\u00a0using\u00a0split\u00a0because\u00a0the\u00a0third\u00a0part\u00a0of\u00a0the\u00a0line\u00a0is\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#the\u00a0name\u00a0and\u00a0the\u00a0name\u00a0can\u00a0have\u00a0spaces\u00a0(and\u00a0the\u00a0fields\u00a0are\u00a0split\u00a0on\u00a0space)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#From\u00a0Node\u00a0is\u00a0Simple,\u00a0char\u00a00\u00a0to\u00a0first\u00a0space\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$WriteFromNodeId\u00a0=\u00a0$line.Substring(0,\u00a0$line.indexOf('\u00a0')).trim()\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#get\u00a0the\u00a0value\u00a0after\u00a0the\u00a0from\u00a0node.\u00a0it\u00a0may\u00a0or\u00a0many\u00a0not\u00a0have\u00a0additional\u00a0information\u00a0after\u00a0it\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$AfterFromNode\u00a0=\u00a0$line.Substring($line.indexOf('\u00a0')\u00a0+\u00a01,\u00a0100\u00a0)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#pad\u00a0for\u00a0the\u00a0substring\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$AfterFromNode\u00a0=\u00a0$AfterFromNode\u00a0+\u00a0\"\u00a0\"\u00a0*\u00a0100\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#Get\u00a0the\u00a0numeric\u00a0surrogate\u00a0of\u00a0the\u00a0from\u00a0node\u00a0for\u00a0the\u00a0insert\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$WriteToNodeId\u00a0=\u00a0$AfterFromNode.Substring(0,\u00a0$AfterFromNode.indexOf('\u00a0')).trim()\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#Fetch\u00a0any\u00a0additional\u00a0data\u00a0from\u00a0the\u00a0string,\u00a0and\u00a0trim\u00a0it\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$AdditionalInfo\u00a0=\u00a0$AfterFromNode.Substring($line.indexOf('\u00a0')\u00a0+\u00a01,\u00a0100\u00a0).Trim()\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#if\u00a0the\u00a0data\u00a0has\u00a0no\u00a0length,\u00a0set\u00a0it\u00a0to\u00a0NULL\u00a0in\u00a0the\u00a0output\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if\u00a0($AdditionalInfo.Length\u00a0-eq\u00a00)\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$AdditionalInfo\u00a0=\u00a0'NULL'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ELSE\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#otherwise,\u00a0add\u00a0single\u00a0quotes\u00a0and\u00a0double\u00a0up\u00a0single\u00a0quotes\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$AdditionalInfo\u00a0=\u00a0\"'\"\u00a0+\u00a0$AdditionalInfo.Replace(\"'\",\"''\")\u00a0+\u00a0\"'\"\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#double\u00a0up\u00a0single\u00a0quotes\u00a0and\u00a0surround\u00a0by\u00a0a\u00a0'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$DefaultEdgeType\u00a0=\u00a0\"'\"\u00a0+\u00a0$EdgeType.Replace(\"'\",\"''\")\u00a0+\u00a0\"'\"\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#Edgetype\u00a0is\u00a0defaulted\u00a0to\u00a0the\u00a0edge\u00a0name,\u00a0or\u00a0the\u00a0default\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$WriteEdgeType\u00a0=\u00a0\"COALESCE\u00a0(\"\u00a0+\u00a0$AdditionalInfo.Trim()\u00a0+\u00a0\",\"\u00a0+\u00a0$DefaultEdgeType.Trim()\u00a0+\u00a0\")\";\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#The\u00a0script\u00a0to\u00a0output\u00a0the\u00a0edge\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$WriteThis\u00a0=\u00a0\"INSERT\u00a0INTO\u00a0$StagingSchema.Edge\u00a0(FileName,\u00a0FromNodeId,\u00a0ToNodeId,\u00a0EdgeType)\u00a0`r`n\"\u00a0+\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"VALUES\u00a0('$BaseName',\"\u00a0+\u00a0$WriteFromNodeId\u00a0+\u00a0\",\"\u00a0+\u00a0$WriteToNodeId.trim()\u00a0+\u00a0\",\u00a0\"\u00a0\u00a0+\u00a0\u00a0$WriteEdgeType\u00a0+\u00a0\");\"\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0#write\u00a0this\u00a0line\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$WriteThis\u00a0|\u00a0Out-File\u00a0-FilePath\u00a0$OutputFile\u00a0-Append\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if\u00a0($VerboseMode)\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if\u00a0((($Rowcount\u00a0+\u00a01)\u00a0%\u00a0$VerboseCountAnnounce)\u00a0-eq\u00a00)\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"Processed\u00a0\"\u00a0+\u00a0$RowCount\u00a0+\u00a0\"\u00a0lines\u00a0in\u00a0the\u00a0file\"\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$RowCount++;\u00a0\n\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0$WriteThis\u00a0=\u00a0\n\u00a0\u00a0\u00a0\u00a0\"\nGO\n--Queries\u00a0to\u00a0output\u00a0the\u00a0nodes\u00a0that\u00a0have\u00a0been\u00a0created\nSELECT\u00a0*\nFROM\u00a0\u00a0\u00a0NodeStaging.Node\nWHERE\u00a0\u00a0Node.Filename\u00a0=\u00a0'$BaseName'\nSELECT\u00a0*\nFROM\u00a0\u00a0\u00a0NodeStaging.Edge\nWHERE\u00a0\u00a0Edge.Filename\u00a0=\u00a0'$BaseName'\nORDER\u00a0BY\u00a0Edge.FromNodeId,Edge.ToNodeId\nGO\n\u00a0\u00a0\u00a0\u00a0\"\n\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0#Write\u00a0the\u00a0start\u00a0of\u00a0the\u00a0file,\u00a0clobbering\u00a0existing\u00a0file\u00a0\n\u00a0\u00a0\u00a0\u00a0$WriteThis\u00a0|\u00a0Out-File\u00a0-FilePath\u00a0$OutputFile\u00a0-Append\n\u00a0\u00a0\u00a0\u00a0if\u00a0($VerboseMode)\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"Finished\u00a0file:\"\u00a0+\u00a0$Request\u00a0+\u00a0\"\u00a0with\u00a0\"\u00a0+\u00a0$RowCount\u00a0+\u00a0\"\u00a0lines.\u00a0Wrote\u00a0script\u00a0to\u00a0\"\u00a0+\u00a0$OutputFile\n\u00a0\u00a0\u00a0\u00a0}\n}\n\nif\u00a0($VerboseMode)\u00a0{\n\u00a0\u00a0\u00a0\u00a0\"\"\n\u00a0\u00a0\u00a0\u00a0\"Import\u00a0End\u00a0time:\u00a0\u00a0$(Get-Date)\"\u00a0\n\u00a0\u00a0\u00a0\u00a0\"\"\n}<\/pre>\n
Hop this is a help as you start using graph tables also.\u00a0 If you are keen to export a graph,\u00a0 Arvind Shyamsundar has the basis of that code in a project that was done to visualize Pass Summit sessions in 2017, to output the graph as a GRAPHML file. (https:\/\/deep.data.blog\/2017\/11\/03\/how-we-did-it-pass-2017-summit-session-similarity-using-sql-graph-and-python\/<\/a>) .<\/p>\n","protected":false},"excerpt":{"rendered":"
Design a graph visually in yEd (a free graph modeling tool), export it as Trivial Graph Format, and import the nodes and edges into SQL Server graph tables. Includes the yEd setup, TGF format explanation, and T-SQL for importing into SQL Server node and edge tables without hand-typing dozens of INSERTs.…<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-89726","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89726","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=89726"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89726\/revisions"}],"predecessor-version":[{"id":110402,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89726\/revisions\/110402"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=89726"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=89726"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=89726"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=89726"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}