Combining INTO with UNION

[Andy Kent]

What's the syntax for combining INTO and UNION clauses?

What I want to do is:

SELECT blah
INTO newtable
FROM oldtable1
WHERE <conditions>
UNION
SELECT blah
INTO newtable
FROM oldtable2
WHERE <conditions>

DTS doesn't seem to like my syntax. Where should the INTO(s) really go?

Thanks

Andy

[MC]

Re: Combining INTO with UNION

Try with something like this:

select <column list>
into newtable
from
(
select <column list>
from table1

union

select <column list>
from table2
) un


MC


"Andy Kent" <andykent.brist ol1095@virgin.n et> wrote in message
news:1132663862 .886236.138010@ g43g2000cwa.goo glegroups.com.. .[color=blue]
> What's the syntax for combining INTO and UNION clauses?
>
> What I want to do is:
>
> SELECT blah
> INTO newtable
> FROM oldtable1
> WHERE <conditions>
> UNION
> SELECT blah
> INTO newtable
> FROM oldtable2
> WHERE <conditions>
>
> DTS doesn't seem to like my syntax. Where should the INTO(s) really go?
>
> Thanks
>
> Andy
>[/color]

[Andy Kent]

Re: Combining INTO with UNION

Thanks, that worked...

Next part of problem: how would I get it to create and populate an
IDENTITY column?

Bearing in mind I'm working in DTS, which doesn't like anything too
clever ...

[MC]

Re: Combining INTO with UNION

well, you can add identity column after you insert data. So, after insert
issue:
go
alter table table1
add Something_ID int identity(1,1)

LMK if you need anything else

MC

"Andy Kent" <andykent.brist ol1095@virgin.n et> wrote in message
news:1132669133 .662008.221370@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> Thanks, that worked...
>
> Next part of problem: how would I get it to create and populate an
> IDENTITY column?
>
> Bearing in mind I'm working in DTS, which doesn't like anything too
> clever ...
>[/color]

[Stu]

Re: Combining INTO with UNION

A poor carpenter blames his tools.

Not trying to pick on you, but DTS is very powerful; there are some
things that it's not good at, but chewing up CORRECT SQL syntax is not
one of them.

If you want to add an identity column in one step, you can use a
subquery for the UNION statement:

SELECT SELECT IDENTITY(int,1, 1) as splat, blah
INTO newTable
FROM (SELECT blah
FROM oldtable
UNION --do you need DISTINCT values? UNION ALL will be faster
SELECT blah
FROM oldTable2) x

Stu

[Gert-Jan Strik]

Re: Combining INTO with UNION

You were quite close... Here's an example:

Use Northwind
GO

SELECT OrderID
INTO #t
FROM Orders
UNION
SELECT ProductID
FROM "Order Details"

HTH,
Gert-Jan


Andy Kent wrote:[color=blue]
>
> What's the syntax for combining INTO and UNION clauses?
>
> What I want to do is:
>
> SELECT blah
> INTO newtable
> FROM oldtable1
> WHERE <conditions>
> UNION
> SELECT blah
> INTO newtable
> FROM oldtable2
> WHERE <conditions>
>
> DTS doesn't seem to like my syntax. Where should the INTO(s) really go?
>
> Thanks
>
> Andy[/color]