Prev: Finding a row with the heigest/lowest value under specific conditions.
Next: Unable to delete Database backup in SQL Server 2005/2008
From: Siv g at sivill dot on 15 Jul 2010 11:38
Hi,
I am sure there is a way that you can have a query that effectively takes
two separate tables that have the identical column layout and add them
together and then perofrm a query on the combined set of data. I just can't
remember how to do it.

My reason for need in this is that I have a table called "SellerNumbers"
that has 4 million rows of data in it and what I want to do to improve
performance is archive the oldest 2 years worth of data to an identical table
called "SellerNumbersArchive", however the users want to be able to
occasionaly run queries across the total set of data that would then be in
the two tables.

I seem to remeber somehwere in my Transact SQL memory there was an SQL
command that you could use that would effectively concatenate the rows of the
archive table to the rows of the current table and then treat the combination
as a single table for the purposes of the SQL Query.

I am getting old and it just won't come back to me how I did it.

If anyone can tell me how to do it I would be most grateful!

Siv
--
Martley, Near Worcester, UK
From: Siv g at sivill dot on 15 Jul 2010 12:38
I finally remembered "UNION":

The following is from Books Online:

Combining Results with UNION
The UNION operator allows you to combine the results of two or more SELECT
statements into a single result set. The result sets combined using UNION
must all have the same structure. They must have the same number of columns,
and the corresponding result set columns must have compatible data types. For
more information, see Guidelines for Using UNION.

UNION is specified as:

select_statement UNION [ALL] select_statement

For example, Table1 and Table2 have the same two-column structure.

Table1 Table2
ColumnA ColumnB ColumnC ColumnD
char(4) int char(4) int
------- --- ------- ---
abc 1 ghi 3
def 2 jkl 4
ghi 3 mno 5



This query creates a UNION between the tables:

SELECT * FROM Table1
UNION
SELECT * FROM Table2

Here is the result set:

ColumnA ColumnB
------- --------
abc 1
def 2
ghi 3
jkl 4
mno 5

The result set column names of a UNION are the same as the column names in
the result set of the first SELECT statement in the UNION. The result set
column names of the other SELECT statements are ignored.

By default, the UNION operator removes duplicate rows from the result set.
If you use ALL, all rows are included in the results and duplicates are not
removed.

The exact results of a UNION operation depend on the collation chosen during
installation and the ORDER BY clause. For more information about the effects
of different collations, see SQL Server Collation Fundamentals.

Any number of UNION operators can appear in a Transact-SQL statement, for
example:

SELECT * FROM TableA
UNION
SELECT * FROM TableB
UNION
SELECT * FROM TableC
UNION
SELECT * FROM TableD

By default, Microsoft® SQL Server™ 2000 evaluates a statement containing
UNION operators from left to right. Use parentheses to specify the order of
evaluation. For example, the following statements are not equivalent:

/* First statement. */
SELECT * FROM TableA
UNION ALL
( SELECT * FROM TableB
UNION
SELECT * FROM TableC
)
GO

/* Second statement. */
(SELECT * FROM TableA
UNION ALL
SELECT * FROM TableB
)
UNION
SELECT * FROM TableC)
GO

In the first statement, duplicates are eliminated in the union between
TableB and TableC. In the union between that set and TableA, duplicates are
not eliminated. In the second statement, duplicates are included in the union
between TableA and TableB but are eliminated in the subsequent union with
TableC. ALL has no effect on the final result of this expression.

When UNION is used, the individual SELECT statements cannot have their own
ORDER BY or COMPUTE clauses. There can be only one ORDER BY or COMPUTE clause
after the last SELECT statement; it is applied to the final, combined result
set. GROUP BY and HAVING can be specified only in the individual SELECT
statements.


--
Martley, Near Worcester, UK


"Siv" wrote:

> Hi,
> I am sure there is a way that you can have a query that effectively takes
> two separate tables that have the identical column layout and add them
> together and then perofrm a query on the combined set of data. I just can't
> remember how to do it.
>
> My reason for need in this is that I have a table called "SellerNumbers"
> that has 4 million rows of data in it and what I want to do to improve
> performance is archive the oldest 2 years worth of data to an identical table
> called "SellerNumbersArchive", however the users want to be able to
> occasionaly run queries across the total set of data that would then be in
> the two tables.
>
> I seem to remeber somehwere in my Transact SQL memory there was an SQL
> command that you could use that would effectively concatenate the rows of the
> archive table to the rows of the current table and then treat the combination
> as a single table for the purposes of the SQL Query.
>
> I am getting old and it just won't come back to me how I did it.
>
> If anyone can tell me how to do it I would be most grateful!
>
> Siv
> --
> Martley, Near Worcester, UK
From: John Bell on 15 Jul 2010 17:19
On Thu, 15 Jul 2010 08:38:23 -0700, Siv <g at sivill dot com> wrote:

>Hi,
>I am sure there is a way that you can have a query that effectively takes
>two separate tables that have the identical column layout and add them
>together and then perofrm a query on the combined set of data. I just can't
>remember how to do it.
>
>My reason for need in this is that I have a table called "SellerNumbers"
>that has 4 million rows of data in it and what I want to do to improve
>performance is archive the oldest 2 years worth of data to an identical table
>called "SellerNumbersArchive", however the users want to be able to
>occasionaly run queries across the total set of data that would then be in
>the two tables.
>
>I seem to remeber somehwere in my Transact SQL memory there was an SQL
>command that you could use that would effectively concatenate the rows of the
>archive table to the rows of the current table and then treat the combination
>as a single table for the purposes of the SQL Query.
>
>I am getting old and it just won't come back to me how I did it.
>
>If anyone can tell me how to do it I would be most grateful!
>
>Siv


Hi

You are talking about a partitioned view, although in SQL 2005 and
above you have partitioned tables that you could use to the same
effect.

John
From: Erland Sommarskog on 15 Jul 2010 17:47
Siv (g at sivill dot com) writes:
> I finally remembered "UNION":

Note that in your case, you want UNION ALL, since UNION implies
DISTINCT, which adds an extra sorting step.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Finding a row with the heigest/lowest value under specific conditions.
Next: Unable to delete Database backup in SQL Server 2005/2008