Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server 2017 (14.x) and later versions
Azure SQL Database
Azure SQL Managed Instance
SQL database in Microsoft Fabric
Adds one or more rows to a node
or edge
table in SQL Server.
Transact-SQL syntax conventions
The syntax for inserting into a Node table is the same as for a regular table.
[ WITH <common_table_expression> [ ,...n ] ]
INSERT
{
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ]
{ <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
{
[ (column_list) ] | [(<edge_table_column_list>)]
[ <OUTPUT Clause> ]
{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ]
| derived_table
| execute_statement
| <dml_table_source>
| DEFAULT VALUES
}
}
}
[;]
<object> ::=
{
[ server_name . database_name . schema_name .
| database_name .[ schema_name ] .
| schema_name .
]
node_table_name | edge_table_name
}
<dml_table_source> ::=
SELECT <select_list>
FROM ( <dml_statement_with_output_clause> )
[AS] table_alias [ ( column_alias [ ,...n ] ) ]
[ WHERE <on_or_where_search_condition> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]
<on_or_where_search_condition> ::=
{ <search_condition_with_match> | <search_condition> }
<search_condition_with_match> ::=
{ <graph_predicate> | [ NOT ] <predicate> | ( <search_condition> ) }
[ AND { <graph_predicate> | [ NOT ] <predicate> | ( <search_condition> ) } ]
[ ,...n ]
<search_condition> ::=
{ [ NOT ] <predicate> | ( <search_condition> ) }
[ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ]
[ ,...n ]
<graph_predicate> ::=
MATCH( <graph_search_pattern> [ AND <graph_search_pattern> ] [ , ...n] )
<graph_search_pattern>::=
<node_alias> { { <-( <edge_alias> )- | -( <edge_alias> )-> } <node_alias> }
<edge_table_column_list> ::=
($from_id, $to_id, [column_list])
Note
This article describes arguments related to SQL graph. For a full list and description of supported arguments in INSERT statement, see INSERT TABLE (Transact-SQL). For standard Transact-SQL statements, see INSERT TABLE (Transact-SQL).
Is an optional keyword that can be used between INSERT
and the target table.
search_condition_with_match
MATCH
clause can be used in a subquery while inserting into a node or edge table. For MATCH
statement syntax, see GRAPH MATCH (Transact-SQL).
graph_search_pattern
Search pattern provided to MATCH
clause as part of the graph predicate.
edge_table_column_list
Users must provide values for $from_id
and $to_id
while inserting into an edge. An error is returned if a value isn't provided or NULLs are inserted into these columns.
$node_id
column are automatically generated.$from_id
and $to_id
columns.$from_id
and $to_id
can then be extracted from the $node_id
column of the node table and inserted as edges.INSERT permission is required on the target table.
INSERT permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Members of the sysadmin, db_owner, and the db_securityadmin roles, and the table owner can transfer permissions to other users.
To execute INSERT with the OPENROWSET function BULK option, you must be a member of the sysadmin fixed server role or of the bulkadmin fixed server role.
The following example creates a Person
node table and inserts two rows into that table.
-- Create person node table
CREATE TABLE dbo.Person (ID integer PRIMARY KEY, name varchar(50)) AS NODE;
-- Insert records for Alice and John
INSERT INTO dbo.Person VALUES (1, 'Alice');
INSERT INTO dbo.Person VALUES (2,'John');
The following example creates a friend
edge table and inserts an edge into the table.
-- Create friend edge table
CREATE TABLE dbo.friend (start_date DATE) AS EDGE;
-- Create a friend edge, that connect Alice and John
INSERT INTO dbo.friend VALUES ((SELECT $node_id FROM dbo.Person WHERE name = 'Alice'),
(SELECT $node_id FROM dbo.Person WHERE name = 'John'), '9/15/2011');
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register today