CODEWITHSUNDEEP
sqlsql-serverunion
LewSim
LewSim

Reputation: 327

Union temporary tables to a final temporary table

I have like 10 diff temporary tables created in SQL server, what I am looking to do is union them all to a final temporary table holding them all on one table. All the tables have only one row and look pretty much exactly like the two temp tables below.

Here is what I have so far this is an example of just two of the temp tables as their all exactly like this one then #final is the table I want to union the all to:

      create table #lo
      (
         mnbr bigint
      )
      insert into #login (mnbr)
      select distinct (_ID)
      FROM [KDB].[am_LOGS].[dbo].[_LOG] 
      WHERE time >= '2012-7-26 9:00:00

       Select count(*) as countreject
       from #lo

     create table #pffblo
     (
       mber_br
     )
     insert into #pffblo (mber_br)
     select distinct (mber_br)
     from individ ip with (nolock)
     join memb mp with (nolock)
          on( ip.i_id=mp.i_id and mp.p_type=101) 
      where ip.times >= '2012-9-26 11:00:00.000'

     select count(*) as countaccept

    create table #final
    (
        countreject bigint
        , Countacceptbigint
        .....
    )

    insert into #final (Countreject, Countaccept....more rows here...)
    select Countreject, Countaccept, ...more rows selected from temp tables.
    from #final
    union 
    (select * from #lo)
    union
    (select * from #pffblo)
    select *
    from #final

drop table #lo
drop table #pffblo
drop table #final

if this the form to union the rows form those temp tables to this final one. Then is this correct way to show all those rows that were thus unioned. When I do this union I get message number of columns in union need to match number of columns selected in union

Upvotes: 2

Views: 61995

Answers (4)

user4634351
user4634351

Reputation: 39

SELECT * 
INTO #1
FROM TABLE2
UNION
SELECT *
FROM TABLE3
UNION
SELECT *
FROM TABLE4

Upvotes: 3

Greg
Greg

Reputation: 3522

I think you're using a union the wrong way. A union is used when you have to datasets that are the same structure and you want to put them into one dataset.

e.g.:

CREATE TABLE #Table1
(
  col1 BIGINT
)

CREATE TABLE #Table2
(
  col1 BIGINT
)

--populate the temporary tables

CREATE TABLE #Final
(
  col1 BIGINT
)

INSERT INTO #Final (col1)
SELECT *
FROM #Table1
UNION
SELECT *
FROM #Table2

drop table #table1
drop table #table2
drop table #Final

I think what you're trying to do is get 1 data set with the count of all your tables in it. Union won't do this.

The easiest way (although not very performant) would be to do select statements like the following:

CREATE TABLE #Table1
(
  col1 BIGINT
)

CREATE TABLE #Table2
(
  col1 BIGINT
)

--populate the temporary tables

CREATE TABLE #Final
(
  col1 BIGINT,
  col2 BIGINT
)




INSERT INTO #Final (col1, col2)
select (SELECT Count(*) FROM #Table1) as a, (SELECT Count(*) FROM #Table2) as b

select * From #Final

drop table #table1
drop table #table2
drop table #Final

Upvotes: 3

Farfarak
Farfarak

Reputation: 1527

If you would like to get count for each temporary table in the resulting table, you will need just to calculate it for each column in subquery:

INSERT INTO result (col1, col2,... SELECT (SELECT COUNT() FROM tbl1) col1 ,(SELECT COUNT() FROM tbl2) col2 ..

Upvotes: 0

Taryn
Taryn

Reputation: 247710

It appears that you want to take the values from each of temp tables and then place then into a single row of data. This is basically a PIVOT, you can use something like this:

create table #final
(
   countreject bigint
   , Countaccept bigint
        .....
)

insert into #final (Countreject, Countaccept....more rows here...)
select
from
(
  select count(*) value, 'Countreject' col  -- your UNION ALL's here
  from #lo
  union all
  select count(*) value, 'countaccept' col
  from #pffblo
) x
pivot
(
  max(value)
  for col in ([Countreject], [countaccept])
) p

Explanation:

You will create a subquery similar to this that will contain the COUNT for each of your individual temp table. There are two columns in the subquery, one column contains the count(*) from the table and the other column is the name of the alias:

  select count(*) value, 'Countreject' col  
  from #lo
  union all
  select count(*) value, 'countaccept' col
  from #pffblo

You then PIVOT these values to insert into your final temp table.

If you do not want to use PIVOT, then you can use a CASE statement with an aggregate function:

insert into #final (Countreject, Countaccept....more rows here...)
select max(case when col = 'Countreject' then value end) Countreject,
   max(case when col = 'countaccept' then value end) countaccept
from
(
  select count(*) value, 'Countreject' col  -- your UNION ALL's here
  from #lo
  union all
  select count(*) value, 'countaccept' col
  from #pffblo
) x

Or you might be able to JOIN all of the temp tables similar to this, where you create a row_number() for the one record in the table and then you join the tables with the row_number():

insert into #final (Countreject, Countaccept....more rows here...)
select isnull(lo.Countreject, 0) Countreject, 
       isnull(pffblo.Countaccept, 0) Countaccept
from
(
  select count(*) Countreject, 
    row_number() over(order by (SELECT 0)) rn
  from #lo
) lo
left join
(
  select count(*) Countaccept, 
    row_number() over(order by (SELECT 0)) rn
  from #pffblo
) pffblo
  on lo.rn = pffblo.rn

Upvotes: 2

Related Questions