Solved

using a union with a temporary table in SQL

Posted on 2012-03-30
8
229 Views
Last Modified: 2012-08-14
I have the following two select statements that I would like to put into a union.  Can someone give me the syntax

I would like to use a union and the put the results into #mbr_coverage

SELECT
  ml.member_id
, ml.member_last_name
, ml.member_first_name
, mc.enrollment_date
, mc.exit_date
, mc.primary_secondary
, mc.active_code
, mc.benefit_plan
, mc.benefit_product
, mc.benefit_group
, 'CURR' as ONFILE
into #mbr_curr
from prod.member_coverage mc (nolock)
   , #mbr_list ml (nolock)
where
   mc.member_id = ml.member_id
 order by ml.member_id
 ;



SELECT
  ml.member_id
, ml.member_last_name
, ml.member_first_name
, mc.enrollment_date
, mc.exit_date
, mc.primary_secondary
, mc.active_code
, mc.benefit_plan
, mc.benefit_product
, mc.benefit_group
, 'HIST' as ONFILE
into #mbr_hist
from prod.member_coverage_history mc (nolock)
   , #mbr_list ml (nolock)
where
   mc.member_id = ml.member_id
 order by ml.member_id
 ;
0
Comment
Question by:morinia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 37789906
Hi,

The better way in this instance instead of a select ... into ... from ... is an insert. That way you don't need a union.

The other question is that what do you want to have happen when there is a "collision" in member_id? That is, what if the same member is in both selects? Do you want one row or two? Normally a union implies distinct, but the ONFILE filed will make the two rows different.

Your union  looks like this:

select
      ml.member_id
      , ml.member_last_name
      , ml.member_first_name
      , mc.enrollment_date
      , mc.exit_date
      , mc.primary_secondary
      , mc.active_code
      , mc.benefit_plan
      , mc.benefit_product
      , mc.benefit_group
      , 'CURR' as ONFILE
from prod.member_coverage mc
inner join #mbr_list ml
      on ml.member_id = mc.member_id

union all

select
      ml.member_id
      , ml.member_last_name
      , ml.member_first_name
      , mc.enrollment_date
      , mc.exit_date
      , mc.primary_secondary
      , mc.active_code
      , mc.benefit_plan
      , mc.benefit_product
      , mc.benefit_group
      , 'HIST' as ONFILE
from prod.member_coverage_history mc
inner join #mbr_list ml
      on ml.member_id = mc.member_id

order by
      ml.member_id
;

Anyway, properly speaking, you don't need an order by on the insert - it just adds an unnecessary sort that saps performance. And since for a union can be replaced with individual inserts, and the output from the temp table can be sorted, you get this:

if object_id( N'tempdb..#mbr_coverage', N'U' ) is not null
      drop table #mbr_coverage;
      
create table #mbr_coverage(
      member_id int
      , member_last_name varchar( 50 )
      , member_first_name varchar( 50 )
      , enrollment_date datetime
      , exit_date datetime
      , primary_secondary varchar( 50 )
      , active_code varchar( 20 )
      , benefit_plan varchar( 50 )
      , benefit_product varchar( 50 )
      , benefit_group varchar( 50 )
      , ONFILE varchar( 20 )
      )
      
insert #mbr_coverage(
      member_id
      , member_last_name
      , member_first_name
      , enrollment_date
      , exit_date
      , primary_secondary
      , active_code
      , benefit_plan
      , benefit_product
      , benefit_group
      , ONFILE
      )
      select
            ml.member_id
            , ml.member_last_name
            , ml.member_first_name
            , mc.enrollment_date
            , mc.exit_date
            , mc.primary_secondary
            , mc.active_code
            , mc.benefit_plan
            , mc.benefit_product
            , mc.benefit_group
            , 'HIST' as ONFILE
      from prod.member_coverage_history mc
      inner join #mbr_list ml
            on ml.member_id = mc.member_id
;

