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

Question

The short version is I'm trying to map from a flat table to a new set of tables with a stored procedure.

The long version: I want to SELECT records from an existing table, and then for each record INSERT into a new set of tables (most columns will go into one table, but some will go to others and be related back to this new table).

I'm a little new to stored procedures and T-SQL. I haven't been able to find anything particularly clear on this subject.

It would appear I want to something along the lines of

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

But I'm uncertain how to get that to save related records since I'm splitting it into multiple tables. I'll also need to manipulate some of the data from the old columns before it will fit into the 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 about 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 think the user is trying to add some data,so not worthy of downvotes — TheGameiswar, Sep 22 '16 at 15:03
Oh man, separating the `Customer Name` to `FirstName` and `LastName` is reaaaaaally not trivial — Lamak, Sep 22 '16 at 15:08
@techspider updated. There's existing Ids that we don't care about, as new Ids will be assigned as records are inserted. — Randy Hall, Sep 22 '16 at 15:08
@Lamak I can work out how to do the actual manipulation, just not sure where that should happen — Randy Hall, Sep 22 '16 at 15:10
Are you sure your customer name always has First Name and Last Name only separated by space? Are you sure no middle names anywhere? — techspider, Sep 22 '16 at 15:10
use the output clause of the insert into MyNewCarTable to insert into MyNewCustomerTable using the identity values from the INSERTED virtual table, http://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value . — Bruce Dunwiddie, Sep 22 '16 at 15:11
@techspider This is example only my actual tables are much more beastly (the origin table has like 35 columns) but yes, I will be able to manage the manipulations themselves. They're pretty straightforward — Randy Hall, Sep 22 '16 at 15:14

techspider · Accepted Answer · 2016-09-22T15:25:42.473

I would say you have your OldTable Id to preserve in new table till you process data.

I assume you create an Identity column Id on your MyNewCarTable

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

Then, join the new table and above table to insert into your second table. I assume your MyNewCustomerTable also has Id column with Identity enabled.

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

Note: I have not applied Split of Customer Name to First Name and Last Name as I was unsure about existing data.

If you don't want your OldId in MyNewCarTable, you can DELETE it

ALTER TABLE MyNewCarTable DROP COLUMN OldId

This gets me off to a great start, thanks! Once I get it working I'll accept, might take me a bit though. — Randy Hall, Sep 22 '16 at 15:27

score 1 · Answer 2 · answered Sep 22 '16 at 15:21

You are missing a step in your normalization. You do not need to duplicate your customer information per vehicle. You need three tables for 4th Normal form. This will reduce storage size and more importantly allow an update to the customer data to take place in one location.

Customer CustomerID FirstName LastName

Car CarID Make Model Year

CustomerCar CustomerCarID CarID CustomerID DatePurchaed

This way you can have multiple owners per car, multiple cars per owner and only one record needs to be updated per car and or customer...4th Normal Form.

score -1 · Answer 3 · answered Sep 22 '16 at 15:13

If I am reading this correctly, you want to take each row from table 1, and create a new record into table A using some of that row data, and then data from the same original row into Table B, Table C but referencing back to Table A again?

If that's the case, you will create TableA with an Identity and make thats the PK.

Insert the required column data into that table and use the @IDENTITY to retrieve the last identity value, then you will insert the remaining data from the original table into the other tables, TableB, TableC, etc. and use the identity you retrieved from TableA as the FK in the other tables.

By Example:

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

When the first row is read, the values for col1 & col2 are inserted into TableA. The Identity is captured from that row inserted, and then value for col3 AND the identity are entered into TableB, and then value for col4 AND the identity are entered into TableC.

This is a standard data migration technique for normalizing data.

Hope this assists,

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

3 Answers3