Solved

using a union with a temporary table in SQL

Posted on 2012-03-30
8
222 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
  • 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
 

Author Closing Comment

by:morinia
ID: 37790668
Thanks mate.  It worked perfectly!
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now