Migrating SQL data from Access

Question

I need to migrate some data from access to sql.

access has one table: tblMakeModel with two fields: make, model

I am making two tables in SQL

tblMake: id, make

and

tblModel: id, makeID, model

I've moved the makes over, but now i can't figure out how to migrate the models to match up with the makeIDs, since I am recreating ID's, how do i match them up?

please help

Larry Lustig · Accepted Answer · 2011-01-03 19:46:14Z

2

(I'm assuming that the IDs are database-generated integers).

Bring the entire table into the SQL Server database and call it tblMakeModel (just like in the original). Then:

 INSERT INTO tblMake (Make) SELECT DISTINCT Make FROM tblMakeModel

 INSERT INTO tblModel (MakeID, Model) 
    SELECT DISTINCT M.MakeID, MM.Model 
    FROM tblMakeModel MM INNER JOIN tblMake M ON MM.Make = M.Make

 DROP TABLE tblMakeModel

If the IDs are not a sequence, then you'll need to edit the tblMake table after the first command to contain the new IDs and then run the other two commands, then add the IDs to the tblModel table. In this case, you'll need to remove the PK constraint on both tables until you finish your work.

answered Jan 3, 2011 at 19:46

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Migrating SQL data from Access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related