Multiple select from one table and insert into multiple tables

Question

I have an oldDB and I want to split one table of oldDB (tblCustomers) in to two tables of newDB (Customer and ReservedService) as follows:

OldDB

tblCustomers: (Code, Name, Phone, Address, Duration, Price)

NewDB

Customer: (Id, Code, Name, Phone, Address)

ReservedService: (Id, CustomerId, Duration, Price)

I have try these two queries:

query1:

use newDB
insert into newDB.dbo.Customer([Code], [Name], [Address], Phone) 
select Code, SUBSTRING(Name,1,50), SUBSTRING(Address,1,100), SUBSTRING(Phone,1,50)
from oldDB.dbo.tblCustomers

query2:

use newDB
    insert into newDB.dbo.ReservedService(CustomerId, Duration, Price) 
    select `????`, SUBSTRING(Duration,1,50), SUBSTRING(Price,1,100)
    from oldDB.dbo.tblCustomers

Notice that I have two problems:

merge these two queries in one query
in second query the select statement should retrieve data from two tables (retrive CusttomerId from newDB.Customer and retrieve other fields from oldDB.tblCustomers)

How can I integrate these two queries in one query without two mentioned problems?

What is your question here? You state that you have problems, but don't ask anything. — Larnu
Is Customer.Id column in the new table an Identity column ? Also is tblCustomers.Code in the old table a unique column, in simple words, You don't have One customer with multiple codes or Two different customers with the same code ? — M.Ali

Eralper Eralper · Accepted Answer · 2018-01-26T11:04:19

@fateme, you can use SQL Output clause in order to get identity value (as Customer table Id column value) and then use it for ReservedService table as follows

declare @t table (
    customerid int, code varchar(10)
)

insert into Customer (
    Code, Name, Phone, Address
)
output
    inserted.id, inserted.code
into @t(customerid, code)
select Code, Name, Phone, Address
from tblCustomers

insert into ReservedService (
    CustomerId, Duration, Price
)
select CustomerId, Duration, Price
from tblCustomers
inner join @t as t
    on t.code = tblCustomers.Code

You can realize that I used a variable table to store Id and Code relation for later use during Insert command for ReservedService

I hope it helps

Multiple select from one table and insert into multiple tables

OldDB

NewDB

1 Answers