user1770461 user1770461 - 1 month ago 8
SQL Question

How to insert using different table based on condition in same query

I am merging data in one table from tables of 2 database. Structure is as per below:


Table in new Database :

User Table : {UserName,Email}

Table in Database1 :

User Table : {UserName,Email,LastLogin}

Table in Database2 :

User Table : {UserName,Email,LastLogin}


Now i need to write query that if Email address are same in 2 tables from database 1 and database2 then we need to insert record where LastLogin is latest.

Can someone suggest over this.

Answer

I think you are in need of this.. :)

Try modifying it accordingly..

declare @Email_1 nvarchar(100),@Email_2 nvarchar(100),@UserName nvarchar(100),@Lastlogin_1 datetime,@Lastlogin_2 datetime,@loop int=0
use [Database1]
while @loop != (select count(Distinct Email) from [User Table])
BEGIN
use [Database1]
set @Email_1 = (select Distinct Email from [User Table] order by email asc offset @loop rows fetch next 1 rows only)
set @LastLogin_1 = (select top 1 max(LastLogin) from [User Table] where email=@Email_1)

use [Database2]
set @Email_2 = (select top 1 Email from [User Table]  where Email like '%@Email_1%')
set @LastLogin_2 = (select top 1 max(LastLogin) from [User Table] where email=@Email_2)

if @email_1=@email_2
BEGIN
if @LastLogin_1>@LastLogin_2
BEGIN
use [Database_1]
set @username = (select top 1 Username from [user table] where email=@email_1 and lastlogin=@Lastlogin_1)
use [New Database]
insert into [User Table]
select @username,@email_1
END
else if @LastLogin_1<@LastLogin_2
BEGIN
use [Database_2]
set @username = (select top 1 Username from [user table] where email=@email_2 and lastlogin=@Lastlogin_2)
use [New Database]
insert into [User Table]
select @username,@email_1
use [Database1]
END
END
set @loop=@loop+1
END
Comments