CODEWITHSUNDEEP
sqlsql-servert-sql
Surya
Surya

Reputation: 426

Insert into one table from another two table

I have 3 tables

declare @approval_request table(req_id int, file_id int, req_type varchar(100))

insert into @approval_request  
values (1, 1001, 'bulk'),
       (2, 1002, 'demo'),
       (3, 1003, 'bulk'),
       (4, 1004, 'test');


declare  @bulk_account table (file_id int, account varchar(50));

insert into @bulk_account
values (1001, '501'), (1002, '401'),
       (1001, '502'), (1002, '402'),
       (1001, '503'), (1002, '403');

I want to get all file_id from approval_request table where type='bulk' then all accounts for corresponding file_id from second table(bulk_account ) and insert into third table as below.

declare  @approval_bulk_account table 
         (
             req_id int, 
             account varchar(50)
         );

so the new table data will be

(req_id, account)
1 501
1 502
1 503

Upvotes: 0

Views: 36

Answers (2)

Surya
Surya

Reputation: 426

insert into @approval_bulk_account 
select a.req_id,b.account 
from   @approval_request a 
inner join  @bulk_account b on a.file_id  =  b.file_id
                           and a.req_type = 'bulk';

Upvotes: 1

Dark S
Dark S

Reputation: 330

Try to insert into the third table with inner join on first 2 tables, something like this

insert into approval_bulk_account 
    (req_id,account)
    select a.req_id , b.account 
       from approval_request a inner join bulk_account b
       on a.file_id =b.file_id
    where a.req_type ='bulk'

Upvotes: 0

Related Questions