使用PrimaryKey将2个表中的多行插入1个表中

I have 4 tables,

我有4张桌子，

Table A header and A Details as a master

表A标题和A详细信息作为主数据

Table C header and C Details as a transaction

表C标题和C详细信息作为事务

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 :

然后，我得到了表C作为事务的插入过程，包括一个细节：

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?)

我想将表A标题和表A详细信息插入表c详细信息（如何获取？）

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.

我想让表A细节的unitcost = 0。

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?

无论如何我能得到结果吗？

2 个解决方案

#1

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

#2

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

#1