Help: How to merge two tables into one table

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

Help: How to merge two tables into one table

Author

Topic

Aramis
Starting Member

13 Posts

Posted - 2009-08-24 : 02:48:14

Guys,

Need you help on how to merge or insert one table into another table.

Here's the example:

Here's my ex:

table1

ID Name Date Fruits
01 Juan 06/06/2009 mango
02 Pedro 06/06/2009 grapes
03 Peter 06/02/2009 apple

table2

ID Date Shipto
01 06/06/2009 USA
02 06/06/2009 RUSSIA
03 06/02/2009 CHINA

Now I want the table2 insert into table1 with the corresponding ID and DATE. PLs. help me how to do it. thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-24 : 02:52:05

[code]
insert into table1 (ID, Name, Date)
select ID, Shipto Date
from table2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Aramis
Starting Member

13 Posts

Posted - 2009-08-24 : 03:10:47

Thanks for the feedback...but i check on the database i creates duplicates records how can i rid of this duplicate data?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-24 : 03:13:36

what is the primary key ? ID ?


insert into table1 (ID, Name, Date)
select ID, Shipto Date
from   table2 t2
where  not exists
       (
           select *
           from   table1 t1
           where  t1.ID = t2.ID
       )

KH
[spoiler]Time is always against us[/spoiler]

Aramis
Starting Member

13 Posts

Posted - 2009-08-24 : 03:15:39

The primary key is the ID and Date

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-24 : 03:17:50

[code]

insert into table1 (ID, Name, Date)
select ID, Shipto, Date
from table2 t2
where not exists
(
select *
from table1 t1
where t1.ID = t2.ID
and t1.Date = t2.Date
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Aramis
Starting Member

13 Posts

Posted - 2009-08-24 : 03:18:48

Is this possible? that we have 2 keys?

I'm trying to create a table like this:

ID Name Date Fruits Shipto
01 Juan 06/06/2009 mango USA
02 Pedro 06/06/2009 grapes RUSSIA
03 Peter 06/02/2009 apple CHINA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-24 : 03:22:48

use INNER JOIN with ID & DATE as the join condition


select t1.ID, t1.Name, t1.Date, t1.Fruits, t2.Shipto
from   table1 t1
       inner join table2 t2 on  t1.ID   = t2.ID 
                            and t1.Date = t2.Date

KH
[spoiler]Time is always against us[/spoiler]

Aramis
Starting Member

13 Posts

Posted - 2009-08-24 : 03:26:44

KH,

If possible if i can insert it into one table...is this possible?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-24 : 03:34:44

yes. just put the INSERT STATEMENT before the SELECT query


INSERT INTO yourtable (ID, Name, Date, Fruits, Shipto)
select t1.ID, t1.Name, t1.Date, t1.Fruits, t2.Shipto
from   table1 t1
       inner join table2 t2 on  t1.ID   = t2.ID 
                            and t1.Date = t2.Date

KH
[spoiler]Time is always against us[/spoiler]

Aramis
Starting Member

13 Posts

Posted - 2009-08-24 : 04:23:16

Thanks a lot KH, it works!

Aramis
Starting Member

13 Posts

Posted - 2009-08-25 : 03:25:53

Hi KH! I got a question how about the remaining datas in the table1 that are not equal on t1.ID = t2.ID and t1.Date = t2.Date. I need also the all the datas in table 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-25 : 03:29:00

then change from INNER JOIN to LEFT JOIN

KH
[spoiler]Time is always against us[/spoiler]

Aramis
Starting Member

13 Posts

Posted - 2009-08-25 : 04:19:34

ok thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources