Create table with inner join sql

Question

I have to create a table using inner join. However, I get "Incorrect syntax near the keyword 'SELECT'" when I use the following code.

CREATE TABLE T1 AS
    SELECT Deliveries.SAS_DUBU.sag_hovedydelse.Cpr, Deliveries.SAS_DUBU.sag_hovedydelse.Ansvarlig, Deliveries.SAS_DUBU.sag_hovedydelse.YdelseStartdato, Deliveries.SAS_DUBU.sag_hovedydelse.YdelseSlutdato, Deliveries.SAS_DUBU.sag_hovedydelse.Ydelsestype, Deliveries.SAS_DUBU.sag_hovedydelse.LeveresAf, Deliveries.SAS_DUBU.sag_hovedydelse.Udgiftstekst, Deliveries.SAS_DUBU.sag_hovedydelse.InstantieretPaaBagrundAf, Deliveries.SAS_DUBU.sag_hovedydelse.DinBruger, Deliveries.SAS_DUBU.sag_ydelse_sup.Pris , Deliveries.SAS_DUBU.sag_ydelse_sup.PrisEnhed, Deliveries.SAS_DUBU.sag_ydelse_sup.PrisIAlt, Deliveries.SAS_DUBU.sag_ydelse_sup.Effektuering, Deliveries.SAS_DUBU.sag_ydelse_sup.EffektueringFrekvens 
    FROM Deliveries.SAS_DUBU.sag_hovedydelse
    inner join Deliveries.SAS_DUBU.sag_ydelse_sup
    ON Deliveries.SAS_DUBU.sag_hovedydelse.Cpr = Deliveries.SAS_DUBU.sag_ydelse_sup.Cpr and Deliveries.SAS_DUBU.sag_hovedydelse.Ansvarlig = Deliveries.SAS_DUBU.sag_ydelse_sup.Ansvarlig and Deliveries.SAS_DUBU.sag_hovedydelse.YdelseStartdato = Deliveries.SAS_DUBU.sag_ydelse_sup.YdelseStartdato

Answer 1

You can try using SELECT INTO statement

SELECT Deliveries.SAS_DUBU.sag_hovedydelse.Cpr, Deliveries.SAS_DUBU.sag_hovedydelse.Ansvarlig, Deliveries.SAS_DUBU.sag_hovedydelse.YdelseStartdato, Deliveries.SAS_DUBU.sag_hovedydelse.YdelseSlutdato, Deliveries.SAS_DUBU.sag_hovedydelse.Ydelsestype, Deliveries.SAS_DUBU.sag_hovedydelse.LeveresAf, Deliveries.SAS_DUBU.sag_hovedydelse.Udgiftstekst, Deliveries.SAS_DUBU.sag_hovedydelse.InstantieretPaaBagrundAf, Deliveries.SAS_DUBU.sag_hovedydelse.DinBruger, Deliveries.SAS_DUBU.sag_ydelse_sup.Pris , Deliveries.SAS_DUBU.sag_ydelse_sup.PrisEnhed, Deliveries.SAS_DUBU.sag_ydelse_sup.PrisIAlt, Deliveries.SAS_DUBU.sag_ydelse_sup.Effektuering, Deliveries.SAS_DUBU.sag_ydelse_sup.EffektueringFrekvens 
INTO T1
FROM Deliveries.SAS_DUBU.sag_hovedydelse
INNER JOIN Deliveries.SAS_DUBU.sag_ydelse_sup
ON Deliveries.SAS_DUBU.sag_hovedydelse.Cpr = Deliveries.SAS_DUBU.sag_ydelse_sup.Cpr and Deliveries.SAS_DUBU.sag_hovedydelse.Ansvarlig = Deliveries.SAS_DUBU.sag_ydelse_sup.Ansvarlig and Deliveries.SAS_DUBU.sag_hovedydelse.YdelseStartdato = Deliveries.SAS_DUBU.sag_ydelse_sup.YdelseStartdato

You need to make sure that T1 doesn't exist before you execute this statement.

This is TSQL dialect.