CODEWITHSUNDEEP
sqlsql-server
Samar Alaa
Samar Alaa

Reputation: 13

how to use union in sql

Hello everyone in SQL if am trying to union two tables in a new one , and am taking the columns names from these table which names its going to take for the new table and why ?

Code:

CREATE  TABLE New AS 
Select Phonenumber from Data 
union 
select PhoneNumber from INFO

Incorrect syntax near the keyword 'AS'

Upvotes: 0

Views: 137

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Your query looks correct, assuming your database support create table as. However, new could easily be a reserved word in your database. I would recommend a more informative name. Something like this:

CREATE TABLE PhoneNumbers AS
    SELECT Phonenumber FROM Data 
    UNION
    SELECT PhoneNumber FROM INFO;

In SQL Server (the tag that was recently added), the correct syntax uses INTO:

SELECT Phonenumber FROM Data 
INTO PhoneNumbers
UNION
SELECT PhoneNumber FROM INFO;

Upvotes: 2

Jande
Jande

Reputation: 1705

You can use with clause like this:

with cte
as
(

Select Phonenumber from [Data] 
union 
select PhoneNumber from .[INFO]
)

SELECT Phonenumber INTO dbo.NEW_TABLE
FROM cte;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you could use parenthesis 

SELECT x.* 
  INTO [NEW_TABLE]
  FROM 
(Select Phonenumber from Data 
 union      
 select PhoneNumber from INFO
 ) x

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Your error suggests me SQL Server DBMS if so then you use SELECT . . . INTO statement :

SELECT Phonenumber INTO New_table 
FROM Data 
UNION
SELECT PhoneNumber 
FROM INFO;

If the new_table already exists then you can do instead : 

INSERT INTO  New_table (Phonenumber)
    SELECT Phonenumber INTO New_table 
    FROM Data 
    UNION
    SELECT PhoneNumber 
    FROM INFO;

Upvotes: 1

Roberto Góes
Roberto Góes

Reputation: 828

For you use union, all the selection groups that you want to use union must have the same number of fields with their own names.

Try:

CREATE TABLE New_table SELECT * FROM (
    SELECT Phonenumber FROM Data 
    UNION
    SELECT PhoneNumber FROM INFO
 );

Upvotes: 0

Related Questions