question

jodonovan avatar image
jodonovan asked

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][1] [1]: /storage/temp/4495-erd.png
sqlserverparameters
erd.png (9.3 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
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`.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the reply yes TestId is an Identity in NewTest table, My issue with that is that i wont be inserting anything into NewTest only retrieving data from inside that table. I am retrieving data from UserDetails and NewTest is and inserting into Booking Details based on what is pulled from the NewTest Table if that makes any sense? Apologies its hard to describe
0 Likes 0 ·
Are you saying that you will query NewTest on Member, Section and Date to retrieve the TestID?
0 Likes 0 ·
Iv made a bit of progress since posting the question so now i want to insert the result of these two select statements into the BookingDetails table to create a new booking: --Input Parameters variables //Pulling from UserDetails table Declare @UserId int = 2 // Pulling from NewTest table Declare @Member nvarchar(50) = 'Staff' Declare @Section nvarchar(50) = 'FullPitch' //These input parameters are already columns in the BookingDetails Table 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' select UserId, Name from UserDetail where UserId = @UserId select TestId, Section, Member, Rate from NewTest where Section=@Section and Member=@Member I hope this makes more sense. The UserID pulls the relevant User logged in to make the booking and Section and Member pull the information relevant for determining the price of the booking (rate, stored also in the NewTest Table)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.