insert into table A, select from table A join B where condition from B, for each record that meets condition in B

Question

I want to insert into table B for a range of employees from table A where the select statement for the insert is from table B. To restate that; I have created all the records I need in table B for one employee (employeeid = 1). Now I want to insert into table B similar records for a range of employees in table A.

so far I have

insert TABLEB as B
(EMPLOYEEID, COLUMN1, COLUMN2, COLUMN3)
     SELECT A.EMPLOYEEID, B.COLUMN1, B.COLUMN2, B.COLUMN3 
     FROM TABLEB 
     WHERE EMPLOYEEID = 1

now I want that to insert for each row in Table A that meets a selecion criteria

the above works for one employee. How do I make it iterate for a range of employees from table A. note that the EMPLOYEEID I want to insert for each row in table B will be the ID from the employee in table A.

Thanks,

What do you mean by range?? Can u specify limits of the range from where to where? — Sudhir kumar, Nov 17 '14 at 07:11
You're missing a join condition between a and b I think. Can you post the schema for table a and b in your question? — kidshaw, Nov 17 '14 at 07:14

score 0 · Answer 1 · edited Nov 18 '14 at 06:03

0

Try the below query:

insert TABLEB as B (EMPLOYEEID, COLUMN1, COLUMN2, COLUMN3) 
SELECT A.EMPLOYEEID, A.COLUMN1, A.COLUMN2, A.COLUMN3 
FROM TABLEA 
WHERE A.EMPLOYEEID between 1 and 13

edited Nov 18 '14 at 06:03

J0e3gan

8,740
10
53
80

answered Nov 17 '14 at 07:14

Imtiaz Ali

13
1
6

score 0 · Answer 2 · answered Nov 17 '14 at 07:47

0

INSERT INTO TABLEB (EMPLOYEEID, COLUMN1, COLUMN2, COLUMN3)
SELECT B.EMPLOYEEID, A.COLUMN1, A.COLUMN2, A.COLUMN3 
FROM TABLEB As B
JOIN TABLEA AS A ON(A.EMPLOYEEID=B.EMPLOYEEID)

answered Nov 17 '14 at 07:47

vinayak

67
3

score 0 · Answer 3 · edited May 23 '17 at 10:33

declare @loopVar int
Set @loopVar = (select min(employeeid) from mast.employee as e 
                where e.JobTitle = 'Night Cook')

while @loopVar is not null

begin

insert mast.PayProfile
(EmployeeID, PayitemCode, PayItem, EarningId, AllowanceId, 
 Quantity, Rate, Amount, BaseRate, ApplySalarySacrifice)
(select @loopvar, PayitemCode, PayItem, EarningId, AllowanceId, 
 Quantity, Rate, Amount, BaseRate, ApplySalarySacrifice 
 from mast.payprofile as pp
 join mast.Employee as e on e.EmployeeId = pp.EmployeeId 
 where pp.EmployeeId = 224 
 and (earningid is not null or allowanceid is not null))

Set @loopVar = (select min(employeeid) from mast.Employee 
                where EmployeeId>@loopVar and JobTitle='Night Cook' 
                and IsTerminated = 0)
end

Note: The loop idea was copied from What are the most common SQL anti-patterns? and modified (because I think there was a typo in the answer).

insert into table A, select from table A join B where condition from B, for each record that meets condition in B

3 Answers3