how to use union in sql

Question

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'

Looks like the DBMS product you are using doesn't support the standard `CREATE TABLE ... AS` syntax. — , Feb 05 '19 at 12:14

Gordon Linoff · Answer 1 · 2019-02-05T13:20:08.897

2

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;

edited Feb 05 '19 at 13:20

answered Feb 05 '19 at 12:09

Gordon Linoff

1,242,037
58
646
786

still not working , same error 'Incorrect syntax near the keyword 'AS' ' – Samar Alaa Feb 05 '19 at 12:31
should be a peculiarity of sqlserver See the answer below or See https://stackoverflow.com/a/4018727/5273803 – Roberto Góes Feb 05 '19 at 12:43

score 1 · Accepted Answer · answered Feb 05 '19 at 12:22

1

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;

answered Feb 05 '19 at 12:22

Yogesh Sharma

49,870
5
26
52

new_table doesnt exist yet i am creating a new table so i will have to use create table right ? – Samar Alaa Feb 05 '19 at 12:31
@SamarAlaa. . . If the `new_table` doesn't exists then use first version of query. – Yogesh Sharma Feb 05 '19 at 12:32
it worked , thank you so much , i have another question , when we do so for multiple columns which Headers its going to take the one for the 1st table or the second one and why ,, for example if we have table 1 has Name Phone address , table 2 has FullName Phonenumber Homeaddress , which headers it going to take for the new table and why ?? – Samar Alaa Feb 05 '19 at 12:42
@SamarAlaa. . . In that case you have to explicitly re-name column_name & yes the first `SELECT` statement column_name will use as final `Column_name`. – Yogesh Sharma Feb 05 '19 at 12:45
1

so if i didnt name the coloumns it will take the Names in the 1st selecte query ! , perfect thank you so much again u r a life saver :) – Samar Alaa Feb 05 '19 at 12:53

score 1 · Answer 3 · answered Feb 05 '19 at 12:26

1

you could use parenthesis

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

answered Feb 05 '19 at 12:26

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 4 · answered Feb 05 '19 at 12:13

0

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
 );

answered Feb 05 '19 at 12:13

Roberto Góes

778
1
13
20

i wrote this exactly and it gives an error ' msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'select'. Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')' ' – Samar Alaa Feb 05 '19 at 12:37

score 0 · Answer 5 · answered Feb 05 '19 at 12:37

0

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;

answered Feb 05 '19 at 12:37

Jande

1,695
2
20
32

how to use union in sql

5 Answers5