Create table from multiple tables

I need to create one table from 3, for now, and I followed the instructions on a couple of websites and in my books but as I thought I had finished, an error appeared at the beginning (. Msg 102, Level 15, State 1, Incorrect syntax near '('. I tried everything I can think of but I don't see the problem. It's a little long...but here's the code: I had to remove PHI so the Where clause has no values as criteria here.

Create Table OPVisits
AS (Select OrganizationMRN, LastNM, FirstNM, VisitID
FROM Patient.PatientInternalOrganizationBASE, Person.PersonBASE, Encounter.VisitBASE
WHERE OrganizationMRN in THERE ARE ABOUT 25 SEPARATE VALUES HERE, ALL IN SINGLE QUOTES...and Encounter.VisitBASE.VisitID is not null);

It would be better that you posted the exact code, otherwise we are just guessing. Substitute "xxx" for anything confidential, but be careful not to alter the syntax at all

Just in case this is the problem your IN clause should look like this

WHERE OrganizationMRN IN ('value1', 'value2', ... 'value25')
	and Encounter.VisitBASE.VisitID is not null);

Thanks Kristen! Tried to email you but it keeps getting rejected... :slight_smile:

Better that you reply in the forum - other folk can join in then, if they have something to add, or see something that I have done wrong / could be done better.

Is this for Azure Data Warehouse? Otherwise I don't believe "CREATE TABLE ... AS" is valid in SQL Server. In CTAS, there are no open and close parens, just the SELECT statement.

Finally, you should do explicit JOINs rather than just list tables in the FROM clause [you may need OUTER joins rather than INNER joins]. You are going to get a potentially very large CROSS JOIN from the query you posted.

Putting it all together, something like this then:

CREATE TABLE OPVisits 
AS 
/* no open paren needed here */
SELECT OrganizationMRN, LastNM, FirstNM, VisitID
FROM Patient.PatientInternalOrganizationBASE POB
INNER JOIN Person.PersonBASE PB ON 
    PB.whatever = POB.whatever /*AND ...*/
INNER JOIN Encounter.VisitBASE VB ON 
    VB.whatever = POB.whatever /*AND ...*/
WHERE POB.OrganizationMRN IN ('value', ...)
AND VB.VisitID IS NOT NULL;

Good point. Same thing in MySQL (and probably the other RDBMs too ...)

Ermm ... also perhaps worth pointing out that if this is MS SQL, rather than Azure, then the alternative SELECT ... INTO ... syntax might help

SELECT OrganizationMRN, LastNM, FirstNM, VisitID
INTO OPVisits 
FROM ...
WHERE ...

Dunno how that compares with CTAS - presumably CTAS can't "steal" PKey, Index and other DDL stuff from the original tables / columns, and more than SELECT ... INTO does?