Skip to main content

INSERT

Creates a new instance or instances of a resource.

See also:
[ StackQL Resource Hierarchy ]


Syntax

insertStatement::=

INSERTawaitQueryHintIGNOREINTOmultipartIdentifier'('fieldList')'selectStatementVALUES'('fieldList')'RETURNINGfieldList';'

 
 

INSERT [ /*+ AWAIT  */ ] [ IGNORE ] [ INTO ] <multipartIdentifier>
( field_name [, field_name] ... )
{ VALUES ( value [, value] ... ) | <selectExpression> }
[ RETURNING field_name [, field_name] ... ];

RETURNING Clause

The RETURNING clause allows you to retrieve data from the INSERT operation response. This is particularly useful when:

  • The API returns important information like generated IDs, status codes, or other metadata
  • You need to capture the response data for further processing
  • You want to verify the operation result

The fields specified in the RETURNING clause will be deserialized from the API response and displayed in the result set.


Examples

Basic INSERT statement

Run an INSERT statement to create a Compute Engine Disk resource. This is a non blocking (asynchronous) invocation.

-- Create a Compute Engine Disk resource
INSERT INTO google.compute.disks (project, zone, name, sizeGb)
SELECT 'stackql-demo',
'australia-southeast1-a',
'test10gbdisk', 10;

INSERT statement (blocking)

Run an INSERT statement to create a Compute Engine Disk resource. This is a blocking (synchronous) invocation.

-- Create a Compute Engine Disk resource
INSERT /*+ AWAIT */ INTO google.compute.disks (project, zone, name, sizeGb)
SELECT 'stackql-demo',
'australia-southeast1-a',
'test10gbdisk', 10;

INSERT statement with SELECT

Run an INSERT statement with SELECT.

-- Create a Azure Network Interface
INSERT INTO azure.network.interfaces
(
resourceGroupName,
subscriptionId,
networkInterfaceName,
data__location,
data__properties
)
SELECT
'vmss-flex',
'0123456789',
'vmss-flex-vnet-nic01-manual-' || '1',
'eastus',
json_replace(json_remove(properties,
'$.allowPort25Out',
'$.auxiliarySku',
'$.provisioningState',
'$.resourceGuid',
'$.macAddress',
'$.vnetEncryptionSupported',
'$.enableIPForwarding',
'$.defaultOutboundAccess',
'$.primary',
'$.virtualMachine',
'$.hostedWorkloads',
'$.tapConfigurations',
'$.nicType',
'$.auxiliaryMode',
'$.ipConfigurations[0].id',
'$.ipConfigurations[0].etag',
'$.ipConfigurations[0].type',
'$.ipConfigurations[0].properties.provisioningState',
'$.ipConfigurations[0].properties.privateIPAddress',
'$.ipConfigurations[0].properties.privateIPAllocationMethod',
'$.ipConfigurations[0].properties.primary',
'$.ipConfigurations[0].properties.privateIPAddressVersion'
),
'$.ipConfigurations[0].name',
'vmss-flex-vnet-nic01-defaultIpConfiguration'
)
FROM
azure.network.interfaces
WHERE subscriptionId = '0123456789'
AND resourceGroupName = 'vmss-flex'
AND networkInterfaceName = 'vmss-nic-01';

INSERT statement with RETURNING clause

Run an INSERT statement and retrieve specific fields from the API response.

-- Execute a Snowflake SQL statement and return the response data
INSERT INTO snowflake.sqlapi.statements (
data__statement,
data__timeout,
data__database,
data__schema,
"User-Agent",
endpoint
)
SELECT
'SELECT
customer_id,
customer_name,
email,
credit_card_number,
purchase_amount,
purchase_date
FROM CUSTOMER_DATA',
10,
'MY_DATABASE',
'MY_SCHEMA',
'User-Agent',
'ABCDEF-XY12345'
RETURNING code, sqlState, message, data;

Result:

|--------|----------|--------------------------------|-----------------------------------------------------------------------------|
| code | sqlState | message | data |
|--------|----------|--------------------------------|-----------------------------------------------------------------------------|
| 090001 | 00000 | Statement executed | [["1","John |
| | | successfully. | Smith","john.smith@example.com","***MASKED***","199.99","20262"],["1","John |
| | | | Smith","john.smith@example.com","***MASKED***","199.99","20263"]] |
|--------|----------|--------------------------------|-----------------------------------------------------------------------------|