merging few tables that has same column into one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

merging few tables that has same column into one

Author

Topic

fengqidan
Starting Member

3 Posts

Posted - 2011-02-09 : 20:17:55

i got a task which require to merge 3 tables that contains exactly same columns but with different names

3 tables' name look like user_1, user_2, user3
their columns are IPaddress, name

what i want to do is to merge these 3 tables into a big table
USER" with one extra column "id" to store the id info"1,2,3"

anyone got ideas?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-09 : 20:42:58

IS the assumption that 1 row would be

ID IPAddress USer1, USer2,User3

And that IPAddress is the way to join the 3 together?


Create Table USERS ( 
 ID int identity(1,1)
,IPAddress varchar(15)
,User1 varchar(10) not null
,User2 varchar(10) null
,User3 varchar(10) null
)


INSERT INTO USERS (IPAddress,User1,User2,User3)

SELECT a.IPaddress, a.User1, b.User2,c.User3
FROM User1 a 
	left join User2 b on a.IPAddress = b.IPaddress
	left join User3 b on a.IPAddress = c.IPaddress

Might be more practical to have 1 3 column table which had the ID, a userNo or sequence name, and the associated username. That way you could have any number of users in sequence for the same ID.


Create Table USERS ( 
 ID int identity(1,1)
,IPAddress varchar(15)
,UserNo int not null
,UserName varchar(10)
)


INSERT INTO USERS (IPAddress,USerNo,UserName)

SELECT IPaddress, 1, UserName,  
FROM User1 a  
UNION ALL
SELECT IPaddress, 2, UserName,  
FROM User2 
UNION ALL
SELECT IPaddress,3, UserName,  
FROM User3

Just some options

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources