none
combining temp tables in stored procedure RRS feed

  • Question

  • I have 2 temp tables in my procedure . I want to combine both of them . Could any one please help how to do this

    Temp 1 :

    enter image description here

    Temp 2 :

    enter image description here

    Output :

    enter image description here

    I tried with full join by joining Team column, but I am getting result like below.

    enter image description here

    Wednesday, May 13, 2020 11:22 AM

Answers

  • You didn't include the T-SQL query you tried or the desired results but I think you just need an inner join like:

    CREATE TABLE #team1(
    	 date date NOT NULL
    	,Team CHAR(2) NOT NULL
    	,Calls int NOT NULL
    	)
    CREATE TABLE #team2(
    	 date date NOT NULL
    	,Team CHAR(2) NOT NULL
    	,Messages int NOT NULL
    	);
    INSERT INTO #team1 (date, Team, Calls) VALUES
    	 ('2020-05-12','T1',12)
    	,('2020-05-12','T2',26)
    	,('2020-05-12','T3',46);
    INSERT INTO #team2 (date, Team, Messages) VALUES
    	 ('2020-05-12','T1',45)
    	,('2020-05-12','T2',27)
    	,('2020-05-12','T3',50);
    
    SELECT
    	 t1.date
    	,t1.team
    	,t1.Calls
    	,t2.Messages
    FROM #team1 AS t1
    JOIN #team2 AS t2 ON
    	t2.Date = t1.Date
    	AND t2.Team = t1.Team;

    This assumes each Date and Team uniquely identifies a row. If you want to include unmatching rows from either table, then use a FULL JOIN and specify COALESCE for the common columns:

    SELECT
    	 COALESCE(t1.date,t2.date) AS date
    	,COALESCE(t1.team,t2.team) AS team
    	,t1.Calls
    	,t2.Messages
    FROM #team1 AS t1
    FULL JOIN #team2 AS t2 ON
    	t2.Date = t1.Date
    	AND t2.Team = t1.Team;

     


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Wednesday, May 13, 2020 11:34 AM
    Moderator

All replies

  • You didn't include the T-SQL query you tried or the desired results but I think you just need an inner join like:

    CREATE TABLE #team1(
    	 date date NOT NULL
    	,Team CHAR(2) NOT NULL
    	,Calls int NOT NULL
    	)
    CREATE TABLE #team2(
    	 date date NOT NULL
    	,Team CHAR(2) NOT NULL
    	,Messages int NOT NULL
    	);
    INSERT INTO #team1 (date, Team, Calls) VALUES
    	 ('2020-05-12','T1',12)
    	,('2020-05-12','T2',26)
    	,('2020-05-12','T3',46);
    INSERT INTO #team2 (date, Team, Messages) VALUES
    	 ('2020-05-12','T1',45)
    	,('2020-05-12','T2',27)
    	,('2020-05-12','T3',50);
    
    SELECT
    	 t1.date
    	,t1.team
    	,t1.Calls
    	,t2.Messages
    FROM #team1 AS t1
    JOIN #team2 AS t2 ON
    	t2.Date = t1.Date
    	AND t2.Team = t1.Team;

    This assumes each Date and Team uniquely identifies a row. If you want to include unmatching rows from either table, then use a FULL JOIN and specify COALESCE for the common columns:

    SELECT
    	 COALESCE(t1.date,t2.date) AS date
    	,COALESCE(t1.team,t2.team) AS team
    	,t1.Calls
    	,t2.Messages
    FROM #team1 AS t1
    FULL JOIN #team2 AS t2 ON
    	t2.Date = t1.Date
    	AND t2.Team = t1.Team;

     


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Wednesday, May 13, 2020 11:34 AM
    Moderator
  • Hi Dan,

    Thank you.

    I tried this, But what some times there would be no data in my second temp tables.

    In that case I am not getting even first temp table data itself as I am using inner joins

    Its vice vesra too for the first table 

    Wednesday, May 13, 2020 11:48 AM
  • I tried this, But what some times there would be no data in my second temp tables.

    In that case I am not getting even first temp table data itself as I am using inner joins

    Its vice vesra too for the first table 


    Perhaps you didn't see it but I edited my answer to add a FULL JOIN example to handle missing rows.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, May 13, 2020 11:56 AM
    Moderator
  • Wednesday, May 13, 2020 12:08 PM
  • Hi friend,

    >>>I tried this, But what some times there would be no data in my second temp tables.
    The FULL JOIN example provided by Dan can solve this problem.

    In addition If you can be sure that Temp 1 always has data, and there would be no data in Temp 2 sometimes, then you can use left join.

    Also check this interesting topic : inner join 2 tables but return all if 1 table empty.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 14, 2020 5:49 AM