INTO Clause with UNION? 
Author Message
 INTO Clause with UNION?

How does one create a table that is the result of a UNION SELECT?

The following statement works and generates a result set:

SELECT CatData.PartNum, CatData.Description, CatData.PageNum,
CatData.ItemNum
FROM CatData
UNION
SELECT DupLinks.PartNum, CatData.Description, DupLinks.PageNum,
DupLinks.ItemNum
FROM DupLinks
LEFT JOIN CatData ON CatData.PartNum = DupLinks.PartNum;

But, if I add an "INTO NewTable" clause I get an error message. I've
tried it as part of the first SELECT, as part of the second SELECT and
following the JOIN clause - none of which work.

How do I do this?

Thanks

Martin



Mon, 06 Oct 2003 23:36:12 GMT
 INTO Clause with UNION?

Martin,

This should do it....

Select x.* INTO NewTable from
(SELECT CatData.PartNum, CatData.Description, CatData.PageNum,
CatData.ItemNum
FROM CatData
UNION
SELECT DupLinks.PartNum, CatData.Description, DupLinks.PageNum,
DupLinks.ItemNum
FROM DupLinks
LEFT JOIN CatData ON CatData.PartNum = DupLinks.PartNum) x

hth.

-oj


Quote:
> How does one create a table that is the result of a UNION SELECT?

> The following statement works and generates a result set:

> SELECT CatData.PartNum, CatData.Description, CatData.PageNum,
> CatData.ItemNum
> FROM CatData
> UNION
> SELECT DupLinks.PartNum, CatData.Description, DupLinks.PageNum,
> DupLinks.ItemNum
> FROM DupLinks
> LEFT JOIN CatData ON CatData.PartNum = DupLinks.PartNum;

> But, if I add an "INTO NewTable" clause I get an error message. I've
> tried it as part of the first SELECT, as part of the second SELECT and
> following the JOIN clause - none of which work.

> How do I do this?

> Thanks

> Martin



Tue, 07 Oct 2003 01:26:36 GMT
 INTO Clause with UNION?
Martin,

The INTO clause must go on the first SELECT of the UNION.  Yes, I know
you tried it there, but that is where it goes.  Remember it comes
right after the SELECT and before the FROM - I know I often slip and
place it after the FROM.

Roy

Quote:

>How does one create a table that is the result of a UNION SELECT?

>The following statement works and generates a result set:

>SELECT CatData.PartNum, CatData.Description, CatData.PageNum,
>CatData.ItemNum
>FROM CatData
>UNION
>SELECT DupLinks.PartNum, CatData.Description, DupLinks.PageNum,
>DupLinks.ItemNum
>FROM DupLinks
>LEFT JOIN CatData ON CatData.PartNum = DupLinks.PartNum;

>But, if I add an "INTO NewTable" clause I get an error message. I've
>tried it as part of the first SELECT, as part of the second SELECT and
>following the JOIN clause - none of which work.

>How do I do this?

>Thanks

>Martin



Tue, 07 Oct 2003 01:29:55 GMT
 INTO Clause with UNION?
Thanks, oj.   That worked perfectly.
Quote:

>Martin,

>This should do it....

>Select x.* INTO NewTable from
>(SELECT CatData.PartNum, CatData.Description, CatData.PageNum,
>CatData.ItemNum
>FROM CatData
>UNION
>SELECT DupLinks.PartNum, CatData.Description, DupLinks.PageNum,
>DupLinks.ItemNum
>FROM DupLinks
>LEFT JOIN CatData ON CatData.PartNum = DupLinks.PartNum) x

>hth.

>-oj



>> How does one create a table that is the result of a UNION SELECT?

>> The following statement works and generates a result set:

>> SELECT CatData.PartNum, CatData.Description, CatData.PageNum,
>> CatData.ItemNum
>> FROM CatData
>> UNION
>> SELECT DupLinks.PartNum, CatData.Description, DupLinks.PageNum,
>> DupLinks.ItemNum
>> FROM DupLinks
>> LEFT JOIN CatData ON CatData.PartNum = DupLinks.PartNum;

>> But, if I add an "INTO NewTable" clause I get an error message. I've
>> tried it as part of the first SELECT, as part of the second SELECT and
>> following the JOIN clause - none of which work.

>> How do I do this?

>> Thanks

>> Martin



Tue, 07 Oct 2003 03:00:26 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Order By clause with UNION : SQL Server 2000

2. UNION Clause unsupporter?

3. Using UNION and ORDER BY clauses

4. UNION clause syntax question

5. UNION Clause Question

6. union in where clause causing problems?

7. Stored Procedure - TOP/UNION clauses

8. Union Clause help needed

9. UNION clause questions

10. Union Clause

11. Parameterised queries and UNION clause

12. Help : Order by with Union clause


 
Powered by phpBB® Forum Software