Select 1 row query insert into multi row

I have existing data like this:

Table A

bill      type            amount
-----------------------------------          
10        Food             100          
11        Beverage          50
12        Food             200

Table Master GL:

Type      D/C     GL_CODE
-------------------------
Food      D       12345
Food      C       56789
Beverage  D       88888
Beverage  C       99999

I need to select all row in table A, insert this into below Table B

1 row in Table A will insert into 2 row in table B, also will select the GL code from table Master GL, to get the GL code.

D/C     GL CODE          Amount      bill
-----------------------------------------
D       12345            100         10
C       56789            100         10
D       88888             50         11
C       99999             50         11
D       12345            200         12
C       56789            200         12

Can anyone advise me the query?

Thank you

Answer

you should join TableA with MasterGL on Type In SQL Server

INSERT INTO TableB (DC, GL_CODE, Amount , bill)
SELECT M.DC,
       M.GL_CODE,
       A.Amount ,
       A.bill
FROM   TableA A
       JOIN mastergl M
         ON A.type = M.type

Advertisement

Answer