How to insert multiple rows in one to many relationship in store procedure

Question

0.00/5 (No votes)

See more:

i have two tables. checkIn table and room table. checkin has many room (1 to many relationship). in my roomGridView i have 3 row belongs to checkin, my question is how can i save multiple row of 1 to many relation using store procedure in c# window form.

in my room table it has checkInId (latest id)// i try using SCOPE_IDENTITY(), i dont know how to use it for multiple time ? if i make two stored procedure how i can i get the checkInId ??

What I have tried:

C#

//in my insertion 
using (SqlCommand cmd = new SqlCommand("sp_insertCheckIn", MainClass.con))
                    {
                        MainClass.con.Open();
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@userId", userId);
                        cmd.Parameters.AddWithValue("@guestId", guestId);
                        cmd.Parameters.AddWithValue("@dateIn", checkIn);

                      foreach (DataGridViewRow row in rooms)
                      {
                        cmd.Parameters.AddWithValue("@individualRoomId", 
                        row.Cells[13].Value.ToString());
                      }

                       cmd.ExecuteNonQuery();
                        MainClass.con.Close();
                        MainClass.showMessage("CheckIn Successfully.", "success");

// sql store procedure 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[sp_insertCheckIn]
@userId int,
@guestId int,
@dateIn datetime2(0),
...
AS
BEGIN
SET NOCOUNT ON;
insert into checkIn_table(checkIn_user_id, guest_id,date_in,date_out,no_day) 
    values (@userId,@guestId,@dateIn,@dateOut,@noDay) -- put values here (from parameters?)
insert into checkIn_room_table(checkIn_id, individual_room_id)
			values (SCOPE_IDENTITY(), @individualRoomId)  -- put other values here (from parameters?)// the problem is it has more than one and it is possible to use foreach here?

Posted 16-Jan-21 4:31am

Member 14921707

Updated 16-Jan-21 5:54am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2021-01-16T05:54:00

From the MS documentation:

SCOPE_IDENTITY (Transact-SQL) - SQL Server | Microsoft Docs[^]:
Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

Which means that provided you insert you data in the same SP, It will return the ID for the first insert to put into the second table, and if that table also has an IDENTITy column it will change for the next row.
If you want the whole of the second table to INSERT the same ID value to many rows, then store the value it returns and INSERT that:

SQL

INSERT INTO TableA ([Desc]) VALUES ('Hello');
DECLARE @lastID INT = SCOPE_IDENTITY(); 
INSERT INTO TableB (FID, [Desc]) VALUES (@lastID, 'World1');
INSERT INTO TableB (FID, [Desc]) VALUES (@lastID, 'World2');
INSERT INTO TableB (FID, [Desc]) VALUES (@lastID, 'World3');