UNION ALL

• soulchyld21

  Mr or Mrs. 500

  Points: 524

  More actions

  February 10, 2012 at 4:09 am

  #256546

  Hi, I have a query that requires that all the rows be 'unioned' I would like to use a temporary table to run this query on, how could I construct my temporary table and populate it, I have the data, millions of rows in an exitsing table, the exitsint table consists of 3 columns Customer_Number,Product and Price and I need these same rows in the (unioned) temporary table, Hope this makes sense!

  heres the query I need to run

  SELECT distinct Customer_Number, SUM(Price)

  FROM Cust_Purch x1

  WHERE

  EXISTS

  (SELECT * FROM Cust_Purch x2 WHERE x2.customer_number = x1.customer_number

  AND x2.product_offer = 'Offer1')

  AND NOT EXISTS

  (SELECT * FROM Cust_Purch x3 WHERE x3.customer_number = x1.customer_number AND

  x3.product_offer IN ( 'Offer5','Offer3') )

  GROUP BY Customer_Number

  The query works as I have tried it on some test data heres a copy of the test table and population

  insert into Cust_Purch

  select 1,'Blah', 23 union ALL

  select 1,'Offer4', 37 union ALL

  select 1,'Offer5', 75 union ALL

  select 5,'Offer1', 88 union ALL

  select 3,'Offer2', 63 union ALL

  select 4,'Offer2', 47 UNION ALL

  select 1,'Blah', 23 union ALL

  select 1,'Offer4', 37 union ALL

  select 1,'Offer5', 75 union ALL

  select 5,'Offer1', 88 union ALL

  select 3,'Offer2', 63 union ALL

  select 4,'Offer2', 47 UNION ALL

  select 3,'Blah', 23 union ALL

  select 2,'Offer4', 37 union ALL

  select 3,'Offer5', 75 union ALL

  select 2,'Offer1', 88 union ALL

  select 4,'Offer2', 63 union ALL

  select 4,'Offer2', 47 UNION ALL

  select 3,'Offer2', 63 union ALL

  select 4,'Offer2', 47 UNION ALL

  select 3,'Blah', 23 union ALL

  select 2,'Offer2', 37 union ALL

  select 3,'Offer2', 75 union ALL

  select 2,'Offer3', 88 union ALL

  select 4,'Offer4', 63 union ALL

  select 4,'Offer5', 47

  Now I need some help populating a temp table with existing data and telling it to union all rows!

• toddasd

  SSCertifiable

  Points: 6298

  More actions

  February 10, 2012 at 6:11 am

  #1445502

  To create a temp table from existing rows, use this type of syntax:

  select Customer_Number,Product, Price

  into #temp_Customer

  from RealCustomerTable

  ______________________________________________________________________________
  How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.