September 29, 2007 at 7:46 am
Hi everyone, appologies I posted before about this but I cant sort it without some advice, seem to be going in circles here is my three tables
Table CandidateTenant
candidate_no PK
first_name
last_name
contact_ph
comments
Table WaitList
waiting_no PK
candidate_no FK
ex_startDate
Prop_type
no_of_rooms ect ect
Table Tenant
tenant_no PK
waiting_no FK
po_addr
P-Code
comments ect ect
What happens is custmer entered into candidateTenant, they are then placed on the waitList, thus deleted from the candidateTenant table (no longer a candidate)they then stay waiting until a property comes up at which time they gofrom waiting to Tenant, I then need to update the tenant table and delete them from the waitiList table. Basically they move from 1 to the next until they become Tenant.
My candidateTenant table is poulated so I do this
BEGIN TRANSACTION
INSERT INTO waitList(candidate_no,exp_start_date,property_type,no_of_rooms,max_rent,no_of_people)
SELECT 11,1-10-2007,'house',3,$300,4
UNION ALL SELECT 12,4-10-2007,'house',4,$350,4
UNION ALL SELECT 13,20-10-2007,'town house',2,$350,2
UNION ALL SELECT 14,5-11-2007,'villa',3,$300,3
UNION ALL SELECT 15,14-10-2007,'house',4,$350,5
FROM waitList candidateTenant
DELETE FROM candidateTenant
FROM candidateTenant,waitList
WHERE waitList.candidate_no = candidateTenant.candidate_no
COMMIT-------this performs the INSERT but will not delete, is there something ovious I cant see, any help would be appreciated, also can i rollback the transaction so I dont have to keep deleting the data as i type in rollback transaction and it gives an error
September 29, 2007 at 8:31 am
I don't get what is the problem in this context. The code should look a lot like this :
BEGIN TRAN
Insert into WaitList (Columns) Select Columns FROM Candidates WHERE ID = @Id
DELETE FROM dbo.Candidates WHERE ID = @Id
COMMIT TRAN
You can always swap @Id with a list of ids if you need to do this by groups. I'm not expert in environement with huge transaction loads so there might be some tweakings here to handle that context. However if you don't have more than 1-2 users at the time doing this, then this should work fine.
September 29, 2007 at 9:10 am
so sorry i dont understand, primary key in the first table needs to be inserted into the second tables and then i need to insert the rest of the columns as they dont match the first table so can i go
Insert into waitlist(columns)
values(--foreign key = primary key of table1,other values,,,,,
September 29, 2007 at 9:17 am
Let's start over.
Show us sample data from all three tables before the transfer.
Then show us what data needs to move and what information you have available at the moment of that request.
Then show us the end results. We'll put the rest of the pieces together.
September 29, 2007 at 9:34 am
candiate_no pk first last type_Rqd ph_no
1 JulieSmithhouse 93075555
2 LisaJoneshouse 93074545
3 JasonBurkeTown house93084422
4 SamJacksvilla 93048811
5 JohnStokeshouse 93024545
6 JanPooleappartment 95012323
This is the candidateTenant table, the waitList table has no data yet. I want to take the candidate_no from here and put it into the following table waitList
Table Waitlist
wait_no PK candiate_no FK exp_start prop_type rooms max_rent
1 2
2 4
3 5
ect but i need to add exp_start, prop_type, rooms, max_rent also
Then i need to delete candidatTenants 2,4,5 from the first table candidateTenant
September 29, 2007 at 9:49 am
This will still work... unless I missed something obvious :
BEGIN TRAN
Insert into WaitList (Columns) Select Columns FROM Candidates WHERE ID IN (2,4,5)
DELETE FROM dbo.Candidates WHERE ID IN (2,4,5)
COMMIT TRAN
September 29, 2007 at 10:07 am
Here is my sql
Insert into WaitList (candidate_no,exp_start_date,property_type,no_of_rooms,max_rent,no_of_people)
Select Columns FROM candidateTenant WHERE ID IN (2,4,5)
DELETE FROM dbo.Candidates WHERE ID IN (2,4,5)
ERROR The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
so how do i add the extra data, do i need another insert statement
September 29, 2007 at 10:59 am
Go into books online and learn how to do that. You must list all the columns you want to transfer in the insert clause and into the select clause. There is most like some good tutorials on w3schools.com about sql as well. Spend the time to learn yourself, it'll be very worth your while on the long run.
September 29, 2007 at 8:31 pm
thank you for all your help, I will do that
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply