INSERT(SQL 图形)INSERT (SQL Graph)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server 中的 nodeedge 表格添加一行或多行。Adds one or more rows to a node or edge table in SQL ServerSQL Server.

备注

有关标准 TRANSACT-SQL 语句,请参阅 INSERT TABLE (Transact-SQL)For standard Transact-SQL statements, see INSERT TABLE (Transact-SQL).

文章链接图标 Transact-SQL 语法约定Article link icon Transact-SQL Syntax Conventions

插入节点表语法INSERT Into Node Table Syntax

插入节点表的语法与插入常规表的语法相同。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])

参数Arguments

本文档介绍与 SQL 图形相关的参数。This document describes arguments related to SQL graph. 有关 INSERT 语句中受支持参数的完整列表和说明,请参阅 INSERT TABLE (Transact-SQL)For a full list and description of supported arguments in INSERT statement, see INSERT TABLE (Transact-SQL)

INTOINTO
一个可选的关键字,可将它用在 INSERT 和目标表之间。Is an optional keyword that can be used between INSERT and the target table.

search_condition_with_match search_condition_with_match
插入节点或边缘表时,可在子查询中使用 MATCH 子句。MATCH clause can be used in a subquery while inserting into a node or edge table. 有关 MATCH 语句的语法,请参阅 GRAPH MATCH (Transact-SQL)For MATCH statement syntax, see GRAPH MATCH (Transact-SQL)

graph_search_pattern graph_search_pattern
提供给 MATCH 子句作为图形谓词的一部分的搜索模式。Search pattern provided to MATCH clause as part of the graph predicate.

edge_table_column_list edge_table_column_list
插入边时,用户必须提供 $from_id$to_id 的值。Users must provide values for $from_id and $to_id while inserting into an edge. 如果未提供值或这些列中插入了 NULL,则会返回错误。An error will be returned if a value isn't provided or NULLs are inserted into these columns.

RemarksRemarks

插入节点表与插入任何关系表相同。Inserting into a node is same as inserting into any relational table. 会自动生成 $node_id 列的值。Values for the $node_id column are automatically generated.

插入边缘表时,用户必须提供 $from_id$to_id 列的值。While inserting into an edge table, users must provide values for $from_id and $to_id columns.

节点表的 BULK 插入与关系表的 BULK 插入相同。BULK insert for node table is the same as for a relational table.

批量插入边缘表之前,必须导入节点表。Before bulk inserting into an edge table, the node tables must be imported. 然后才可从节点表的 $node_id 列中提取 $from_id$to_id 的值,并将其作为边插入。Values for $from_id and $to_id can then be extracted from the $node_id column of the node table and inserted as edges.

权限Permissions

需要对目标表具有 INSERT 权限。INSERT permission is required on the target table.

默认情况下,将 INSERT 权限授予 sysadmin 固定服务器角色成员、db_owner 和 db_datawriter 固定数据库角色成员以及表所有者 。INSERT permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. sysadmin、db_owner 和 db_securityadmin 角色成员和表所有者可以将权限转让给其他用户 。Members of the sysadmin, db_owner, and the db_securityadmin roles, and the table owner can transfer permissions to other users.

若要使用 OPENROWSET 函数 BULK 选项执行 INSERT,必须是 sysadmin 固定服务器角色成员或 bulkadmin 固定服务器角色成员 。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.

示例Examples

A.A. 插入节点表Insert into node table

以下示例创建 Person 节点表,并向该表插入 2 行。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');

B.B. 插入边缘表Insert into edge table

以下示例创建友元边缘表,并向表中插入一条边。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');

另请参阅See Also

INSERT TABLE (Transact-SQL) INSERT TABLE (Transact-SQL)
使用 SQL Server 2017 进行图形处理Graph processing with SQL Server 2017