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]

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" <an******************@virgin.net> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

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

[Andy Kent]

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]

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" <an******************@virgin.net> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

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 ...

[Stu]

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]

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:

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