thank you for all your help, I will do that

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...

candiate_no pk first last type_Rqd ...

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...

try group by

SELECT DISTINCT field1

FROM Table1

WHERE (conditions)

GROUP BY field2 - hope this helps

I think I must be misunderstanding my querie, just cant seem to get it tow work

BEGIN TRANSACTION

USE KWEA;

INSERT INTO waitList(candidate_no,exp_start_date,property_type,no_of_rooms,max_rent,no_of_people)

SELECT 1,1-10-2007,'house',3,$300,4

UNION ALL SELECT 2,4-10-2007,'house',4,$350,4

...