How to use Output in Sql Server

Intro

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 Syntax

The basic syntax of a Output is as follows:

INSERT INTO [table] (name)
OUTPUT Inserted.ID
VALUES('bob');

Getting Setup

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.

docker-compose up

If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.

Example

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');