Skip to main content

INSERT (bSQL) | Blockpoint Docs

Adds one or more rows to a blockchain. For examples, see Examples.

bSQL Syntax Conventions

Syntax​

-- Syntax for INSERT - Blockchain Mutation 
  
INSERT   
{  
    <object>
    {  
        [ ( column_list ) ]   
        { VALUES ( [ ,...n ] ) [ , ( [ ,...n ] )... ]     
        }  
        [ <output_clause> ]  
    }  
}  
  
<object> ::=  
{   
   database_name . blockchain_name   
   | blockchain_name    
}  
  
<output_clause> ::= 
    OUTPUT { column_name [ ,...n ] }

-- Syntax for INSERT INTO - Blockchain Mutation

INSERT INTO <target_blockchain>  [ ( column_list ) ]  
     <select_statement>

<target_blockchain> ::= 
{ database_name.schema_name.blockchain_name |
 schema_name.blockchain_name |
 blockchain_name }

<select_statement> ::=
 SELECT *select_list*
 [ FROM *blockchain_source* | <import_statement> ] [ WHERE *search_condition* ]  
 [ GROUP BY *group_by_expression* ]  
 [ HAVING *search_condition* ]  
 [ ORDER BY *order_expression* [ ASC | DESC ] ]  
 [ FILTER *filter_expression* ] 

<import_statment> ::=
   IMPORT = <filepath> 
   [HEADER]
   **schema definition**

Arguments​

INTO
Is an optional keyword that can be used between INSERT and the target blockchain.

database_name
Is the name of the database.

blockchain_name
Is the name of the blockchain that is to receive the data.

(column_list)
Is a list of one or more columns in which to insert data. column_list must be enclosed in parentheses and delimited by commas.

If a column is not in column_list, the schema must be able to provide a value based on the definition of the column otherwise, the row cannot be loaded. The system automatically provides a value for the column if the column:

  • Has an autoincrement property. The next incremented identity value is used.
  • Has a default. The default value for the column is used.
  • Has a timestamp data type. The current timestamp value is used.
  • Is nullable. A null value is used.

OUTPUT Clause
Returns inserted rows as part of the insert operation.

VALUES

  • Introduces the list or lists of data values to be inserted. There must be one data value for each column in column_list, if specified, or in the blockchain. The value list must be enclosed in parentheses.

  • If the values in the Value list are not in the same order as the columns in the blockchain or do not have a value for each column in the blockchain, column_list must be used to explicitly specify the column that stores each incoming value.

  • If the schema contains default, autoincrement, or null columns the columns must be specified to avoid ambiguous insertions.

INSERT INTO

Inserts the results of SELECT statement into the target.

  • It must be a blockchain.
  • The resulting data must be compatible with the blockchain schema.

Data Types​

When you insert rows, consider the following data type behavior:

  • If a value is being loaded into columns with a string or byte array data type, the padding or truncation of trailing blanks (spaces for PACKED string, zeros for PACKED byte array).
Data typeDefault operation
stringPack the value to the size of the string inserted.
byte arrayPack the byte array to its length.

Limitations and Restrictions​

  • When you insert values into a blockchain and not all values for all columns are specified, you must identify the columns to which the specified values are to be inserted.
  • INSERT queries that use SELECT with ORDER BY to populate rows guarantees the order in which the rows are inserted.

Permissions​

INSERT or WRITE permissions are required on the target blockchain.

Both INSERT and WRITE permissions default to members of the admin and developer fixed database roles. Members of the admin fixed database role can transfer permissions to other users.

When inserting into a blockchain container from a select statement the user must also have READ permissions on the query.

Examples​

