CODEWITHSUNDEEP
sqlsql-servert-sql
Jml
Jml

Reputation: 79

Insert Multiple Rows from 2 table into 1 table with PrimaryKey

I have 4 tables,

Table A header and A Details as a master

Table C header and C Details as a transaction

for example :

Table A Header
|Aid | Desc | UnitCost
|1   | Ts   | 1400

Table A Detail
Aid |BID  | Qty | UnitCost
1   |1    | 12  | 200
1   |2    | 21  | 300
1   |3    | 33  | 400

then, i got the insert process for table C as a transaction, include a detail :

Table C header
CID |Desc
1   |Payment Transaction

the detail transaction as follow:

I want to insert Table A header and Table A Detail into table c detail (How to Get this?)

Table C detail
CID |BID  | Qty | UnitCost
1   |1    |  1  | 1400
1   |1    | 12  | 0
1   |2    | 21  | 0
1   |3    | 33  | 0

i already create an insert process like this (below), but it give me an error.

and i want to make unitcost = 0 for the Table A Detail.

INSERT INTO TableCHeader (CID, Desc) VALUES (1, 'Payment Transactions')

-- insert table A header first
INSERT INTO TableCDetail (CID, BID, Qty, UnitCost)
VALUES (1, (SELECT AID, 1, UnitCost FROM TableAHeader WHERE AID = 1))

-- then, insert table A details
INSERT INTO TableCDetail (CID, BID, Qty, UnitCost)
VALUES (1, (SELECT BID, Qty, UnitCost FROM TableADetail WHERE AID = 1))

is there anyway I can get the result?

Upvotes: 1

Views: 58

Answers (2)

user1469712
user1469712

Reputation: 54

Answer will be like this:

;WITH Table_A_Header AS
(
    SELECT 1  Aid , 'Ts' AS [Desc], 1400 AS UnitCost
)
,Table_A_Detail AS
(
    SELECT 1 AS Aid,1 AS BID,12 AS Qty,200 AS UnitCost
    UNION ALL
    SELECT 1,2,21,300
    UNION ALL
    SELECT 1,3,33,400
)
--INSERT INTO TableCDetail (CID, BID, Qty, UnitCost)
SELECT 
    CID=1,
    BID=AID,
    QTY=1, 
    UnitCost 
FROM Table_A_Header WHERE AID = 1
UNION ALL
SELECT
    CID=1,
    BID, 
    Qty, 
    UnitCost=0
FROM Table_A_Detail WHERE AID = 1

Upvotes: 3

DhruvJoshi
DhruvJoshi

Reputation: 17126

Your latter part should be like 

-- insert table A header first
INSERT INTO TableCDetail (CID, BID, Qty, UnitCost)
SELECT 
    CID=1,
    BID=AID,
    QTY=1, 
    UnitCost 
FROM TableAHeader WHERE AID = 1

-- then, insert table A details
INSERT INTO TableCDetail (CID, BID, Qty, UnitCost)
SELECT
    CID=1,
    BID, 
    Qty, 
    UnitCost=0
FROM TableADetail WHERE AID = 1

Upvotes: 1

Related Questions