SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert using select


Insert using select

Author
Message
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3061 Visits: 2766
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/
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23335 Visits: 9730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search