Solved

using a union with a temporary table in SQL

Posted on 2012-03-30
8
230 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

691 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