|Category|Featured syntax elements| |--------------|------------------------------| |[Basic syntax](#BasicSyntax)|INSERT • blockchain value constructor| |[Handling column values](#ColumnValues)| default values • auto increment| |[Other Source](#OtherSources)|INSERT INTO| ### Basic SyntaxExamples in this section demonstrate the basic functionality of the INSERT statement using the minimum required syntax. #### A. Inserting a single row of data The following example inserts one row into the `financial.companies` blockchain in the [demo database](/docs/demo_database). The columns in this blockchain are `symbol`, `name`, and `sector`. Because values for all columns are supplied and are listed in the same order as the columns in the blockchain, the column names do not have to be specified in the column list*.* ```sql INSERT financial.companies VALUES ("WPG", "Washington Prime Group", "Trust") ``` #### B. Inserting multiple rows of data The following example inserts two rows into the `financial.companies` blockchain in the [demo database](/docs/demo_database) in a single INSERT statement. Because values for all columns are supplied and are listed in the same order as the columns in the blockchain, the column names do not have to be specified in the column list.
INSERT financial.companies
VALUES ("MYL", "Mylan", "Health Care"), ("POLA", "Polar Power, Inc", "Industrial")

C. Inserting multiple rows of data with output​

The following example inserts two rows into the financial.companies blockchain in the demo database in a single INSERT statement and request a view of the resulting insertion using the OUTPUT keyword on the rows symbol and sector. 

INSERT financial.companies
VALUES ("MYL", "Mylan", "Health Care"), ("POLA", "Polar Power, Inc", "Industrial")
OUTPUT symbol, sector

The output would look like:

[0] symbol: MYL sector: Health Care
[1] symbol: POLA    sector: Industrial

D. Inserting data that is not in the same order as the blockchain columns​

The following example uses a column list to explicitly specify the values that are inserted into each column. The column order in the financial.companies blockchain in the demo database database is symbol, name, and sector however, the columns are not listed in that order in column_list. 

INSERT financial.companies (name, symbol,
    sector)  
VALUES ("Better Online Solutions Ltd.", "BOSC", "Information Technology")

Handling Column Values​

Examples in this section demonstrate methods of inserting values into columns that are defined with an AUTO INCREMENT property, DEFAULT value. #### E. Inserting data into a blockchain with columns that have default values The following example shows inserting rows into a blockchain with columns that automatically generate a value or have a default value. `sector` has the default value "Undefined", and defaults to this value when another isn't specified. In order to this type of insertion the user must specify the columns. ```sql INSERT financial.companies (symbol, name) VALUES ("SACH", "Sachem Capital Corp.") ``` #### F. Inserting data into a blockchain with an auto incremented value The following example shows how to insert data into a blockchain with an auto incremented column. AutoIncrement is used for columns of numeric types, starting at 0 and incrementing after each insertion. ```sql CREATE BLOCKCHAIN financial.ipos TRADITIONAL (id INT8 PRIMARY AUTO INCREMENT, symbol STRING PACKED PRIMARY)

INSERT financial.ipos (symbol) VALUES ("MYL"), ("POLA")

  After these two insertions the blockchain would look like:
  ```sql
ID [INT8]   SYMBOL [STRING] 
0   MYL 
1   POLA

Other Sources​

A. Inserting data from another blockchain​

The following example inserts all the data financial.pricing blockchain in the demo database to a new blockchain financial.current_price. 

CREATE BLOCKCHAIN financial.current_price TRADITIONAL
 (symbol STRING PACKED PRIMARY, price FLOAT32)

INSERT INTO financial.current_price
SELECT symbol, price FROM financial.pricing

A. Inserting data from a file​

The following example inserts all the data from constituents-fianancial.csv in the examples directory into the financial.raw blockchain. The schema defined in the blockchain must correspond to the schema defined for the import, and since the csv file has a header HEADER is specified.

CREATE BLOCKCHAIN financial.raw
(symbol STRING PADDED PRIMARY, name STRING PADDED, sector STRING PADDED, price FLOAT32, 
dividend_yield FLOAT32, price_earning FLOAT32, earning_share FLOAT32, book_value FLOAT32, 
52_week_low FLOAT32, 52_week_high FLOAT32, market_cap FLOAT64, ebitda FLOAT64, sales FLOAT64,
price_book_value FLOAT32, sec_filings STRING PADDED)

INSERT INTO financial.raw 
SELECT * FROM IMPORT = "exampls/constituents-financials.csv" HEADER
(symbol STRING PADDED PRIMARY, name STRING PADDED,
 sector STRING PADDED, price FLOAT32, price_earning FLOAT32,
 dividend_yield FLOAT32, earning_share FLOAT32,  book_value FLOAT32,
 52_week_low float32, 52_week_high float32, market_cap float64,
 ebitda FLOAT64, sales FLOAT64, price_book_value FLOAT32,
 sec_filings STRING PADDED)

Important File types supported are currently .json, .csv, and .html files.

See Also​