2

I have a number of tables in an existing database that i would like to migrate to a new database but with a little different structure.

For example if a current table has columns like below:

table1
id, a1, a2, a3, a4, b1, b2, b3, b4

And the new structure would be something like:

table1, table2 (new table)

table1 (patient names)
id, a1, a2, a3, a4, bfk

table2 (doctor names)
id, b1, b2, b3, b4

EDIT:

Adding hypothetical scenario of patients and doctors.

Lets say that the original table had 4 rows for 4 patients so it would look like

 row1   id, pat1fname, pat1lname, pat1address...., doc1fname, doc1lname,....
 row2   id, pat2fname, pat2lname, pat2address...., doc2fname, doc2lname,....
 row3   id, pat3fname, pat3lname, pat3address...., doc1fname, doc1lname,....
 row4   id, pat4fname, pat4lname, pat4address...., doc1fname, doc1lname,....

Lets say that we only have two doctors and we want to put them in their own table 'Doctors'

so the tables would look like this

Original table structure
     row1   id, pat1fname, pat1lname, pat1address...., doc1fname, doc1lname,.... 
     row2   id, pat2fname, pat2lname, pat2address...., doc2fname, doc2lname,....
     row3   id, pat3fname, pat3lname, pat3address...., doc1fname, doc1lname,....
     row4   id, pat4fname, pat4lname, pat4address...., doc1fname, doc1lname,....


New table structure
'Patients' table
    row1    id, pat1fname, pat1lname, pat1address, 1, ....,
    row2    id, pat2fname, pat2lname, pat2address, 2, ....,
    row3    id, pat3fname, pat3lname, pat3address, 1, ....,
    row4   id, pat4fname, pat4lname, pat4address, 1, ....,
'Doctors' table
    row1    1, doc1fname, doc1lname,....
    row2    2, doc2fname, doc2lname,....

What would be a good way of linking these two new tables and converting each row from one-table-structure above to the two-table-structure i mentioned.

  • Don't forget: doctors can be patients. – Neil McGuigan Nov 2 '14 at 4:51
1

Methods of Linking:
1. Logically just left join doctors table and get doctor ID during patients data insert as showed below
2. Physically add foreign key constraint and during insert left join doctors table and get doctor ID during patients data insert

 /* Supposed your OriginalTable is with following structure */

    create table #OriginalTable
    (
    ID int,
    patfname varchar(100),
    patlname varchar(100),
    pataddress1 varchar(100),
    pataddress2 varchar(100), 
    patcity varchar(40),
    patstate varchar(4),
    Patzipcode varchar(20),
    Docfname varchar(100),
    Doclname varchar(100),
    Docaddress1 varchar(100),
    Docaddress2 varchar(100), 
    Doccity varchar(40),
    Docstate varchar(4),
    Doczipcode varchar(20),
    DocNPI varchar(40)
    )

    /*Dumpy data insert*/
    insert into #OriginalTable
    select '1', 'pat1fname','pat1lname','pat1address1','pat1address2','Pat1City','p1S','pt1zip'
    ,'doc1firstname','doc1lastname','doc1address1' ,'doc1address2' ,'doc1City','d1S','dt1zip','000001'
    union all
    select '2', 'pat2fname','pat2lname','pat2address2','pat2address2','Pat2City','p2S','pt2zip'
    ,'doc2firstname','doc2lastname','doc2address1' ,'doc2address2' ,'doc2City','d2S','dt2zip','000002'
    union all
    select '3', 'pat3fname','pat3lname','pat3address3','pat3address2','Pat3City','p3S','pt3zip'
    ,'doc1firstname','doc1lastname','doc1address1' ,'doc1address2' ,'doc1City','d1S','dt1zip','000001'
    union all
    select '4', 'pat4fname','pat4lname','pat4address4','pat4address2','Pat4City','p4S','pt4zip'
    ,'doc1firstname','doc1lastname','doc1address1' ,'doc1address2' ,'doc1City','d1S','dt1zip','000001'


    /* create patients table with all required fields*/    
    create table Patients
    (
    ID int identity(1,1),
    FirstName Varchar(100),
    LastName Varchar(100),
    Adress1 Varchar(100),
    Adress2 Varchar(100),
    City Varchar(50),
    [State] Varchar(4),
    ZipCode varchar(20),
    DoctorID Int
    )    

    /* create Doctors table with all required fields*/
    create table Doctors
    (
    ID int identity(1,1),
    FirstName Varchar(100),
    LastName Varchar(100),
    Address1 Varchar(100),
    Address2 Varchar(100),
    City Varchar(50),
    [State] Varchar(4),
    ZipCode varchar(20),
    NPI varchar(40)
    ) 

    /*Remove existing data from patients and doctors table*/
    delete from Doctors
    delete from patients


    /* insert all distinct doctors data from Orginal table */
    insert into Doctors( FirstName ,LastName ,Address1 ,Address2 ,City ,[State] ,ZipCode ,NPI )
    select distinct docfname,doclname,docAddress1,docAddress2, docCity, docState, docZipcode, docNPI
    from #OriginalTable


    /* Set Identity insert On to add OLD unique ID values as they are in #OriginalTable */

    SET IDENTITY_INSERT Patients ON

    insert into  Patients( ID , FirstName ,LastName ,Adress1 ,Adress2 ,City ,[State] ,ZipCode ,DoctorID )
    select old.ID ,old.patfname ,old.patlname ,old.pataddress1 ,old.pataddress2 ,old.patcity ,old.patstate ,old.Patzipcode ,doc.ID
    from #OriginalTable old left outer join Doctors doc
    on old.Docfname=doc.FirstName
    and old.Doclname=doc.LastName
    and old.Docaddress1=doc.Address1
    and old.Docaddress2=doc.Address2
    and old.Doccity=doc.City
    and old.Docstate=doc.[State]
    and old.Doczipcode=doc.ZipCode
    and old.DocNPI=doc.NPI

    /*Set Identity insert off */
    SET IDENTITY_INSERT Patients OFF




    /* Query table to verify inserted data*/
    select * from #OriginalTable
    select * from Patients
    select * from Doctors


    DROP Table #OriginalTable 
