CODEWITHSUNDEEP
sqldatabasephpmyadminmariadb
Nick Zagkanas
Nick Zagkanas

Reputation: 89

Insert selected columns from one table to another SQL

I have two separate databases in the same host (localhost:3306). I have one table from the first database and a second from the other database. Here is 2 screenshots to become more clear enter image description here

enter image description here

I need to match:

ID -> username
EMAIL -> email
FIRST_NAME -> first_name
LAST_NAME -> last_name

Is there any automatic way with a software? Need I to write some specific query to copy these 4 columns from one table to another? I search on internet but I didn't find any example. For the info, the first picture is the ldap users from keycloack and the second one is the imported users to an app with sso auth.

Till now I copied manually the records of users.

thanks

Upvotes: 0

Views: 112

Answers (1)

MtwStark
MtwStark

Reputation: 4058

you should try to update users with missing/different informations from ldap database to app database

update u2 set email=u1.email, first_name=u1.first_name, last_name=u1.last_name
from database2.app_users u2 
join database1.ldap_users u1 on u1.id = u2.username
where isnull(u2.email, '') <> isnull(u1.email, '')
or isnull(u2.first_name, '') <> isnull(u1.first_name, '')
or isnull(u2.last_name, '') <> isnull(u1.last_name, '')

EDIT

After more detailed explanation I understood that you need to insert missing users and not to update them.

insert into u2 (username, email, first_name, last_name)
select u1.id, u1.email, u1.first_name, u1.last_name
from database1.ldap_users u1 
left join database2.app_users u2 on u1.id = u2.username
where u2.username is null

this should work

Upvotes: 1

Related Questions