INSERT INTO multiple tables from SELECT statement

Question

I have a table called GoodsInOld which contains 4 columns:

GoodsInOld:     Id, CustomerAccount, CustomerName, ItemId

due to a new data structure I've created 2 new tables to hold the data:

GoodsIn and GoodsInProduct.

They have a relationship like below:

GoodsIn:        Id, CustomerAccount, CustomerName ...
GoodsInProduct: Id, ItemId, GoodsInId ...

GoodsIn MAY have 1 or many GoodsInProduct

I want to insert the data from GoodsInOld into these 2 tables using a INSERT INTO or SELECT INTO?

You could always use SSIS to move the data. Then you can use a Multi cast operator to split your results to as many tables as you want. — Zane
– Zane, Commented Nov 21, 2013 at 16:07

juergen d · Accepted Answer · 2013-11-21 16:05:36Z

2

insert into GoodsIn (CustomerAccount, CustomerName)
select CustomerAccount, CustomerName
from GoodsInOld 


insert into GoodsInProduct(ItemId, GoodsInId)
select o.ItemId, i.id
from GoodsInOld o
inner join GoodsIn i on o.CustomerAccount = i.CustomerAccount
                     and o.CustomerName = i.CustomerName

answered Nov 21, 2013 at 16:05

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Wesley Gordon Over a year ago

You may also want to select distinct from GoodsInOld for your customer info to go into GoodsIn if your original table could have duplicates.

Collectives™ on Stack Overflow

INSERT INTO multiple tables from SELECT statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related