CODEWITHSUNDEEP
sql-server
Mohammad Shahbaz
Mohammad Shahbaz

Reputation: 423

perform function after inserting every row from select

I have two tables. first table has data 

Table_1
===================

ID | Name | Status       | Code
-------------------------------------
1  | John | Manager      | A
2  | Don  | Manager      | A
2  | Don  | Supervisor   | B
3  | Jim  | Manager      | A
3  | Jim  | Supervisor   | B
3  | Jim  | Employee     | C

Now I have another table where I want to move this data so I used Insert from select statement but in the table Table_2 , I want each Name to have all three status( manager, supervisor, employee)

If a name in table_1 already has all 3 status and code then it should insert from select without any change, but If it has only 1 status then table_2 should insert two more dummy names with the same id AND if if a name has two entry then enter only 1 dummy name with same ID.

Here is the Table_2 which I actually want:

 Table_2
 ===================

 ID | Name | Status       | Code
 -------------------------------------
 1  | John | Manager      | A
 1  | Dum1 | Supervisor   | B
 1  | Dum2 | Employee     | C
 2  | Don  | Manager      | A
 2  | Don  | Supervisor   | B
 2  | Dum2 | Employee     | C
 3  | Jim  | Manager      | A
 3  | Jim  | Supervisor   | B
 3  | Jim  | Employee     | C

I am using Insert from select statement but that simply copies the data form table_1 to table_2

Upvotes: 1

Views: 33

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

By far the biggest issue here is that your data is not normalized. This causes lots of challenges. There is probably an easier way to accomplish this but here is what I came up with. The first step is to use a couple of ctes to make your data normalized so you can easily join to it. From there a simple ROW_NUMBER will get you Dummy_1 etc that will restart for each value of ID and will always be in sequence regardless of which position in the ID the missing data is.

Using the fine sample data and table from scsimon I put this together.

declare @table1 table (ID int, Name varchar(64), status varchar(64), Code char(1))
insert into @table1
values
(1,'John','Manager','A'),
(2,'Don','Manager','A'),
(2,'Don','Supervisor','B'),
(3,'Jim','Manager','A'),
(3,'Jim','Supervisor','B'),
(3,'Jim','Employee','C')
;

with Codes as
(
    select distinct Code
        , Status
    from @table1
)
, IDs as
(
    select distinct ID
    from @table1
)
select i.ID
    , Name = case when t.Name is null then 'Dummy_' + convert(varchar(10), row_number() over(partition by i.ID, t.Name order by c.Code)) else t.Name end
    , c.Status
    , c.Code
from Codes c
cross join IDs i
left join @table1 t on t.code = c.Code and t.ID = i.ID
order by i.ID
    , c.Code

Upvotes: 2

S3S
S3S

Reputation: 25112

You can use a table constructor and cross apply.

declare @table1 table (ID int, Name varchar(64), status varchar(64), Code char(1))
insert into @table1
values
(1,'John','Manager','A'),
(2,'Don','Manager','A'),
(2,'Don','Supervisor','B'),
(3,'Jim','Manager','A'),
(3,'Jim','Supervisor','B'),
(3,'Jim','Employee','C')



declare @table2 table (ID int, Name varchar(64), status varchar(64), Code char(1))

insert into @table2 (ID, Name, Status, Code)
select distinct
    ID
    ,Name
    ,x.Status
    ,Code = case 
                when x.Status = 'Manager' then 'A'
                when x.Status = 'Supervisor' then 'B'
                when x.Status = 'Employee' then 'C'
            end
from @table1
cross apply (values('Manager'),('Supervisor'),('Employee')) x(Status)
order by
    ID
    ,x.Status


update t2
    set t2.Name = 'Dummy_' + case 
                                when t2.Status =  'Manager' then '1'
                                when t2.Status =  'Supervisor' then '2'
                                when t2.Status =  'Employee' then '3'
                            end
from
    @table2 t2
left join
    @table1 t1 on t1.ID = t2.ID and t1.Code = t2.Code
where
    t1.ID is null



select *
from @table2
order by ID, Code

Upvotes: 2

Related Questions