December 4, 2012 at 6:55 am
I want to insert data into a temp table from a talbe...
I used this query but getting an error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'ProcedureCode'.
select * into #temp
(
[ProcedureCode] ,[ProcedureName] ,[SelectionTypeID] ,[NoOfVisit]
,[SurfaceCount],[Prosthesis],[RecallPer],[DenthOrhyg] ,[ToothEntry] ,[MaxQty]
,[RVU] ,[DentalCost] ,[SurfaceType] ,[IsGenral] ,[IsSpeciality]
,[IsBillable],[IsActive],[MonID],[MonCount] ,[Optional] ,[AlwaysOpt]
,[CreatedByID]
,[CreatedDate]
,[IsDeleted]
,[MainPrefOrder]
,[SmartSurf1]
,[SmartSurf2]
,[SmartSurf3]
,[SmartSurf4]
,[SmartSurf5]
,[SmartPosterior]
,[SmartBicuspid]
,[SmartAnterior]
,[OldDaCost]
,[NoteTemplate]
,[AddAdditionalPercentage]
,QuadrantGroupId
,[IsDenture]
) from
select P.fProcedure Collate Latin1_General_CI_AI
,P.fDesc Collate Latin1_General_CI_AI
,P.fSelectionType
,P.fVisits
,P.fSurfaces
,P.fProsthesis
,P.fRecallPer
,P.fDenthOrhyg Collate Latin1_General_CI_AI
,P.fToothEntry Collate Latin1_General_CI_AI
,P.fMaxQty
,P.fRVU
,P.fCost
,P.fGroup Collate Latin1_General_CI_AI,0,0
,P.Fbillable
,1
,P.fMonID
,P.fMonCount
,P.fOptional
,P.fAlwaysOpt
,1,GETDATE(),P.fDelete
,P.fMainPrefOrder
,P.fSmartSurf1 Collate Latin1_General_CI_AI,
P.fSmartSurf2 Collate Latin1_General_CI_AI
,P.fSmartSurf3 Collate Latin1_General_CI_AI
,P.fSmartSurf4 Collate Latin1_General_CI_AI
,P.fSmartSurf5 Collate Latin1_General_CI_AI
,P.fSmartPosterior Collate Latin1_General_CI_AI
,P.fSmartBicuspid Collate Latin1_General_CI_AI
,P.fSmartAnterior Collate Latin1_General_CI_AI,
P.fOldDACost,P.fNoteTemplate,0,0,0
from WCDentalSQL_COR..ProcP P
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 4, 2012 at 7:09 am
That's not T-SQL syntax. Not ANSI SQL syntax either. Not sure what it is.
The part that SQL Server is objecting to is the list of columns (I assume they're columns) in parentheses, after the temp table name.
Then it's objecting to the syntax of the From clause.
Then to the unnested-nested From after that.
I think this may be what you need, but I can't be sure without knowing what sort of object WCDentalSQL_COR..ProcP is. By the name, I would expect it to be a stored procedure, and you can't Select From a stored procedure. If it's a table or view, then you can Select From it, and this will at least be sytactically correct:
WITH CTE([ProcedureCode], [ProcedureName], [SelectionTypeID], [NoOfVisit], [SurfaceCount], [Prosthesis], [RecallPer], [DenthOrhyg], [ToothEntry], [MaxQty], [RVU], [DentalCost], [SurfaceType], [IsGenral], [IsSpeciality], [IsBillable], [IsActive], [MonID], [MonCount], [Optional], [AlwaysOpt], [CreatedByID], [CreatedDate], [IsDeleted], [MainPrefOrder], [SmartSurf1], [SmartSurf2], [SmartSurf3], [SmartSurf4], [SmartSurf5], [SmartPosterior], [SmartBicuspid], [SmartAnterior], [OldDaCost], [NoteTemplate], [AddAdditionalPercentage], QuadrantGroupId, [IsDenture])
AS (SELECT P.fProcedure COLLATE Latin1_General_CI_AI,
P.fDesc COLLATE Latin1_General_CI_AI,
P.fSelectionType,
P.fVisits,
P.fSurfaces,
P.fProsthesis,
P.fRecallPer,
P.fDenthOrhyg COLLATE Latin1_General_CI_AI,
P.fToothEntry COLLATE Latin1_General_CI_AI,
P.fMaxQty,
P.fRVU,
P.fCost,
P.fGroup COLLATE Latin1_General_CI_AI,
0,
0,
P.Fbillable,
1,
P.fMonID,
P.fMonCount,
P.fOptional,
P.fAlwaysOpt,
1,
GETDATE(),
P.fDelete,
P.fMainPrefOrder,
P.fSmartSurf1 COLLATE Latin1_General_CI_AI,
P.fSmartSurf2 COLLATE Latin1_General_CI_AI,
P.fSmartSurf3 COLLATE Latin1_General_CI_AI,
P.fSmartSurf4 COLLATE Latin1_General_CI_AI,
P.fSmartSurf5 COLLATE Latin1_General_CI_AI,
P.fSmartPosterior COLLATE Latin1_General_CI_AI,
P.fSmartBicuspid COLLATE Latin1_General_CI_AI,
P.fSmartAnterior COLLATE Latin1_General_CI_AI,
P.fOldDACost,
P.fNoteTemplate,
0,
0,
0
FROM WCDentalSQL_COR..ProcP P)
SELECT *
INTO #temp
FROM CTE;
If ProcP is a stored procedure, this won't work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply