CODEWITHSUNDEEP
sqlsql-servert-sql
Clem_Fandango
Clem_Fandango

Reputation: 364

Delete and Insert Into

I'm looking to execute a stored procedure everyday where it will firstly delete the data within a specified table and then insert the new data in.

My stored procedure looks roughly like this

ALTER PROCEDURE  [dbo].[SP_Name]

 AS BEGIN

 WITH  CTE_Name as 

   (   select
        Title,
        First_Name,
        Surname

         From table

   ) 

   DELETE [dbo].[NEW_TABLE]
   INSERT INTO [dbo].[NEW_TABLE]

   Select * from CTE_NAME

    END

When I execute the query I get the error invalid object name 'CTE_NAME'

I have tried removing the 'DELETE [dbo].[NEW_TABLE]' line and upon doing this the stored procedure does run and does insert the data into the table.

I'm using SQl Management Studio 2012

Upvotes: 0

Views: 172

Answers (2)

SKLTFZ
SKLTFZ

Reputation: 950

I think you need to separate your With by adding a semicolon in front of it

below code should work

DECLARE @TABLE TABLE (
 id VARCHAR(100)
)
DELETE FROM @TABLE
;WITH  CTE_Name AS
(   
    SELECT id FROM OtherTable
)    
INSERT INTO @TABLE 
SELECT id FROM CTE_Name

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37500

You could try this code:

ALTER PROCEDURE [dbo].[SP_Name] AS
BEGIN
    DECLARE @helperTbl TABLE (Title varchar(100), First_Name varchar(100), Surname varchar(100))
    INSERT INTO @helperTbl
    SELECT Title,
           First_Name,
           Surname
    FROM [table]

    DELETE FROM [dbo].[NEW_TABLE]
    INSERT INTO [dbo].[NEW_TABLE]
    SELECT * FROM @helperTbl
END

Upvotes: 0

Related Questions