I have two tables called timeus and usdpay in my SQL Server database. These two tables get updated every week.

I did small transformation and combined these two tables and created a new table called fluslabor.

I created fluslabor using the stored procedure shown here, and it is working:

CREATE PROCEDURE [dbo].[sp_uslabor]
AS
BEGIN
    SET NOCOUNT ON

    IF OBJECT_ID(N'dbo.fluslabor', N'U') IS NOT NULL
        TRUNCATE TABLE [dbo].[fluslabor];

    SELECT ut.employee_code
            ,ut.employee_name
            ,ut.department_desc
            ,ut.location_desc
            ,ut.hour 
            ,ut.projects_code
            ,ut.in_effective_time
            ,ut.out_effective_time
            ,ut.date
            ,ut.id  
            ,p.rate
            ,(p.rate * ut.hour ) as Labour_Cost
    INTO fluslabor
    FROM timeus ut
    LEFT JOIN usdpay p ON (TRIM(ut.id) = TRIM(p.id) AND ut.date = p.date)
    WHERE ut.projects_code NOT LIKE '0%'
END

Today I got new data updated in my two tables timeus and usdpay.

When I execute my stored procedure, SQL Server is throwing this error:

Msg 2714, Level 16, State 6, Procedure SP_uslabor, Line 12 [Batch Start Line 38]
There is already an object named 'fluslabor' in the database.

I need to truncate my table every time and load the new data. I checked the similar post, they said to use drop table option. I don't want to drop the table, just want to truncate and execute the procedure

Can anyone advise what is the issue here please?

CodePudding user response：

The problem here is that the table fluslabor already exists in the database. what you are trying above the insert is checking the object existence and then truncating the same

There are two possible approaches that you can try here.

1. Instead if the TRUNCATE do a DROP TABLE. But This will also remove the existing user permissions on the table if you have provided specific custom access to the table to any of the users

   IF OBJECT_ID(N'dbo.fluslabor', N'U') IS NOT NULL DROP TABLE [dbo].[fluslabor];

2. The safest approach will be change the SELECT .. INTO statement and convert it into INSERT INTO like this

   INSERT INTO fluslabor ( <List your Destination columns> ) SELECT <List your Source columns> FROM <Source Query>

the 2nd approach will have the records loaded along with keeping all the existing permissions

CodePudding user response：

IF EXISTS (SELECT 1 FROM sys.objects WHERE type = 'P' AND name = 'your SP name')
BEGIN
    DROP PROCEDURE "your SP name";
END
GO
CREATE PROCEDURE "your SP name"

AS
BEGIN
.
.
.
.

try this one I guess this will help you.