Help Creating a Table

[gjmac2]

I am trying to create a table from a stored procedure, and am getting the following error code:

"Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'."

Here is the code I am trying to run:

CREATE table donors
AS SELECT dbo.VW_Case.ReferralDate, dbo.VW_Case.AdmitDate, dbo.VW_Case.StatusDate, dbo.VW_0OPTN.OPTNnumber, dbo.VW_Case.DonorTracCaseID,
dbo.VW_Case.StatTracId, dbo.VW_Case.ReferralFacilityName, dbo.VW_Triage.gender, dbo.VW_Triage.race, dbo.VW_Triage.causeofdeath,
dbo.VW_Triage.vent, dbo.VW_Triage.heartbeat, dbo.VW_Triage.mechofdeath, dbo.VW_Triage.circumofdeath,
dbo.VW_Triage.braindeathpronouncedvalues, dbo.VW_Triage.Age, dbo.VW_Triage.Height, dbo.VW_Triage.Weight, dbo.VW_Triage.Bmi,
dbo.rptDonorType(dbo.VW_Case.ID) AS DonorType,
MAX(CASE WHEN dbo.VW_IntraOpHeart.OutcomeDisposition LIKE '%50%' THEN 1 WHEN dbo.VW_IntraOpHeart.OutcomeDisposition LIKE '%51%' THEN
1 ELSE NULL END) AS [Heart Recovered], dbo.VW_IntraOpHeart.OutcomeOrganDisposition AS [Heart UNOS Code],
MAX(CASE WHEN dbo.VW_IntraOpLiver.TopOrganDispositionValue LIKE '%50%' THEN 1 WHEN dbo.VW_IntraOpLiver.TopOrganDispositionValue LIKE '%51%'
THEN 1 ELSE NULL END) AS [Top Liver Recovered], dbo.VW_IntraOpLiver.TopOrganDispositionValue AS [Top Liver UNOS Code],
MAX(CASE WHEN dbo.VW_IntraOpLiver.BottomOrganDispositionValue LIKE '%50%' THEN 1 WHEN dbo.VW_IntraOpLiver.BottomOrganDispositionValue
LIKE '%51%' THEN 1 ELSE NULL END) AS [Bottom Liver Recovered],
dbo.VW_IntraOpLiver.BottomOrganDispositionValue AS [Bottom Liver UNOS Code],
MAX(CASE WHEN dbo.VW_IntraOpPancreas.Organdispositionvalue LIKE '%50%' THEN 1 WHEN dbo.VW_IntraOpPancreas.Organdispositionvalue LIKE '%51%'
THEN 1 ELSE NULL END) AS [Pancreas Recovered], dbo.VW_IntraOpPancreas.organdispositionvalue AS [Pancreas UNOS Code],
MAX(CASE WHEN dbo.VW_IntraOpSmallIntestine.OrganOutcomeDispostion LIKE '%50%' THEN 1 WHEN dbo.VW_IntraOpSmallIntestine.OrganOutcomeDispostion
LIKE '%51%' THEN 1 ELSE NULL END) AS [Small Intestine Recovered],
dbo.VW_IntraOpSmallIntestine.OrganOutcomeDispostion AS [Small Intestine UNOS Code],
MAX(CASE WHEN dbo.VW_IntraOpRenal.TopOrganDispositionvalue LIKE '%50%' THEN 1 WHEN dbo.VW_IntraOpRenal.TopOrganDispositionvalue LIKE
'%51%' THEN 1 ELSE NULL END) AS [Right Kidney Recovered], dbo.VW_IntraOpRenal.TopOrganDispositionValue AS [Right Kidney UNOS Code],
MAX(CASE WHEN dbo.VW_IntraOpRenal.BottomOrganDispositionvalue LIKE '%50%' THEN 1 WHEN dbo.VW_IntraOpRenal.BottomOrganDispositionvalue
LIKE '%51%' THEN 1 ELSE NULL END) AS [Left Kidney Recovered], dbo.VW_IntraOpRenal.BottomOrganDispositionValue AS [Left Kidney UNOS Code],
MAX(CASE WHEN dbo.VW_IntraOpLung.TopOrganDispositionvalue LIKE '%50%' THEN 1 WHEN dbo.VW_IntraOpLung.TopOrganDispositionvalue LIKE '%51%'
THEN 1 ELSE NULL END) AS [Right Lung Recovered], dbo.VW_IntraOpLung.TopOrganDispositionValue AS [Right Lung UNOS Code],
MAX(CASE WHEN dbo.VW_IntraOpLung.BottomOrganDispositionvalue LIKE '%50%' THEN 1 WHEN dbo.VW_IntraOpLung.BottomOrganDispositionvalue LIKE
'%51%' THEN 1 ELSE NULL END) AS [Left Lung Recovered], dbo.VW_IntraOpLung.BottomOrganDispositionValue AS [Left Lung UNOS Code],
MAX(CASE WHEN dbo.VW_0LGTISSUECATEGORY.organtissue = 'Skin' AND dbo.VW_0LGTISSUECATEGORY.[Tissue Specific Deviation] IS NULL
THEN 1 ELSE NULL END) AS Skin, MAX(CASE WHEN dbo.VW_0LGTISSUECATEGORY.organtissue = 'Pericardium' AND
dbo.VW_0LGTISSUECATEGORY.[Tissue Specific Deviation] IS NULL THEN 1 ELSE NULL END) AS Pericardium,
MAX(CASE WHEN dbo.VW_0LGTISSUECATEGORY.organtissue = 'Saphenous Vein' AND
dbo.VW_0LGTISSUECATEGORY.[Tissue Specific Deviation] IS NULL THEN 1 ELSE NULL END) AS [Saphenous Vein''],
MAX(CASE WHEN dbo.VW_0LGTISSUECATEGORY.organtissue = 'Femoral Vein' AND
dbo.VW_0LGTISSUECATEGORY.[Tissue Specific Deviation] IS NULL THEN 1 ELSE NULL END) AS [Femoral Vein],
MAX(CASE WHEN dbo.VW_0LGTISSUECATEGORY.organtissue = 'Heart for Valves' AND
dbo.VW_0LGTISSUECATEGORY.[Tissue Specific Deviation] IS NULL THEN 1 ELSE NULL END) AS [Heart for Valves],
MAX(CASE WHEN dbo.VW_0LGTISSUECATEGORY.organtissue = 'Whole Globe' AND
dbo.VW_0LGTISSUECATEGORY.[Tissue Specific Deviation] IS NULL THEN 1 WHEN dbo.VW_0LGTISSUECATEGORY.organtissue = 'cornea' AND
dbo.VW_0LGTISSUECATEGORY.[Tissue Specific Deviation] IS NULL THEN 1 ELSE NULL END) AS Eyes,
MAX(CASE WHEN dbo.VW_0LGTISSUECATEGORY.organtissue = 'Anterior Tibialis Tendon' AND
dbo.VW_0LGTISSUECATEGORY.[Tissue Specific Deviation] IS NULL
THEN 1 WHEN dbo.VW_0LGTISSUECATEGORY.organtissue = 'Posterior Tibialis Tendon' AND
dbo.VW_0LGTISSUECATEGORY.[Tissue Specific Deviation] IS NULL THEN 1 ELSE NULL END) AS Tibialis,
MAX(CASE WHEN dbo.VW_0LGTISSUECATEGORY.organtissue <> 'Skin' AND dbo.VW_0LGTISSUECATEGORY.organtissue <> 'Paricardium' AND
dbo.VW_0LGTISSUECATEGORY.organtissue <> 'Saphenous Vein' AND dbo.VW_0LGTISSUECATEGORY.organtissue <> 'Femoral Vein' AND
dbo.VW_0LGTISSUECATEGORY.organtissue <> 'Heart for Valves' AND dbo.VW_0LGTISSUECATEGORY.organtissue <> 'Whole Globe' AND
dbo.VW_0LGTISSUECATEGORY.organtissue <> 'cornea' AND dbo.VW_0LGTISSUECATEGORY.organtissue <> 'Anterior tibialis tendon' AND
dbo.VW_0LGTISSUECATEGORY.organtissue <> 'posterior tibialis tendon' AND dbo.VW_0LGTISSUECATEGORY.[Tissue Specific Deviation] IS NULL
THEN 1 ELSE NULL END) AS Bone
FROM dbo.VW_0OPTN INNER JOIN
dbo.VW_Case ON dbo.VW_0OPTN.CaseID = dbo.VW_Case.ID RIGHT OUTER JOIN
dbo.VW_Triage ON dbo.VW_Case.DonorTracCaseID = dbo.VW_Triage.DonorTracCaseID LEFT OUTER JOIN
dbo.VW_0LGTISSUECATEGORY ON dbo.VW_Case.DonorTracCaseID = dbo.VW_0LGTISSUECATEGORY.DonorTracCaseID LEFT OUTER JOIN
dbo.VW_IntraOpPancreas ON dbo.VW_Case.ID = dbo.VW_IntraOpPancreas.CaseId LEFT OUTER JOIN
dbo.VW_IntraOpLiver ON dbo.VW_Case.ID = dbo.VW_IntraOpLiver.CaseId LEFT OUTER JOIN
dbo.VW_IntraOpHeart ON dbo.VW_Case.ID = dbo.VW_IntraOpHeart.CaseId LEFT OUTER JOIN
dbo.VW_IntraOpLung ON dbo.VW_Case.ID = dbo.VW_IntraOpLung.CaseId LEFT OUTER JOIN
dbo.VW_IntraOpRenal ON dbo.VW_Case.ID = dbo.VW_IntraOpRenal.CaseID LEFT OUTER JOIN
dbo.VW_IntraOpSmallIntestine ON dbo.VW_Case.DonorTracCaseID = dbo.VW_IntraOpSmallIntestine.CaseId
GROUP BY dbo.VW_Case.ReferralDate, dbo.VW_Case.AdmitDate, dbo.VW_Case.StatusDate, dbo.rptDonorType(dbo.VW_Case.ID), dbo.VW_0OPTN.OPTNnumber,
dbo.VW_Case.DonorTracCaseID, dbo.VW_Case.StatTracId, dbo.VW_Case.ReferralFacilityName, dbo.VW_Triage.gender, dbo.VW_Triage.race,
dbo.VW_Triage.causeofdeath, dbo.VW_Triage.vent, dbo.VW_Triage.heartbeat, dbo.VW_Triage.mechofdeath, dbo.VW_Triage.circumofdeath,
dbo.VW_Triage.braindeathpronouncedvalues, dbo.VW_Triage.Age, dbo.VW_Triage.Height, dbo.VW_Triage.Weight, dbo.VW_Triage.Bmi,
dbo.VW_IntraOpHeart.OutcomeOrganDisposition, dbo.VW_IntraOpLiver.TopOrganDispositionValue,
dbo.VW_IntraOpLiver.BottomOrganDispositionValue, dbo.VW_IntraOpPancreas.organdispositionvalue,
dbo.VW_IntraOpRenal.TopOrganDispositionValue, dbo.VW_IntraOpRenal.BottomOrganDispositionValue,
dbo.VW_IntraOpLung.TopOrganDispositionValue, dbo.VW_IntraOpLung.BottomOrganDispositionValue,
dbo.VW_IntraOpSmallIntestine.OrganOutcomeDispostion
HAVING (dbo.VW_Case.StatTracId IS NOT NULL)

Any help would be greatly appreciated.

 

[SQLDenis]

mmmmmm, yeah......maybe you are trying to create a view?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

Looks like you are trying to create a view (not a table). Try...

[tt]CREATE [!]View[/!] donors[/tt]

 

[gjmac2]

My apologies, but I thought I could use the same command from a view, and save the results to a new table. I should explain that I am a newbie to the whole SQL world.

 

[gmmastros]

If you want to create a new table based on the results of a query, you should use the Select Into syntax.

[tt][blue]
Select columns
[red]Into NewTableNameHere[/red]
From table1 Join Table2 on blah=blah
[/blue][/tt]

 

[gjmac2]

Thank you very much, gmmastros. It worked great.