Specifying a value and converting columns during a table transfer?

Question

I am currently transferring data from one database to another.

My problem occurs with a newly designed table. The old table consisted of the columns:

car1, car2, car3, id1, id2, id3, id4, idtxt, idtxt2  
(id types could be the same for multiple rows)  

The rows would have a specific combination to car1, 2, 3 with a specific id for each combination. id1 is always filled, while 2,3,4,txt,and txt2 may or may not be filled.

My new columns are consisted of:

car1, car2, car3, idtype, id#,text

In this new table idtype is = to id1, id2, id3, id4, id5(idtxt), and id6(idtxt2) while id# is = to the row values of the id in table one. This means there will be more rows than in the old table since there will be a row for each id# even if they are the same combination.

How would you go upon transferring this information through a stored procedure?

I currently can only think of running multiple stored procedures that consist of insert into statements, but can't seem to find any answers as to how i would go upon inserting a certain value in addition to transferring the data. ie (idtype = id1 for all of this transfer)

I am also convinced this isn't the most efficient way.

Please tell me if clarification is needed. Thanks!

Edit, I have figured out how to insert specific values during a data transfer. Other problems/ suggestions still stand.

Answer 1

If I understand correctly what you are doing, you can do it with a simple query.

It sounds like you are trying to normalize the data, by taking fields pivoted in the first table and unpivoting them in the second.

select car1, car2, car3, 'id1' as idtype, id1
from t union all
select car1, car2, car3, 'id2', id2
from t union all
select car1, car2, car3, 'id3', id3
from t union all
select car1, car2, car3, 'id4', id4
from t union all
select car1, car2, car3, 'id5', idtxt
from t union all
select car1, car2, car3, 'id2', idtxt2
from t

You can save this into another table by creating the destination table and inserting, or by using "create table as" or "select into" (depending on the database you are using).

For instance, if you are creating the table, your code would look something like:

create table <new table in new database> (
    <tablename>id int primary key identity(1,1),
    car1 <some type>,
    ...,
    CreatedBy varchar(256) default system_user,
    CreatedAt datetime default getdate(),
); 

insert into <new table in new database>(car1, car2, car3, idtype, id1)
    <select query above>

Note: I've use SQL Server syntax for this, and included three additional columns: id, CreatedBy, and CreatedAt. (Almost) all tables should have these columns.