SQL command for relationships inside database

Hi guys,

I’m currently trying to export data from an old SugarCRM into SuiteCRM using SQL in phpMyadmin.

What i did:

1)Import the Accounts : OK
2)Import the Contacts: OK
3)import the accounts_contacts table : Failed…

I read that just doing this in that order should have created the relationship right away but it didn’t.

It seems that the contact_id and the account_id are changing once in Suite

PS: Before importing the accounts and contacts, i made sure that they were all unique by using the UUID() function in php

Do you have an idea to make the relationships work ? I cannot manually redo all of them DB is too big



What is your SuiteCRM version?

Are you importing directly into the database (with mysql command-line, phpMyAdmin or similar), or are you using the Import function inside the app?

I would recommend doing it directly in the database, that way no IDs will get changed.

I found it !

I managed to create a SQL command that works for me and efficiently links the contact to the accounts

INSERT INTO accounts_contacts (account_id, contact_id)
SELECT accounts.id ,contacts.id
FROM accounts
JOIN contacts
WHERE accounts.name= ‘XXXXX’ && contacts.last_name= ‘XXXXXXX’

Then i just execute this command to create a unique id for this table

UPDATE accounts_contacts
SET id =UUID()
WHERE contact_id =“XXXXXXX”

These commands work with limited conditions ( I wanted to work on a limited amount of contacts and accounts) but i haven’t got the mass importation conditions figured yet

Btw i’m importing from phpMyAdmin .

1 Like