Solved

using a union with a temporary table in SQL

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

773 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