Select from one table and insert to another two table

Question

I am a beginner at SQL and I don't know much about 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).

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

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

Thanks you for all of your replies.

Have a look at this link http://blog.sqlauthority.com/2011/08/10/sql-server-use-insert-into-select-instead-of-cursor/ — Chetter Hummin, Mar 30 '12 at 08:42
I can insert second table by using insert second table(colums,..) select primary table — NNNN, Mar 30 '12 at 08:46
you can edit your post and insert your code in it, it would be easier on the eyes =) — cctan, Mar 30 '12 at 08:49
Can you insert the Primary Table's primary key into the "First Table"? Otherwise, I don't think there's a way you can select the right row (or at least guarantee that it will be unique) ... — McGarnagle, Mar 30 '12 at 09:14

score 5 · Accepted Answer · answered Mar 30 '12 at 09:08

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.

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

Select from one table and insert to another two table

1 Answers1

Linked

Related