insert #mbr_coverage(
      member_id
      , member_last_name
      , member_first_name
      , enrollment_date
      , exit_date
      , primary_secondary
      , active_code
      , benefit_plan
      , benefit_product
      , benefit_group
      , ONFILE
      )
      select
            ml.member_id
            , ml.member_last_name
            , ml.member_first_name
            , mc.enrollment_date
            , mc.exit_date
            , mc.primary_secondary
            , mc.active_code
            , mc.benefit_plan
            , mc.benefit_product
            , mc.benefit_group
            , 'CURR' as ONFILE
      from prod.member_coverage mc
      inner join #mbr_list ml
            on ml.member_id = mc.member_id
;

select
      c.member_id
      , c.member_last_name
      , c.member_first_name
      , c.enrollment_date
      , c.exit_date
      , c.primary_secondary
      , c.active_code
      , c.benefit_plan
      , c.benefit_product
      , c.benefit_group
      , c.ONFILE
from #mbr_coverage c
order by
      c.member_id
;

Obviously I've had to guess the types.

I've removed your nolock hints on the basis that if you don't know how to write a union or an insert then you aren't at the point to understand hints.

HTH
  David
0
 

Author Comment

by:morinia
ID: 37790234
I am running in SQL Server.  This is a SQL Query running in Exce.  Each query runs fine.  I wanted to put tem together in one statement with a union.  Nolock does not lock other users out od tthe table.
0
 
LVL 9

Accepted Solution

by:
OCDan earned 500 total points
ID: 37790635
This works as you want mate:

SELECT
  ml.member_id
, ml.member_last_name
, ml.member_first_name
, mc.enrollment_date
, mc.exit_date
, mc.primary_secondary
, mc.active_code
, mc.benefit_plan
, mc.benefit_product
, mc.benefit_group
, 'CURR' as ONFILE
into #mbr_coverage
from prod.member_coverage mc (nolock)
   , #mbr_list ml (nolock)
where
   mc.member_id = ml.member_id
UNION
SELECT
  ml.member_id
, ml.member_last_name
, ml.member_first_name
, mc.enrollment_date
, mc.exit_date
, mc.primary_secondary
, mc.active_code
, mc.benefit_plan
, mc.benefit_product
, mc.benefit_group
, 'HIST' as ONFILE
from prod.member_coverage_history mc (nolock)
   , #mbr_list ml (nolock)
where
   mc.member_id = ml.member_id


Although I would say don't use table1,table2 where t1.id = t2.id. Using a join is a much better and reliable way to go

e.g. table1 t1 join table2 t2 on t1.id=t2.id

If nothing else it allows you to keep your where clause isolated to just filtering criteria.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Closing Comment

by:morinia
ID: 37790668
Thanks mate.  It worked perfectly!
0
 
LVL 35

Expert Comment

by:David Todd
ID: 37791201
Hi,

>>  Nolock does not lock other users out of the table.

Nope. It doesn't do that it all. It allows dirty reads, that is uncommitted data to be read.

Strongly suggest you refrain from using locking hints at this stage. I don't believe its necessary, and it could be dangerous.

Regards
  David
0
 

Author Comment

by:morinia
ID: 37791326
Sorry David,

I did not mean to give a definition of locking functions.  I was just told in our shop to put (nolocK) when writing code against the production tables.  I was told because multiple users are entering data while I am running report queries, my queries will not lock them out while I am running reports.  This is why my code had (nolock).
0
 
LVL 35

Expert Comment

by:David Todd
ID: 37791372
Hi,

If that is what your shop said to put then fine. They should know what is going on better than me.

But still, you don't need to use it on your temp tables, as they are session specific so no one else will be accessing those tables.

Regards
  David
0
 

Author Comment

by:morinia
ID: 37791399
Thanks David,

Temp tables are new to me, I am trying to learn about them now.  I was using code from someone else.  You are correct the temp tables do not need nolock.  I assume the person put them in by habit.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question