How to merge 2 tables together?-CodePudding

I have 2 a table with this structure:

[Database1].[dbo].[Users_Detail]

UserID    NickName    
-------------------------------
1         book1       
2         book2       
3         book2       
4         tv1

[Database2].[dbo].[Users_Detail]

UserID    NickName    
-------------------------------
1         nick1       
2         nick2       
3         book2       
4         tv2
5         tv3  
6         tv1

I have 2 tables with this structure where the UserID column is linked to the Users_Detail

[Database1].[dbo].[Sys_User_Ghost]

ID      UserID       Value    
-------------------------------
1         3         Value1       
2         3         Value2       
3         3         Value3       
4         4         Value4

[Database2].[dbo].[Sys_User_Ghost]

ID        UserID    NickName    
-------------------------------
1          3         Value12       
2          2         Value13       
3          4         Value14       
4          4         Value15
5          6         Value16  
6          6         Value16

I want to convert this table to:

[Database1].[dbo].[Users_Detail]

UserID    NickName    
-------------------------------
1         book1       
2         book2       
3         book2       
4         tv1    
5         tv3    
6         nick1       
7         nick2       
8         book2       
9         tv2
10         tv1

[Database1].[dbo].[Sys_User_Ghost]

ID      UserID       Value    
-------------------------------
1         3         Value1       
2         3         Value2       
3         3         Value3       
4         4         Value4 
5         8         Value12       
6         7         Value13       
7         9         Value14       
8         9         Value15
9         10        Value16  
10        10        Value16

I want the duplicate userid columns to be able to change to another userid without duplicates and add.

I have a Sys_User_Ghost table with a userid column associated with the userid at Users_Detail. I want it to sync with the duplicate userids changed in the question above.

CodePudding user response：

For User_Details Table Use Following Query

Insert Into [Database1].[dbo].[Users_Detail] (UserID,NickName) Select 
UserID,NickName from [Database2].[dbo].[Users_Detail]

For Sys_User_Ghost Table Use Following Query

Insert Into [Database1].[dbo].[Sys_User_Ghost] (ID,UserID,Value) Select 
ID,UserID,Value from [Database2].[dbo].[Sys_User_Ghost]

Using Above qurery from database2 tables data insert into database1 tables, you can check using following query

Select * From [Database1].[dbo].[Users_Detail]

Select * From [Database1].[dbo].[Sys_User_Ghost]

CodePudding user response：

You can do it in below approach,

For Users_Detail Table,

select * from [Database1].[dbo].[Users_Detail]
union all
select * from [Database2].[dbo].[Users_Detail]

For Sys_User_Ghost table,

select * from [Database1].[dbo].[Sys_User_Ghost]
union all
select * from [Database2].[dbo].[Sys_User_Ghost]

Note that if you dont want to take duplicates then use union instead of unionall.

See here details about sql union and union all .

https://www.sqlshack.com/sql-union-vs-union-all-in-sql-server/