SQL-Server add record tip
Tags: csharp, dotnetcore, sql, sqlserver
Published: 2024-06-16 12:46:12

## Introduction

Learn how to add a new record to a SQL-Server database table and in the same SQL statement insert the new primary key and current user name into a secondary table. This permits a way to track who added a new record. Although not shown, the same technique can be used for update and delete operations.

{% cta https://github.com/karenpayneoregon/sql-basics/tree/master/SqlServerOutputToOtherTableExample %} Sample project {% endcta %}

## Basics

To add a new record to a Person table, create an INSERT statement followed by a SELECT separated by a semi-colon.

```sql
INSERT INTO dbo.Person (FirstName,
                        LastName,
                        Gender)
VALUES ('Karen', 'Payne', 'Female');
SELECT CAST(SCOPE_IDENTITY() AS INT);
```

In SSMS (SQL-Server Management Studio) after executing the above statements the new identifier is shown in the results window.

### Dapper sample 1

[Dapper](https://github.com/DapperLib/Dapper) is used here while a developer can perform the same operations with a connection and command objects, Dapper simply makes the process easier.

First create the statements, here the statement is in a read only string, since Dapper handles stored procedures the statement can be in a stored procedure, here it is easy to follow in a raw string literal.

Microsoft documentation for [OUTPUT clause](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16) which, in this case provides our new primary key.

```csharp
internal class SqlStatements
{
    public static string InsertPerson =>
        """
        INSERT INTO dbo.Person (FirstName,
                                LastName,
                                Gender)
        OUTPUT Inserted.Id
        VALUES (@FirstName, @LastName, @Gender);
        """;
}
```

Here the code is in a console project in Program/Main method.

Person class

```csharp
public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Gender { get; set; }
}
```

- Create a new Person
- Create a connection with the connection string in appsettings.json
- Using Dapper's QueryFirstAsync method which will return an int, first parameter is the SQL statement from above and the second parameter the person to add.
- Display the new identifier via [Spectre.Console](https://spectreconsole.net/) NuGet package.


```csharp
Person p = new Person() { FirstName = "Karen", LastName = "Payne", Gender = "Female"};

await using var cn = new SqlConnection(DataConnections.Instance.MainConnection);

AnsiConsole.MarkupLine("[yellow]Adding a single record[/]");
var identifier = await cn.QueryFirstAsync<int>(SqlStatements.InsertPerson, p);
AnsiConsole.MarkupLine($"[cyan]Identifier: {identifier}[/]");
```

### Dapper sample 2

Insert a new Person record and also insert a record into another table, Transaction to remember who added the Person record.

As mention above the same can be done with updates and deletions. If going this route, add another column to the transition table for what type of action was performed e.g. add, delete or edit. Also, a date column has been added to know when the action was performed.

First, here is the database structure

- Person table which is for storing people.
- Transactions table which will store an id from an insert statement for Person table and the identifier of who inserted the person record.


![Table structures](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z2ek7pi2to4zum5dnadi.png)

SQL Statement, InsertPersonToTransactions, in the OUTPUT clause the new primary key and the current user identifier are passed to the Transaction table to be added.


![INSERT statement](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xu8cd3s7pgjtsaoyqp6n.png)

C# Code which will insert two records into the Person table and Transaction table.

```csharp
List<Person> list =
[
    new() { FirstName = "Mary", LastName = "Adams", Gender = "Female" },
    new() { FirstName = "Bill", LastName = "Jones", Gender = "Male" }
];

await using var cn = new SqlConnection(DataConnections.Instance.MainConnection);
await cn.ExecuteAsync(SqlStatements.InsertPersonToTransactions, list);
```
## Adding a type to the Transaction table

Add a new column, in this case named Action.


![Modified Transaction table schema with action column](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/teblse02qdbwiocumd7g.png)

SQL Statement

![sql statement with action column](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bjs0jfmwmbhskha5et11.png)

The code does not change from the last example.

## Summary

Code has been presented to show how to insert a new record in a primary table along with at the same time insert a new record into a secondary table. Although Dapper was used, the same will work with conventional connection and command objects.

As stated earlier, SQL statements are embedded in code while a developer may consider using stored procedures as an option.

### Setup sample code

Once you have the sample code

1. Open SSMS to localdb
1. Create a database named OutputDatabase
1. Run the script under Scripts/createDatabase_with_Action_In_Transaction_Table.sql
1. Build and run the project,

{% cta https://github.com/karenpayneoregon %} 🚀 Visit My GitHub Profile {% endcta %}