sql server - T-SQL Select, manipulate, and re-insert via stored procedure -

the short version i'm trying map flat table new set of tables stored procedure.

the long version: want select records existing table, , each record insert new set of tables (most columns go 1 table, go others , related new table).

i'm little new stored procedures , t-sql. haven't been able find particularly clear on subject.

it appear want along lines of

insert [dbo].[mynewtable] (col1, col2, col3)     select         oldcol1, oldcol2, oldcol3               [dbo].[myoldtable]  

but i'm uncertain how save related records since i'm splitting multiple tables. i'll need manipulate of data old columns before fit new columns.

thanks

example data

myoldtable

id  | year | make | model   | customer name 572 | 2001 | ford | focus   | bobby smith 782 | 2015 | ford | mustang | bobby smith 

into (with no worries duplicate customers or retaining old ids):

mynewcartable

id | year | make | model 1  | 2001 | ford | focus  2  | 2015 | ford | mustang 

mynewcustomertable

id | firstname | lastname | carid 1  | bobby     | smith    | 1 2  | bobby     | smith    | 2 

i have oldtable id preserve in new table till process data.

i assume create identity column id on mynewcartable

insert mynewcartable (oldid, year, make, model) select id, year, make, model myoldtable 

then, join new table , above table insert second table. assume mynewcustomertable has id column identity enabled.

insert mynewcustomertable (customername, carid) select customername, new.id  myoldtable old join mynewcartable new on old.id = new.oldid 

note: have not applied split of customer name first name , last name unsure existing data.

if don't want oldid in mynewcartable, can delete it

alter table mynewcartable drop column oldid