What is the easiest way to do the follwoing.
I have 2 tables. I want all the rows from one table and what is left from
the second table.
I.e. table 1.
ID value
1 10
2 20
3 30
i.e. table 2
ID value
1 15
2 25
3 35
4 444
i want my final table to have the following (everything from table 1 and
left over from table 2)
ID value
1 10
2 20
3 30
4 4444
ThanksTry:
select
*
from
Table1
union all
select
*
from
Table2 t2
where not exists
(
select
*
from
Table2 t2
where
t2.[ID] = t1.[ID]
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Fab" <lazzaro@.rogers.com> wrote in message
news:ew4q9HKPGHA.3936@.TK2MSFTNGP10.phx.gbl...
What is the easiest way to do the follwoing.
I have 2 tables. I want all the rows from one table and what is left from
the second table.
I.e. table 1.
ID value
1 10
2 20
3 30
i.e. table 2
ID value
1 15
2 25
3 35
4 444
i want my final table to have the following (everything from table 1 and
left over from table 2)
ID value
1 10
2 20
3 30
4 4444
Thanks|||Here's another solution:
select table2.ID,
ISNULL(table1.value, table2.value) AS value
from table2
left outer join table1 on table2.ID = table1.ID
"Fab" <lazzaro@.rogers.com> wrote in message
news:ew4q9HKPGHA.3936@.TK2MSFTNGP10.phx.gbl...
> What is the easiest way to do the follwoing.
> I have 2 tables. I want all the rows from one table and what is left from
> the second table.
> I.e. table 1.
> ID value
> 1 10
> 2 20
> 3 30
> i.e. table 2
> ID value
> 1 15
> 2 25
> 3 35
> 4 444
> i want my final table to have the following (everything from table 1 and
> left over from table 2)
> ID value
> 1 10
> 2 20
> 3 30
> 4 4444
> Thanks|||Hi Tom.
Me thinks there's a problem with your query.
This should work.
select
*
from
Table1
union all
select
*
from
Table2 t1
where not exists
(
select
*
from
Table1 t2
where
t2.[ID] = t1.[ID]
)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uueE$JKPGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> *
> from
> Table1
> union all
> select
> *
> from
> Table2 t2
> where not exists
> (
> select
> *
> from
> Table2 t2
> where
> t2.[ID] = t1.[ID]
> )
> --
> Tom|||On Tue, 28 Feb 2006 15:02:56 -0500, Fab wrote:
>What is the easiest way to do the follwoing.
>I have 2 tables. I want all the rows from one table and what is left from
>the second table.
>I.e. table 1.
>ID value
>1 10
>2 20
>3 30
>i.e. table 2
>ID value
>1 15
>2 25
>3 35
>4 444
>i want my final table to have the following (everything from table 1 and
>left over from table 2)
>ID value
>1 10
>2 20
>3 30
>4 4444
>Thanks
>
Hi Fab,
SELECT t2.ID, COALESCE(t1.value, t2.value)
FROM Table2 AS t2
LEFT OUTER JOIN Table1 AS t1
ON t1.ID = t2.ID
(untested - see www.aspfaq.com/5006 if you prefer a tested solution)
Hugo Kornelis, SQL Server MVP|||Another way is to use "full join".
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eJPO4mKPGHA.420@.tk2msftngp13.phx.gbl...
> Hi Tom.
> Me thinks there's a problem with your query.
> This should work.
> select
> *
> from
> Table1
> union all
> select
> *
> from
> Table2 t1
> where not exists
> (
> select
> *
> from
> Table1 t2
> where
> t2.[ID] = t1.[ID]
> )
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uueE$JKPGHA.2320@.TK2MSFTNGP11.phx.gbl...
>|||Ah, yes. Good catch.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eJPO4mKPGHA.420@.tk2msftngp13.phx.gbl...
Hi Tom.
Me thinks there's a problem with your query.
This should work.
select
*
from
Table1
union all
select
*
from
Table2 t1
where not exists
(
select
*
from
Table1 t2
where
t2.[ID] = t1.[ID]
)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uueE$JKPGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> *
> from
> Table1
> union all
> select
> *
> from
> Table2 t2
> where not exists
> (
> select
> *
> from
> Table2 t2
> where
> t2.[ID] = t1.[ID]
> )
> --
> Tom|||--@.@.@. TESTED and works
Create Table #tbl1
([ID] int,
[Value]varchar(10))
INSERT #tbl1 ([ID],[VALUE])
VALUES (1,'10')
INSERT #tbl1 ([ID],[VALUE])
VALUES (2,'20')
INSERT #tbl1 ([ID],[VALUE])
VALUES (3,'30')
Create Table #tbl2
([ID] int,
[Value]varchar(10))
INSERT #tbl2 ([ID],[VALUE])
VALUES (1,'15')
INSERT #tbl2 ([ID],[VALUE])
VALUES (2,'25')
INSERT #tbl2 ([ID],[VALUE])
VALUES (3,'35')
INSERT #tbl2 ([ID],[VALUE])
VALUES (4,'444')
-- View both
SELECT *
FROM #tbl1
SELECT *
FROM #tbl2
-- Combine to make all display
SELECT *
FROM #tbl1
UNION ALL
SELECT *
from #tbl2
where not exists(select *
from #tbl1
where [ID] = #tbl2.[ID])
--@.@.@. TESTED and works
"Fab" wrote:
> What is the easiest way to do the follwoing.
> I have 2 tables. I want all the rows from one table and what is left from
> the second table.
> I.e. table 1.
> ID value
> 1 10
> 2 20
> 3 30
> i.e. table 2
> ID value
> 1 15
> 2 25
> 3 35
> 4 444
> i want my final table to have the following (everything from table 1 and
> left over from table 2)
> ID value
> 1 10
> 2 20
> 3 30
> 4 4444
> Thanks
>
>|||Thanks to everyone for your help...
The solution Joseph provided worked.
:-)
"JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
news:E841A384-DEE2-4CC7-A939-A5F6C13DD975@.microsoft.com...
> --@.@.@. TESTED and works
> Create Table #tbl1
> ([ID] int,
> [Value]varchar(10))
> INSERT #tbl1 ([ID],[VALUE])
> VALUES (1,'10')
>
> INSERT #tbl1 ([ID],[VALUE])
> VALUES (2,'20')
>
> INSERT #tbl1 ([ID],[VALUE])
> VALUES (3,'30')
>
>
> Create Table #tbl2
> ([ID] int,
> [Value]varchar(10))
>
> INSERT #tbl2 ([ID],[VALUE])
> VALUES (1,'15')
>
> INSERT #tbl2 ([ID],[VALUE])
> VALUES (2,'25')
>
> INSERT #tbl2 ([ID],[VALUE])
> VALUES (3,'35')
> INSERT #tbl2 ([ID],[VALUE])
> VALUES (4,'444')
>
> -- View both
> SELECT *
> FROM #tbl1
> SELECT *
> FROM #tbl2
>
> -- Combine to make all display
> SELECT *
> FROM #tbl1
> UNION ALL
> SELECT *
> from #tbl2
> where not exists(select *
> from #tbl1
> where [ID] = #tbl2.[ID])
>
>
> --@.@.@. TESTED and works
> "Fab" wrote:
>
