Creating table with fields from 3 different tables Error

Question

i am trying to create a table that is a combination of 3 other tables (but need to join 4 becuase of relations). I was following the post from here:

Creating tables with fields from 2 different tables

When i run the query without CREATE TABLE Customer_Information AS I dont get any errors and shows me the table. But when i run it with CREATE i get this error:

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

Here is the query:

CREATE TABLE Customer_Information AS (
SELECT DimServere.Servernavn, DimServere.Serverstatus,
       DimKunder.Ministerium, DimKunder.MinisteriumFuldeNavn, DimKunder.RapporteringsKunde, 
       IderaPatchAnalyzer.IP_Adresse, IderaPatchAnalyzer.Release_, IderaPatchAnalyzer.Level_, IderaPatchAnalyzer.Edition_, 
       IderaPatchAnalyzer.Build, IderaPatchAnalyzer.Updates_Available, IderaPatchAnalyzer.Supported_, IderaPatchAnalyzer.Support_Status

FROM IderaPatchAnalyzer 
        JOIN DimServere
            ON IderaPatchAnalyzer.IP_Adresse = DimServere.TcpIpAddress
        JOIN FactSystemServereKunder
            ON DimServere.Servernavn = FactSystemServereKunder.Servernavn
        JOIN DimKunder
            On FactSystemServereKunder.KundeID = DimKunder.KundeID
        WHERE DimServere.Serverstatus != 'Disposed/Retired'
        );

Also in the table IderaPatchAnalyzer, when i do a simple SELECT * FROM IderaPatchAnalyzer i get 190 rows. But when i run the joined table, shown above i get 437 rows. My goal is to attach information to those 190 rows. I dont understand why the table gets larger.

Thanks in advanced

Answer 1

I dont think, you can initialize Table that way. You can use:

Select * Into #(tableName)From X where X = Y

But then you will need to Drop that Table

Drop Table #(tableName)

Also important thing is that, it will say it dont know that table but you can use it without a problem.

Your code will then look like:

SELECT DimServere.Servernavn, DimServere.Serverstatus,
       DimKunder.Ministerium, DimKunder.MinisteriumFuldeNavn, DimKunder.RapporteringsKunde, 
       IderaPatchAnalyzer.IP_Adresse, IderaPatchAnalyzer.Release_, IderaPatchAnalyzer.Level_, IderaPatchAnalyzer.Edition_, 
       IderaPatchAnalyzer.Build, IderaPatchAnalyzer.Updates_Available, IderaPatchAnalyzer.Supported_, IderaPatchAnalyzer.Support_Status
INTO #Customer_Information 
FROM IderaPatchAnalyzer 
        JOIN DimServere
            ON IderaPatchAnalyzer.IP_Adresse = DimServere.TcpIpAddress
        JOIN FactSystemServereKunder
            ON DimServere.Servernavn = FactSystemServereKunder.Servernavn
        JOIN DimKunder
            On FactSystemServereKunder.KundeID = DimKunder.KundeID
        WHERE DimServere.Serverstatus != 'Disposed/Retired'

        --Your Code

        DROP TABLE #Customer_Information

For your next problem. You have multiple references to 1 item. It should be solved when you add more Conditions to JOIN / WHERE