How to retrieve a value from a table and insert into another
I am developing a Booking System and for my database i am using Sql Server. I have three tables created and i need to join them. My user will need to input details of their booking which then gets saved to the BookingDetails table. The rate they get charged depends on a number of parameters they enter when booking. My Question is how do i return the TestId from TestNew table when user enters in the specified parameters(i.e Section and Member). Below is my ERD and what i have tried so far. Any advice or help is much appreciated. Thanks --Input Parameters variables Declare @UserId int = 1 Declare @Member nvarchar(50) = 'Student' Declare @Section nvarchar(50) = 'FullPitch' Declare @Date date = '03/23/18' Declare @StartTime time(7) = '18:00:00' Declare @EndTime time(7) = '19:00:00' Declare @Description nvarchar(max) = 'Blah Blah' --Identity Keys Declare @BookingId int Declare @AmountPaid int Declare @TestId int SELECT UD.Name, BD.Date, BD.StartTime, BD.EndTime, BD.Description, NT.Member, NT.Section, NT.Rate from BookingDetails BD join UserDetail UD on UD.UserId = BD.UserId join NewTest NT on NT.TestId = BD.TestId where UD.UserId = @UserId and BD.Date = @Date and BD.StartTime = @StartTime and BD.EndTime = @EndTime and BD.Description = @Description and NT.Member = @Member and NT.Section = @Section and NT.TestId = @TestId Begin insert into dbo.BookingDetails values (@UserId, @Member, @Section, @Date, @StartTime, @EndTime, @Description, @AmountPaid, @TestId) select @BookingId = SCOPE_IDENTITY() End ![alt text] : /storage/temp/4495-erd.png
I'm presuming `TestID` is an `IDENTITY` on `NewTest`? You would need to insert into `NewTest` first and use `SCOPE_IDENTITY()` to retrieve the value. Then you can insert into `BookingDetails` with a valid FK value of `TestID`.