mysql create multiple tables from one table

Question

I have one table which I have imported into mysql.

Now I need to create multiple related tables.

So basically I have currently got the following

start transaction;

Insert into Address (AddressLine1, AddressLine2, Town, County, Postcode)
Select Distinct Address_line_1, Address_Line_2, Town, County, Postcode from Import;

set addressLastId = last_insert_id();

INSERT INTO Organisation (Name, AddressID) 
SELECT DISTINCT Supplier_Name, addressLastId FROM Import;

commit;

The second part where I use the last_insert_id never increments probably because it gets the last insert.

So I need to workout how i can get the previous id for each row inserted into the address table?

Would i need to have an inner loop within the address insert ?

Cheers

turkeyhundt · Accepted Answer · 2013-12-17 00:25:26Z

1

I agree with Tim. After you've populated Address, then you could just run

INSERT INTO Organisation (Name, AddressID)
SELECT DISTINCT Import.Supplier_Name, Address.id
FROM Import INNER JOIN Address ON (set all the address lines and city etc =, since Im guessing there wasnt an address ID in the original import)

answered Dec 17, 2013 at 0:25

turkeyhundt

2142 silver badges15 bronze badges

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

Comments

Tim Ogilvy · Accepted Answer · 2013-12-17 00:06:54Z

0

You could use a join for the second insert - join Address and Import, and insert the required fields from each into Organisation.

Getting the last insert ID will only work if you process each record sequentially.

answered Dec 17, 2013 at 0:06

Tim Ogilvy

1,9731 gold badge25 silver badges37 bronze badges

6 Comments

tjhack Over a year ago

Cheers. So how will i match the the correct address to the organisation as there is no unique id to determine this?

Tim Ogilvy Over a year ago

I would join on the 4 fields you have imported into address (so Address_line_1, Address_Line_2, Town, County) and return the ID from Address also. You probably need to use a left join and select distinct if there are duplicates.

Tim Ogilvy Over a year ago

Have a go at making the joined table first so that it gets you all the info you need to insert into company, and post up your query if you get stuck.

tjhack Over a year ago

Cheers will give that a go first

tjhack Over a year ago

When doing the insert into the company table do i need to get the company name and search that in the import table and then using the import table match the row within the address table? Cos currently company table is blank

|

Collectives™ on Stack Overflow

mysql create multiple tables from one table

2 Answers 2

Comments

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Related