Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert using select Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 6:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
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/
Post #1392454
Posted Tuesday, December 4, 2012 7:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1392467
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse