469,631 Members | 1,116 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,631 developers. It's quick & easy.

Combining INTO with UNION

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

Nov 23 '05 #1
5 33695
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

Nov 23 '05 #2
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 ...

Nov 23 '05 #3
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 ...

Nov 23 '05 #4
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

Nov 23 '05 #5
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

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by Phillip Rhodes | last post: by
2 posts views Thread by spatik | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.