Επεξεργασία

NODE_ID_FROM_PARTS (Transact-SQL)

Applies to: SQL Server 2017 (14.x) and later versions Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

In SQL Graph tables, the NODE_ID_FROM_PARTS function returns the character representation (JSON) of the node ID for a given object ID and graph ID.

Syntax

NODE_ID_FROM_PARTS ( object_id, graph_id )

Arguments

object_id

An int representing the object ID for the node table.

graph_id

A bigint value for the graph ID for a node.

Return value

Returns an nvarchar(1000) character representation (JSON) of the node ID. The return value can be NULL if any of the supplied arguments are invalid.

Remarks

  • The character representation (JSON) of the node ID returned by NODE_ID_FROM_PARTS is an implementation specific detail, and is subject to change.
  • NODE_ID_FROM_PARTS is the only supported way to construct a suitable character representation of the node ID.
  • NODE_ID_FROM_PARTS is useful for bulk inserting of data into a graph table, when the source data has a suitable natural or surrogate key with an integer data type.
  • The value returned from NODE_ID_FROM_PARTS can be used to populate the $node_id column in a node table. It can also be used to populate the $from_id / $to_id columns in an edge table.
  • For NODE_ID_FROM_PARTS to return valid character representation (JSON) of a node ID, the object_id parameter must correspond to an existing node table. The graph_id parameter can be any valid integer, but it need not exist in that node table. If any of these checks fail, NODE_ID_FROM_PARTS returns NULL.

Graph tables were introduced in SQL Server 2017. The NODE_ID_FROM_PARTS function isn't available in SQL Server 2016 or in Fabric Data Warehouse.

Examples

The following example uses the OPENROWSET Bulk Rowset Provider to retrieve the ID and name columns from a CSV file stored on an Azure Storage account. It then uses NODE_ID_FROM_PARTS to create the appropriate character representation of $node_id for eventual (bulk) insert into the Person node table. This transformed data is then (bulk) inserted into the Person node table.

INSERT INTO Person($node_id, ID, [name])
SELECT NODE_ID_FROM_PARTS(OBJECT_ID('Person'), ID) as node_id, ID, [name]
FROM OPENROWSET (BULK 'person_0_0.csv',
    DATA_SOURCE = 'staging_data_source',
    FORMATFILE = 'format-files/person.xml',
    FORMATFILE_DATA_SOURCE = 'format_files_source',
    FIRSTROW = 2) AS staging_data;
;

Related content

  • Last updated on