Select one table and insert into another table

I am new to SQL and I know little about Transact-SQL.

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

I have a table with multiple columns ( 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 into another table.

First table

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



is a combination of the first three characters from locationCode

The data in the first table is grouped using the first three char from locationCode

and the client code

second table

(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 the first table and the second table by selecting the primary table rows from SQL?

Thank you for all your answers.


source to share

1 answer

I think you need something like below. The @Output temporary table will capture the inserted ids for the first table, then they can be used when inserting into the second 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,
FROM    [PrimaryTable] p
        INNER JOIN @Output o
            ON LEFT(LocationCode, 3) = WarehouseCode
            AND p.CustomerCode = o.CustomerCode




All Articles