CODEWITHSUNDEEP
sql-serverselectinsertunion
Walker
Walker

Reputation: 1307

How to use UNION while inserting in a table using SELECT clause from many tables in SQL Server 2000

I am using SQL Server 2000. I have multiple criterias which i have to use to extract different sets of records from mumtiple table in another table. I am using 

INSERT INTO T1(A,B)
(SELECT E,R FROM T2)
UNION
(SELECT Z,X FROM T3)

Query analyzer is throwing error that while using UNION, you should use ORDER BY clause with SELECT. Even after doing that i m not able to union two different queries which are returing same columns in their select clause.

What is the right way to insert using SELECTS with UNIONS and ORDER BY.

Upvotes: 1

Views: 23314

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

The pseudo is too cryptic (reduced?) It is very unlikely to get 2 columns per cross join of 2 tables in each of the union components

INSERT INTO T1(A,B)
(SELECT * FROM E,R)
UNION
(SELECT * FROM Z,X)

Note: If you have ANY order by clause at all, it must be at the end of the union

INSERT T1(A,B)
SELECT P,Q FROM E,R
UNION
SELECT R,S FROM Z,X

@updated based on error text "Server: Msg 104, Level 15, State 1, Line 1 ORDER BY items must appear in the select list if the statement contains a UNION operator"

This occurs when you have a union that attempts to perform ORDER BY on a column that does not appear in the result. Consider a normal ORDER BY involving non-selected columns

select top 10 name from syscolumns order by xtype

The rows are consistent and the query can be satisfied. However, if you did

select top 10 name from syscolumns where xtype > 50
union all
select top 10 name from syscolumns where xtype < 50
order by xtype

EVEN IF xtype exists in both parts of the UNION, but the time it gets presented to ORDER BY (which works at the END over the entire result set), the column is not there. You would have to rewrite it (if you didn't want to show xtype) as

select name from (
    select top 10 name, xtype from syscolumns where xtype > 50
    union all
    select top 10 name, xtype from syscolumns where xtype < 50
) x
order by xtype

Hope that helps

Upvotes: 3

Related Questions