0

Either use a SELECT INTO or generate your tables and do an INSERT INTO SELECT

USE NewDatabase
GO

SELECT id, a1, a2, a3, a4, <some calc here> AS bfk
      INTO NewTable1 -- Generates the new table
FROM OldDatabase.schema.Table1

SELECT id, b1, b2, b3, b4
      INTO NewTable2 -- Generates the new table
FROM OldDatabase.schema.Table1

OR

USE NewDatabase
GO

CREATE TABLE NewTable1 (id int, a1 ......)
CREATE TABLE NewTable2 (id int, b1 ......)

INSERT INTO NewTable1
SELECT id, a1, a2, a3, a4, <some calc here> AS bfk
FROM OldDatabase.schema.Table1

INSERT INTO NewTable2
SELECT id, b1, b2, b3, b4
FROM OldDatabase.schema.Table1

The first method is of course much easier but the second gives you quite a bit more control over the structure. For example if the id column is an identity then the id columns in both NewTable1 and NewTable2 will be identity columns.

EDIT: Based on the edit here is what I would do. You can still do it a number of ways, this is just one example.

CREATE TABLE Table1 (id int, patfname varchar(50), patlname varchar(50),
                        pataddress varchar(50), docfname varchar(50),
                        doclname varchar(50))

INSERT INTO Table1 VALUES
     (1, 'pat1fname', 'pat1lname', 'pat1address', 'doc1fname', 'doc1lname'), 
     (2, 'pat2fname', 'pat2lname', 'pat2address', 'doc2fname', 'doc2lname'),
     (3, 'pat3fname', 'pat3lname', 'pat3address', 'doc1fname', 'doc1lname'),
     (4, 'pat4fname', 'pat4lname', 'pat4address', 'doc1fname', 'doc1lname')

CREATE TABLE Doctor (docid int NOT NULL IDENTITY(1,1),
                    docfname varchar(50), doclname varchar(50))

INSERT INTO Doctor
SELECT docfname, doclname
FROM Table1
GROUP BY docfname, doclname

CREATE TABLE Patients (id int, patfname varchar(50), patlname varchar(50),
                        pataddress varchar(50), docid int)

INSERT INTO Patients
SELECT Table1.id, Table1.patfname, Table1.patlname, Table1.pataddress, Doctor.docid
FROM Table1
INNER JOIN Doctor
    ON Table1.docfname = Doctor.docfname
    AND Table1.doclname = Doctor.doclname
  • These INSERT INTO NewTable# statements would have to be in some kind of a loop mechanism right? So that i can loop through all rows of the current table. – Dust Nov 2 '14 at 0:41
  • Also, another confusing piece is how would i keep the relationship between the two tables. I mean, the <some calc here> would have to connects the correct record from the new table to the table1 – Dust Nov 2 '14 at 0:45
  • First question: No, the query will pull all of the rows of the table and put it into the new table. In fact if you want to restrict it you would have to put a WHERE clause on the query. – Kenneth Fisher Nov 2 '14 at 0:51
  • Second question: I have to admit that I'm assuming that the id column was the primary key of the original table. If that's the case then you would be able to use it to relate the two tables. If not you can use ROW_NUMBER() to generate a relationship but you will have to put it in both places. – Kenneth Fisher Nov 2 '14 at 0:53
  • yes, correct assumption. ids are PKs. The thing, though, is that once i create the new table NewTable2 (id, b1, b2...) how will i make bfk field from table1 be FK to NewTable2. Sorry, for newbie questions, I'm not that good in DB world. – Dust Nov 2 '14 at 1:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.