Creating new table from existing select statement

Question

I am creating a new table into database. My code is below

CREATE TABLE Master_data AS
select a.*, COALESCE(b.content_view,0) as content_view, COALESCE(b.headline_view,0) as headline_view
FROM [Britannia_MMx_2021].[dbo].[Sales_20210323] a
left join [Britannia_MMx_2021].[dbo].[Doximity_20220328] b on a.NPI = b.NPI
           and a.Year_Mon = b.Date;

I am getting this error

Msg 102, Level 15, State 1, Line 46
Incorrect syntax near '('.

Are you sure this is MySQL?

FanoFN
– FanoFN

2022-03-29 02:45:06 +00:00
Commented Mar 29, 2022 at 2:45 — FanoFN
– FanoFN, Commented Mar 29, 2022 at 2:45
how come you error is on line 46, provided SQL has 5 lines

Mauricio Gracia Gutierrez
– Mauricio Gracia Gutierrez

2022-03-29 02:47:04 +00:00
Commented Mar 29, 2022 at 2:47 — Mauricio Gracia Gutierrez
– Mauricio Gracia Gutierrez, Commented Mar 29, 2022 at 2:47

Paul Maxwell · Accepted Answer · 2022-03-29 02:58:50Z

1

Your SQL snippet displays characteristics of TSQL. In TSQL (e.g. in MS SQL Server) you do not use "create table as" followed by a select statement, instead you use an INTO clause e.g.

SELECT a.*
    , COALESCE(b.content_view, 0) AS content_view
    , COALESCE(b.headline_view, 0) AS headline_view
INTO Master_data
FROM [Britannia_MMx_2021].[dbo].[Sales_20210323] a
LEFT JOIN [Britannia_MMx_2021].[dbo].[Doximity_20220328] b ON a.NPI = b.NPI
    AND a.Year_Mon = b.DATE;

In MySQL you do use CREATE TABLE [ IF NOT EXISTS ] new_table [ AS ]

answered Mar 29, 2022 at 2:58

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating new table from existing select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related