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
warehouseCode
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
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 the first table and the second table by selecting the primary table rows from SQL?
Thank you for all your answers.
source to share
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.
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
source to share