Incorrect syntax near the keyword end

Question

Post reply

Incorrect syntax near the keyword end

jsshivalik

SSChasing Mays

Points: 612
More actions
July 8, 2022 at 4:34 pm
Go to Answer
#4060726

Create PROC [dbo].[SP_Planning]
AS
BEGIN
Truncate TABLE Planning
Select * into
Planning
from (select T0.ID,T1.SID,[dbo].[Planning](T1.Id,T0.SId) as Status
from BDetails T0
cross join SDetails T1)
END

Jeffrey Williams

SSC Guru

Points: 90303
More actions
July 8, 2022 at 9:41 pm
Answer
#4060852
There is no reason for the sub-query. Just use that query in the insert statement:
```
Insert Into Planning
Select T0.ID,T1.SID,[dbo].[Planning](T1.Id,T0.SId) as Status
from BDetails T0
cross join SDetails T1
```
Better yet - include the columns from 'Planning' in the insert statement:
```
Insert Into Planning (list of columns here)
Select ...
  From ...
 Cross Join ...;
```
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

You need to provide an alias for the derived table in the query:

  from (select T0.ID,T1.SID,[dbo].[Planning](T1.Id,T0.SId) as Status
          from BDetails T0
          cross join SDetails T1) AS [a] -- <-- alias the derived table

END

Eddie Wuerch
MCM: SQL

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

Also, since the table you are INSERTing to already exists, you need to INSERT to it, not SELECT ... INTO it:

Truncate TABLE Planning

Insert Into Planning
Select *

from (select T0.ID,T1.SID,[dbo].[Planning](T1.Id,T0.SId) as Status

from BDetails T0

cross join SDetails T1) AS query1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".