CODEWITHSUNDEEP
sqlasp.netsql-server
Hello World
Hello World

Reputation: 13

Retrieve data from one table and insert into another table

I am trying to retrieve data from one table and then insert it into another table. This is a a sample of the first table in which there is the following data. tb1 is the table which consists of data. The two columns Manager and TeamLeader basically means for example : Josh is managed by Vik and so on. An employee can also be a manager to another employer. For example, Josh is the manager of Nirvan and Deva.

+---------+-------------+
|        tbl1           |
+---------+-------------+
| Manager | Employee    |
+---------+-------------+
| Vik     | Josh        |
+---------+-------------+
| Vik     | Cindy       |
+---------+-------------+
| Vik     | Alvin       |
+---------+-------------+
| Vik     | Kim         |
+---------+-------------+
| Josh    | Nirvan      |
+---------+-------------+
| Josh    | Deva        |
+---------+-------------+
| Cindy   | Mervyn      |
+---------+-------------+
| Nirvan  | Reeta       |
+---------+-------------+
| Nirvan  | Zaki        |
+---------+-------------+
| Nirvan  | Sunny       |
+---------+-------------+

What i want is to insert all these records in another table with the following columns : Id(which is set to IDENTITY/AUTONUM), Name(name of employee/manager), ParentId(of the manager which a particular employee has to report to). So for example, I should be getting something of the sort :

ID Name ParentId
1  Vik    0
2  Josh   1
3  Cindy  1
4  Alvin  1
5  Kim    1
6  Nirvan 2
7  Deva   2
8  Mervyn 3
9  Reeta  6
10 Zaki   6
11 Sunny  6

I am having difficulty to get the right sql to retrieve this data from the first table and insert it into another table.

Upvotes: 1

Views: 8337

Answers (3)

bayamgam
bayamgam

Reputation: 1

create table #tbl1 (manager char(15), employee char(15))

create table #tbl2 (ID int identity(1,1), Name char(15), ParentID int)

 insert into #tbl1
 values ('Vik', 'Josh') ,
('Vik'     , 'Cindy')  ,
('Vik'     , 'Alvin') ,
('Vik'     , 'Kim')   ,
('Josh'    , 'Nirvan'),
('Josh'    , 'Deva') ,
('Cindy'   ,'Mervyn')  ,
('Nirvan'  , 'Reeta'),
('Nirvan'  , 'Zaki'),
('Nirvan'  , 'Sunny')

--- big Boss 
insert into #tbl2(Name, ParentID)
SELECT DISTINCT manager, 0
FROM #tbl1
WHERE manager NOT IN ( SELECT employee FROM #tbl1  )

--- Grab all Employees (Name)
insert into #tbl2(Name)
select distinct employee from #tbl1

--- Update ParentID 
declare @StructureLevl int = 3  -- 0 1 2 3  Do something to figure out this number first
declare @Var_int int = 0
while (@Var_int < @StructureLevl)
begin
    update #tbl2 
    set ParentID= @Var_int +1
    where Name in (select A.employee 
                    from #tbl1 A inner join #tbl2 B on A.manager = B.Name 
                    where B.ParentID = @Var_int)

    set @Var_int +=1
end

select * from #tbl2
order by ParentID

Upvotes: 0

Greg Viers
Greg Viers

Reputation: 3523

INSERT INTO tbl2 (
    Name
    ,parentId
    )
SELECT DISTINCT manager
    ,0
FROM tbl1
WHERE manager NOT IN (
        SELECT employee
        FROM tbl1
        )

INSERT INTO tbl2
SELECT DISTINCT employee
    ,0
FROM tbl1

UPDATE tbl2
SET parentid = parent.id
FROM tbl2
INNER JOIN tbl1 ON tbl2.Name = tbl1.employee
INNER JOIN tbl2 parent ON parent.Name= tbl1.manager

Upvotes: 1

Brian Pressler
Brian Pressler

Reputation: 6713

You have to do it in two steps. One to create all your ID fields. Then you can join with this table to match up the employee to find the manager and the manager's ID:

insert into MyNewTable (Name, ParentID)
select Manager, ParentID
from tbl1
union
select Employee, ParentID 
from tbl1

update MyNewTable
set MyNewTable.ParentId = Managers.Id
from MyNewTable
join tbl1
on tbl1.Employee = MyNewTable.Name
join MyNewTable Managers
on MyNewTable.Name = Managers.Manager

Upvotes: 2

Related Questions