SQL Server provides the Output keyword to return values after inserting. A common task is to insert data and return it immediately so we don't have to make multiple sql requests. In this article, we will learn how to use Output in Sql Server.
The basic syntax of a Output is as follows:
INSERT INTO [table] (name) OUTPUT Inserted.ID VALUES('bob');
For this, we will be using docker. This is recommended for more than just using SQL Server. To find how to install docker go here: https://docs.docker.com/engine/install/
Now create a file called
docker-compose.yml and add the following.
version: "3.9" services: db: image: "mcr.microsoft.com/mssql/server" ports: - 1433:1433 environment: SA_PASSWORD: "Your_password123" ACCEPT_EULA: "Y"
Open a terminal and go to the folder the file is located. Then run the following.
If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.
Let's start by creating a simple Employee table.
CREATE TABLE employees ( first_name VARCHAR (50) NOT NULL, last_name VARCHAR (50) NOT NULL, );
Now we can insert an employee and use the OUTPUT keyword to return the result immediately.
insert into employees (first_name, last_name) output inserted.* values ('keith', 'holliday');
We can also tell SQL Server to return specific columns instead of all columns.
insert into employees (first_name, last_name) output inserted.last_name values ('keith', 'holliday');