In SQL Server, it’s possible to insert data via a table-valued function (TVF).

By this, I mean insert rows in the underlying tables that the function queries.

To insert a new row via a TVF, simply use the same T-SQL INSERT syntax that you’d use if inserting data directly into the table.

Example

Here’s a quick example to demonstrate.

The Function

Here’s a quick function that selects basic data from a table.

CREATE FUNCTION dbo.udf_Cats_ITVF()
    RETURNS TABLE
AS
RETURN (
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats
    );
GO

This function selects all rows from a table.

I could use this function to insert a new row into that table.

Select Data with the Function

Here’s how we’d normally invoke the function with a SELECT statement. This enables us to see the current data in the underlying table.

SELECT * FROM udf_Cats_ITVF();

Result:

+---------+-----------+------------+
| CatId   | CatName   | Phone      |
|---------+-----------+------------|
| 1       | Garfield  | 9871237654 |
| 2       | Felix     | 8871237651 |
| 3       | Tom       | 7871237652 |
| 4       | Fetch     | 6871237653 |
+---------+-----------+------------+

So there are four rows.

Insert a Row via the Function

Now let’s use the TVF to add a new row.

INSERT INTO udf_Cats_ITVF() 
VALUES ('Scratchy', '1111111111');

SELECT * FROM udf_Cats_ITVF();

Result:

+---------+-----------+------------+
| CatId   | CatName   | Phone      |
|---------+-----------+------------|
| 1       | Garfield  | 9871237654 |
| 2       | Felix     | 8871237651 |
| 3       | Tom       | 7871237652 |
| 4       | Fetch     | 6871237653 |
| 5       | Scratchy  | 1111111111 |
+---------+-----------+------------+

So I successfully inserted a new cat using the inline table-valued function.