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
Solved

using a union with a temporary table in SQL

Posted on 2012-03-30
8
228 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 32
MS SQL Update query with connected table data 3 30
Passing dataset name to stored procedure 3 19
SQL Server / Update DB? 22 36
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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

789 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