February 3, 2014 at 1:30 am
Hi All,
Please help me in Merging table :
--------Dummy TABLE
create table #Tbl1 (date1 date,WSH varchar(10),ITN int,Executions int)
insert into #Tbl1 (date1 , WSH , ITN , Executions)
select '20130202' ,'ABC', 1 , 100
union all
select '20130203' ,'DEF', 1 , 200
go
create table #Tbl2 (date1 date,MCG int,Positions int)
insert into #Tbl2 (date1 , MCG , Positions)
select '20130202' , 2 , 500
union all
select '20130205' , 2 , 600
SELECT * FROM #Tbl1
SELECT * FROM #Tbl2
------SELECT available data FROM BOTH tables
select COALESCE(t1.date1,t2.date1) as date,t1.wsh as wsh,t1.ITN,
t1.Executions,t2.MCG,t2.Positions
from #Tbl1 t1 full outer join #Tbl2 t2 on 1=0
--DROP TABLE #Tbl1
--DROP TABLE #Tbl2
I want Result like this
date1 WSH ITNExecutionsMCGPositions
2013-02-02 ABC 1 100 2 500
2013-02-03 DEF1 200 NULL NULL
2013-02-05 NULLNULL NULL 2 600
Thanks
February 3, 2014 at 1:50 am
select COALESCE(t1.date1,t2.date1),
t1.wsh as wsh,t1.ITN,
t1.Executions,t2.MCG,t2.Positions
from #Tbl1 t1 full outer join #Tbl2 t2 on t1.date1 = t2.date1
Order by 1
Regards,
Mitesh Oswal
+8698619998
Regards,
Mitesh OSwal
+918698619998
February 3, 2014 at 2:03 am
Thanks Mitesh. 🙂
I also find the way way to do it , but with MERGE : --
DECLARE @tbl11 TABLE (date1 DATE ,WSH VARCHAR(3),ITN int ,Executions INT ,MCG INT , Positions INT )
DECLARE @tbl22 TABLE (date1 DATE ,WSH VARCHAR(3),ITN int ,Executions INT ,MCG INT , Positions INT )
INSERT INTO @tbl11
SELECT date1 ,WSH ,ITN ,Executions,NULL AS MCG, NULL AS Positions FROM #tbl1
INSERT INTO @tbl22
SELECT date1 ,CAST (NULL AS VARCHAR(10)) as WSH ,NULL AS ITN ,NULL Executions, MCG, Positions FROM #tbl2
SELECT * FROM @tbl11
SELECT * FROM @tbl22
MERGE @tbl11 AS t1
USING (SELECT date1 ,WSH ,ITN ,Executions, MCG, Positions FROM @tbl22) AS t2
ON t1.date1 = t2.date1
WHEN MATCHED THEN UPDATE SET t1.date1 = COALESCE(t1.date1,t2.date1),t1.wsh=COALESCE(t1.wsh,t2.wsh),
t1.ITN=COALESCE(t1.ITN,t2.ITN) ,t1.Executions=COALESCE(t1.Executions,t2.Executions),t1.MCG=COALESCE(t1.MCG,t2.MCG) ,t1.Positions=COALESCE(t1.Positions,t2.Positions)
WHEN NOT MATCHED THEN
INSERT(date1 ,WSH ,ITN ,Executions, MCG, Positions)
VALUES(t2.date1 ,t2.WSH ,t2.ITN ,t2.Executions, t2.MCG, Positions);
SELECT * FROM @tbl11
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply