INSERT
Creates a new instance or instances of a resource.
See also:
[ StackQL Resource Hierarchy ]
Syntax
insertStatement::=
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"]] |
|--------|----------|--------------------------------|-----------------------------------------------------------------------------|