从一个表中选择并插入到另外两个表。

I am a beginner at SQL and I don't know much about Transact-SQL.

我是SQL初学者，对Transact-SQL了解不多。

I realize this is a newbie question, but I'm looking for a simple solution.

我知道这是一个新手的问题，但我正在寻找一个简单的解决方案。

I have a table with some columns (locationCode, CustomerCode).

我有一个包含一些列的表(locationCode, CustomerCode)。

Primary table

主要表

    ItemCode    locationCode    CustomerCode
    I001        001001          C001 
    I002        001002          C001    
    I003        001001          C002
    I004        002001          C002

I want to select data from this table and insert to two others table.

我想从这个表中选择数据并插入到另外两个表中。

First table

第一个表

firstTblId(autoIncrement)  warehouseCode  CustomerCode
       1                       001           C001   
       2                       001           C002
       3                       002           C002

warehouseCode is a combination of first three characters from locationCode

warehouseCode是locationCode中前三个字符的组合

Data in first table are grouped by first three char of locationCode and Customer Code

第一个表中的数据按位置码和客户码的前三个字符进行分组

second table

第二个表

secondTblId
(autoIncrement)  ItemCode  locationCode  CustomerCode   firstTblId(FK)
      1            I001       001001        C001            1
      2            I002       001002        C001            1
      3            I003       001001        C002            2
      4            I004       002001        C002            3

So, how can I insert first table and second table by selecting primary table's rows with SQL??

那么，如何使用SQL选择主表的行来插入第一个表和第二个表呢?

Thanks you for all of your replies.

谢谢你的回复。

1 个解决方案

#1

I think you want something like the below. The temporary table @Output will capture the inserted identities for the first table, then these can be used when inserting to the second table.

我想你想要下面这样的东西。临时表@Output将捕获第一个表的插入标识，然后可以在插入第二个表时使用这些标识。

DECLARE @Output TABLE 
(       FirstTableID    INT NOT NULL PRIMARY KEY, 
        WarehouseCode   VARCHAR(3), 
        CustomerCode    VARCHAR(4)
)
INSERT INTO FirstTable (WarehouseCode, CustomerCode)
OUTPUT inserted.FirstTblID, inserted.WarehouseCode, inserted.CustomerCode INTO @Output
SELECT  DISTINCT LEFT(LocationCode, 3) [WarehouseCode], CustomerCode
FROM    [PrimaryTable]

INSERT INTO SecondTable (ItemCode, LocationCode, CustomerCode, FirstTblID)
SELECT  p.ItemCode,
        p.LocationCode,
        p.CustomerCode, 
        o.FirstTableID
FROM    [PrimaryTable] p
        INNER JOIN @Output o
            ON LEFT(LocationCode, 3) = WarehouseCode
            AND p.CustomerCode = o.CustomerCode