How can I get the identity of the last row inserted into the snowflake database?

8.23K viewsSQL
0

How can I get the identity of the last row inserted into the snowflake database?

Alejandro Penzini Answered question May 11, 2023
0

In Snowflake, you can use the LAST_INSERT_ID() function to retrieve the identity of the last row that was inserted into a table with an auto-incrementing column.

Here's an example of how to use this function:

Create a table with an auto-incrementing column:
sql
Copy code
CREATE TABLE my_table (
id INTEGER AUTOINCREMENT,
name VARCHAR(50)
);
Insert a new record into the table:
sql
Copy code
INSERT INTO my_table(name) VALUES('John Doe');
Retrieve the identity of the last inserted row using the LAST_INSERT_ID() function:
sql
Copy code
SELECT LAST_INSERT_ID();
This will return the identity of the last row that was inserted into the my_table table.

Note that the AUTOINCREMENT keyword specifies that the id column should be automatically incremented for each new row that is inserted into the table. If you insert a record without specifying a value for the id column, Snowflake will automatically generate a unique value for the column.

Also, note that the LAST_INSERT_ID() function only works for the current session. If you perform another INSERT operation in a different session, the function will return the identity of the last row inserted in that session, not the current session.

nam_rata Posted new comment May 16, 2024

When I tried to use it, it is saying ‘Unknown function LAST_INSERT_ID()’

Maximize Your Data Potential With ITS

Feedback on Q&A