CODEWITHSUNDEEP
mysqlsql
tjhack
tjhack

Reputation: 1032

mysql create multiple tables from one table

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

Upvotes: 0

Views: 138

Answers (2)

turkeyhundt
turkeyhundt

Reputation: 214

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)

Upvotes: 1

Tim Ogilvy
Tim Ogilvy

Reputation: 1973

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.

Upvotes: 0

Related Questions