x

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

erd.png (9.5 kB)
more ▼

asked Feb 09 at 08:11 AM in Default

avatar image

jodonovan
1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Feb 09 at 08:18 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

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

Feb 09 at 03:02 PM jodonovan

Are you saying that you will query NewTest on Member, Section and Date to retrieve the TestID?

Feb 09 at 03:11 PM Kev Riley ♦♦

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)

Feb 09 at 03:49 PM jodonovan
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x95
x73

asked: Feb 09 at 08:11 AM

Seen: 37 times

Last Updated: Feb 09 at 04:02 PM

Copyright 2018 Redgate Software. Privacy Policy