Insert data into 2 related tables at once?

Question

I am trying to insert into two tables at once using the primary key from the first table to insert into the second but I am not sure how to go about it.

My table structure is as follows:

Person

PersonId
FirstName
Surname

Employee

EmployeeId
HoursWorked

PersonId in the Person table is an auto incremented column. EmployeeId in the second table is a primary and foreign key that should be the same as PersonId.

I been trying with this query string which I found on Google but with not much luck:

string queryString = "BEGIN TRANSACTION DECLARE @DataID int; "
 +"INSERT INTO Person(FirstName, Surname) VALUES(@firstName, @surname);" 
 + "SELECT @DataID = scope_identity();" 
 + "INSERT INTO Employee VALUES(@DataId, @hoursWorked);" 
 + "COMMIT";

Are you looking for a T-SQL stored procedure to do this? Or some programming language code to handle this? If so: **what** programming language? — marc_s, May 29 '22 at 09:51
I am doing this inside a C# function in a Windows Forms App. — Nuvolari, May 29 '22 at 09:52
What code do you have so far? How many rows are you inserting — Charlieface, May 29 '22 at 10:04
I been trying with this query string which I found on Google but with not much luck: `string queryString = "BEGIN TRANSACTION DECLARE @DataID int; " +"INSERT INTO Person(FirstName, Surname) VALUES(@firstName, @surname);" + "SELECT @DataID = scope_identity();" + "INSERT INTO Employee VALUES(@DataId, @hoursWorked);" + "COMMIT";` — Nuvolari, May 29 '22 at 10:10
Are you familar with the concept of the transactions (see e.g. https://learn.microsoft.com/de-de/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-ver16)? — h.m.i.13, May 29 '22 at 10:18
I am getting an error about "column name or number of supplied values does not matching table definition" — Nuvolari, May 29 '22 at 10:19
It's been a while since I read up on transactions but my memory is that if one part of the transaction fails they all fail so that you don't get partial data in the database. — Nuvolari, May 29 '22 at 10:39

marc_s · Accepted Answer · 2022-05-29T10:40:21.050

From C#, you can try something like this:

// define the INSERT query - insert a firstname,surname into "Person",
// and insert a row into the Emplyoee table with the new ID
// created by the first insert
string insertStmt = @"BEGIN TRANSACTION
                      INSERT INTO dbo.Person(FirstName, Surname) VALUES(@FirstName, @Surname);
                      DECLARE @NewPersonId INT = SCOPE_IDENTITY();
                      INSERT INTO dbo.Employee(EmployeeId, HoursWorked) VALUES(@NewPersonId, @HoursWorked);
                      COMMIT TRANSACTION;"

// define connection and command for inserting data                       
using (SqlConnection conn = new SqlConnection(-your-connection-string-here-))
using (SqlCommand cmdInsert = new SqlCommand(insertStmt, conn))
{
    // Define parameters - adapt datatype and max length as required
    cmdInsert.Parameters.Add("@FirstName", SqlDbType.VarChar, 100);
    cmdInsert.Parameters.Add("@Surname", SqlDbType.VarChar, 100);
    cmdInsert.Parameters.Add("@HoursWorked", SqlDbType.Int);
    
    // set parameter values
    cmdInsert.Parameters["@FirstName"].Value = "John";
    cmdInsert.Parameters["@Surname"].Value = "Doe";
    cmdInsert.Parameters["@HoursWorked"].Value = 35;
    
    // Open connection, execute query, close connection
    conn.Open();
    
    int rowsInserted = cmdInsert.ExecuteNonQuery();
    
    conn.Close();
}

Update: as mentioned by @charlieface, you can write this code more concisely IF you're only ever inserting a single row - like this:

// define connection and command for inserting data                       
using (SqlConnection conn = new SqlConnection(-your-connection-string-here-))
using (SqlCommand cmdInsert = new SqlCommand(insertStmt, conn))
{
    // Define and set parameters
    cmdInsert.Parameters.Add("@FirstName", SqlDbType.VarChar, 100).Value = "John";
    cmdInsert.Parameters.Add("@Surname", SqlDbType.VarChar, 100).Value = "Doe";
    cmdInsert.Parameters.Add("@HoursWorked", SqlDbType.Int).Value = 35;
    
    // Open connection, execute query, close connection
    conn.Open();
    int rowsInserted = cmdInsert.ExecuteNonQuery();
    conn.Close();
}

Nice and neat. `cmdInsert.Parameters.Add("@FirstName", SqlDbType.VarChar, 100).Value = "....";` more concise. Also probably best to `SET XACT_ABORT ON` — Charlieface, May 29 '22 at 10:25
@Charlieface: I've come to avoid that "more concise" style - if you have the need to insert 10 rows, one after another - it's better to have explicit steps to *define* the parameters (once, before a loop), and a second step to actually set the parameter values (inside a loop, to handle the multiple inserts). But for a single insert - I agree, that style would be preferable — marc_s, May 29 '22 at 10:32
I hear you, although for multi-row inserts I tend to use either a Table Valued Parameter or `SqlBulkCopy` anyway — Charlieface, May 29 '22 at 10:38

score 4 · Answer 2 · answered May 29 '22 at 10:24

4

You need to specify the columns you are inserting into.

You should also use SET XACT_ABORT ON if you have an explicit transaction.

Note also the use of a multi-line string.

string queryString = @"
SET XACT_ABORT ON;

BEGIN TRANSACTION;

INSERT INTO Person (FirstName, Surname)
VALUES(@firstName, @surname);
DECLARE @DataID int = scope_identity();

INSERT INTO Employee (EmployeeId, HoursWorked)
VALUES(@DataId, @hoursWorked);

COMMIT;
";

answered May 29 '22 at 10:24

Charlieface

52,284
6
19
43

I am unfamiliar with this explicit transaction thing but I will add it in. – Nuvolari May 29 '22 at 10:34
See https://stackoverflow.com/a/1150069/14868997 – Charlieface May 29 '22 at 10:39

Insert data into 2 related tables at once?

2 Answers2