SQL: Select INTO using joins?

[Newsgroup_User]

I can do a simple select into easy enough:

SELECT * INTO myNewTable FROM myOldTable

How do I do that, but using a much more complex select statement using joins
and unions?

Would I uses a nested query for that?

SELECT (SELECT [my complex query]) INTO myNewTable FROM ???

-Darrel

[Newsgroup_User]

> SELECT (SELECT [my complex query]) INTO myNewTable FROM ???

on second thought, that wouldn't work.

A bit more background:

We have two servers, one internal, one external.

The internal one has 5 tables that we are using to track some data.

We need to make a copy of this for the public, but need to filter it as much
of the data is private data.

What we have been doing is using a DTS package to make a complex query of
joins and unions on the 5 tables and sending it out to the public server as
a single table.

The problem is that this DTS package is failing on occasion sans any errors.
So we can't really figure out what the issue may be.

As a workaround, I'm rewriting the DTS package as a stored procedure that
will then be triggered manually.

Maybe that's too much info. ;o)

_Darrel

[Newsgroup_User]

Let me rephrase one more time. ;o)

I think the specific question I have is:

how do I use select into while also using a UNION statement?

I have:

SELECT...
UNION...
SELECT...

If I do this:

SELECT...INTO...
UNION...
SELECT...INTO...

I get an error of

---------------------------
SELECT INTO must be the first query in an SQL statement containing a UNION
operator.
---------------------------

And that is where I get lost in SQL syntax land...

-Darrel

[Newsgroup_User]

> If I do this:
>
> SELECT...INTO...
> UNION...
> SELECT...INTO...
>
> I get an error

But NOT if I do this...

SELECT...INTO...
UNION...
SELECT...

That SEEMS wrong, but does appear to give me exactly what I am looking for.

-Darrel

[Newsgroup_User]

You could use a derived table. eg.

insert into myTable (f1)
select f1 from
(
select f1 from t1
union all
select f1 from t2
)

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004