T-SQL Select, Manage, and Reinstall Using a Stored Procedure

Question

A short option - I'm trying to map from a flat table to a new set of tables with a stored procedure.

Long version: I want SELECTrecords from an existing table, and then for each record INSERTin a new set of tables (most columns will go into one table, but some will go to others and will be associated with this new table).

I am a little new to stored procedures and T-SQL. I could not find anything particularly clear on this subject.

It would seem that I want something like strings

INSERT INTO [dbo].[MyNewTable] (col1, col2, col3)
    SELECT
        OldCol1, OldCol2, OldCol3 
    FROM 
        [dbo].[MyOldTable] 

But I'm not sure how to get this to save related records, since I split it into several tables. I will also need to manipulate some data from the old columns before they enter the new columns.

thank

Data examples

MyOldTable

Id  | Year | Make | Model   | Customer Name
572 | 2001 | Ford | Focus   | Bobby Smith
782 | 2015 | Ford | Mustang | Bobby Smith

In (without worries about duplicate clients or keeping old identifiers):

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

Answer 1

I would say that you have your OldTable identifier to store in a new table before processing the data.

I assume you are creating a column Identity Idon yourMyNewCarTable

INSERT INTO MyNewCarTable (OldId, Year, Make, Model)
SELECT Id, Year, Make, Model FROM MyOldTable

, . , MyNewCustomerTable Id Identity .

INSERT INTO MyNewCustomerTable (CustomerName, CarId)
SELECT CustomerName, new.Id 
FROM MyOldTable old
JOIN MyNewCarTable new ON old.Id = new.OldId

. , .

OldId MyNewCarTable, DELETE

ALTER TABLE MyNewCarTable DROP COLUMN OldId

Answer 2

. . 4- . , , .

LastName

CarID

CustomerCar CustomerCarID CarID DatePurchaed

, , ​​ / ... 4- .

Answer 3

, 1 A, , B, C, A?

, TableA PK.

@IDENTITY , , TableB, TableC .. , TableA FK .

:

1 col1, col2, col3, col4, col5 A TabAID, col1, col2 B TabBID, TabAID, col3 TableC TabCID, TabAID, col4

, col1 col2 A. , col3 TableB, col4 TableC.

.

, ,