Drop 2 SQL tables and re-create (but without the data)

The problem is that you use the [dbo]. part in the table check before dropping.

You should use

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tblSafStatsApd]') AND type in (N'U'))
DROP TABLE [dbo].[tblSafStatsApd]
GO

Select allOpen in new window

The schema, dbo in this case, is not part of the table name so you are not supposed to provide it in the check. In the drop statement however the schema doesn't heart because if not provided the default schema will be assumed anyway, which is dbo.

Thank you both!  I've never used "Truncate" before so I had to look it up to see what it does.  I think that it would work better for what I need.  It's faster and I don't have to re-create the table, just clear it.  So I changed my code (see below)  but there is now an issue with all my table names.  I tried it with and without dbo but there is still an issue.

USE [Safety]
GO
/****** Object:  StoredProcedure [dbo].[spSafetyStatsYTDCompare]    Script Date: 09/14/2012 09:17:17 ******/

TRUNCATE TABLE [dbo].[tblSafStatsApd]
TRUNCATE TABLE [dbo].[tblSafStats]
GO

USE [Safety]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [Safety]
GO


ALTER PROCEDURE [dbo].[spSafetyStatsYTDCompare]
...
INSERT INTO tblSafStatsApd ( CurCnt801 )   (all my tables and fields are underlined.

Thank you again!  I have not created very many store procedures and never anything like this.  I'm sure there is a lot more efficient way to do this.  I noticed I have a lot of USE & GO.  I don't know if I need them all.  But I've made another attempt and all my table names are still underlined.  If I try to readd them they are not listed in the dropdown list even tho I see they are still in the db.  I could really use more help with this.

USE [Safety]
GO
/****** Object:  StoredProcedure [dbo].[spSafetyStatsYTDCompare]    Script Date: 09/14/2012 09:17:17 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [Safety]
GO

ALTER PROCEDURE [dbo].[spSafetyStatsYTDCompare]

(
@Month int,
@Year int
)
AS

DECLARE @FromDate datetime = CAST(CAST(@Month as varchar(2)) + '/1/' + CAST(@Year  as varchar(4)) as date)
DECLARE @ToDate datetime = DATEADD(d, -1, CAST((CAST(@Month + 1  as varchar(2))+ '/1/' + CAST(@Year  as varchar(4))) as date))
DECLARE @Yr int = @Year - 1
DECLARE @LstYrFrmDate datetime = CAST(CAST(@Month as varchar(2)) + '/1/' + CAST(@Yr  as varchar(4)) as date)
DECLARE @LstYrToDate datetime = DATEADD(d, -1, CAST((CAST(@Month + 1  as varchar(2))+ '/1/' + CAST(@Yr  as varchar(4))) as date))

TRUNCATE TABLE [dbo].[tblSafStatsApd]
TRUNCATE TABLE [dbo].[tblSafStats]

-- Append 801 count to tblSafStatsApd
INSERT INTO tblSafStatsApd ( CurCnt801 )      --table is underlined here.
SELECT Count(tblIncidents.InjuryId) AS CurCnt801
FROM tblIncidents
WHERE tblIncidents.Date Between (@FromDate) and (@ToDate)
GROUP BY tblIncidents.[801]
HAVING (((tblIncidents.[801])= 1));

etc

Don't pay attention to the code above ALTER, that is automatically added when you script out the procedure code. What matters here is within the procedure code and that follows after CREATE/ALTER command.

I think you got it now, the 2 TRUNCATE will empty your tables and later in the procedure you should be able to repopulate them.

You have some errors though. You cannot declare and initialize variable with values at the same time. You declare first and then you set them:

ALTER PROCEDURE [dbo].[spSafetyStatsYTDCompare]

(
@Month int,
@Year int
)
AS

DECLARE @FromDate datetime 
SET @FromDate = CAST(CAST(@Month as varchar(2)) + '/1/' + CAST(@Year  as varchar(4)) as date)

DECLARE @ToDate datetime 
SET @ToDate = DATEADD(d, -1, CAST((CAST(@Month + 1  as varchar(2))+ '/1/' + CAST(@Year  as varchar(4))) as date)) 

DECLARE @Yr int 
SET @Yr = @Year - 1

DECLARE @LstYrFrmDate datetime 
SET @LstYrFrmDate = CAST(CAST(@Month as varchar(2)) + '/1/' + CAST(@Yr  as varchar(4)) as date) 

DECLARE @LstYrToDate datetime 
SET @LstYrToDate = DATEADD(d, -1, CAST((CAST(@Month + 1  as varchar(2))+ '/1/' + CAST(@Yr  as varchar(4))) as date)) 



TRUNCATE TABLE [dbo].[tblSafStatsApd]
TRUNCATE TABLE [dbo].[tblSafStats]

-- Append 801 count to tblSafStatsApd
INSERT INTO tblSafStatsApd ( CurCnt801 )      --table is underlined here.
SELECT Count(tblIncidents.InjuryId) AS CurCnt801
FROM tblIncidents
WHERE tblIncidents.Date Between (@FromDate) and (@ToDate)
GROUP BY tblIncidents.[801]
HAVING (((tblIncidents.[801])= 1)); 

etc

Select allOpen in new window

Thank you so much!  But I'm still confused on what I need to do.  The code was working before I added the truncates.  And even now I do not have any errors with my declared values.  The only errors showing are my table names.  So I'm not sure what to do/change.

I got your code about the declares and added that - Thank you.  But the errors left didn't change.  Still has a problem with my table names, any thoughts?

What errors? I didn't see any errors yet.

I'm still getting this error...
     Msg 2714, Level 16, State 6, Procedure spSafetyStatsYTDCompare, Line 96
There is already an object named 'tblSafStats' in the database.

And the table name on all my "INSERT INTO" lines of code are underlined.

Here are my changes...
DECLARE
      @FromDate datetime,
      @ToDate datetime,
      @Yr int,
      @LstYrFrmDate datetime,
      @LstYrToDate datetime

SELECT
      @FromDate = CAST(CAST(@Month as varchar(2)) + '/1/' + CAST(@Year  as varchar(4)) as date),
      @ToDate = DATEADD(d, -1, CAST((CAST(@Month + 1  as varchar(2))+ '/1/' + CAST(@Year  as varchar(4))) as date)),
      @Yr = @Year - 1,
      @LstYrFrmDate = CAST(CAST(@Month as varchar(2)) + '/1/' + CAST(@Yr  as varchar(4)) as date),
      @LstYrToDate = DATEADD(d, -1, CAST((CAST(@Month + 1  as varchar(2))+ '/1/' + CAST(@Yr  as varchar(4))) as date))  
      
TRUNCATE TABLE [dbo].[tblSafStatsApd]
TRUNCATE TABLE [dbo].[tblSafStats]
--This code gathers data needed for Satety Statistics report...
--SELECT        InjuryId, LastName, FirstName, Incident, Location, Date, Dept, Status, LightDuty, Preventable, [801], Category, Injury, [Desc]
--FROM            tblIncidents
--WHERE        (DATENAME(Month, Date) = @Month) AND (DATEPART(yy, Date) = @Year)
--ORDER BY Date

-- The rest of the code appends data to tblSafStatsApd and then creates tblSafStas
--which is stats that goes on the bottom of the above report...

-- Append 801 count to tblSafStatsApd
INSERT INTO tblSafStatsApd ( CurCnt801 )           TABLE NAME UNDERLINED HERE
SELECT Count(tblIncidents.InjuryId) AS CurCnt801
FROM tblIncidents
WHERE tblIncidents.Date Between (@FromDate) and (@ToDate)
GROUP BY tblIncidents.[801]
HAVING (((tblIncidents.[801])= 1));

etc

You've been so prompt with your responses so I thought I'd let you know  I have to head off to a meeting.  I've attached a picture in hopes that will help.  
Thanks!
sp.jpg

Just checked back on this thread. Did this table drop on a previous execution without being recreated?

I'm sorry for the bad code.  I mentioned I'm not good at this right...: )

I do have "SELECT into" code.  I've pasted my entire sp below so you can see it.  At the very bottom I take all the data I appended to tblSafStatsApd and insert it into tblSafStats.  If I can't do a "SELECT INTO", How should I be doing this?  Thanks!

USE [Safety]
GO
/****** Object:  StoredProcedure [dbo].[spSafetyStatsYTDCompare]    Script Date: 09/14/2012 09:17:17 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [Safety]
GO

ALTER PROCEDURE [dbo].[spSafetyStatsYTDCompare]

(
@Month int,
@Year int
)
AS

DECLARE
      @FromDate datetime,
      @ToDate datetime,
      @Yr int,
      @LstYrFrmDate datetime,
      @LstYrToDate datetime

SELECT
      @FromDate = CAST(CAST(@Month as varchar(2)) + '/1/' + CAST(@Year  as varchar(4)) as date),
      @ToDate = DATEADD(d, -1, CAST((CAST(@Month + 1  as varchar(2))+ '/1/' + CAST(@Year  as varchar(4))) as date)),
      @Yr = @Year - 1,
      @LstYrFrmDate = CAST(CAST(@Month as varchar(2)) + '/1/' + CAST(@Yr  as varchar(4)) as date),
      @LstYrToDate = DATEADD(d, -1, CAST((CAST(@Month + 1  as varchar(2))+ '/1/' + CAST(@Yr  as varchar(4))) as date))  
      
TRUNCATE TABLE [dbo].[tblSafStatsApd]
TRUNCATE TABLE [dbo].[tblSafStats]

--This code gathers data needed for Satety Statistics report...
SELECT        InjuryId, LastName, FirstName, Incident, Location, Date, Dept, Status, LightDuty, Preventable, [801], Category, Injury, [Desc]
FROM            tblIncidents
WHERE        (DATENAME(Month, Date) = @Month) AND (DATEPART(yy, Date) = @Year)
ORDER BY Date

-- The rest of the code appends data to tblSafStatsApd and then creates tblSafStas
--which is stats that goes on the bottom of the above report...

-- Append 801 count to tblSafStatsApd
INSERT INTO tblSafStatsApd ( CurCnt801 )
SELECT Count(tblIncidents.InjuryId) AS CurCnt801
FROM tblIncidents
WHERE tblIncidents.Date Between (@FromDate) and (@ToDate)
GROUP BY tblIncidents.[801]
HAVING (((tblIncidents.[801])= 1));

--Append 'Client' count to tblSafStatsApd
INSERT INTO tblSafStatsApd ( CurCntClient )
SELECT Count(tblIncidents.InjuryId) AS CurCntClient
FROM tblIncidents
WHERE tblIncidents.Date Between (@FromDate) and (@ToDate)
GROUP BY tblIncidents.Status
HAVING (((tblIncidents.Status)= 'Client'));

--Append 'DH' count to tblSafStatsApd
INSERT INTO tblSafStatsApd ( CurCntDH)
SELECT Count(tblIncidents.InjuryId) AS CurCntDH
FROM tblIncidents
WHERE tblIncidents.Date Between (@FromDate) and (@ToDate)
GROUP BY tblIncidents.Status
HAVING (((tblIncidents.Status)= 'DH'));

--Append 'Current YTD' count to tblSafStatsApd
INSERT INTO tblSafStatsApd ( CurYTD)
SELECT Count(tblIncidents.InjuryId) AS CurYTD
FROM tblIncidents
WHERE tblIncidents.Date Between (@FromDate) and (@ToDate) AND (tblIncidents.Status = 'Client' or tblIncidents.Status = 'DH')

-- Append Last years '801' count to tblSafStatsApd
INSERT INTO tblSafStatsApd ( LstCnt801 )
SELECT Count(tblIncidents.InjuryId) AS LstCnt801
FROM tblIncidents
WHERE tblIncidents.Date Between (@LstYrFrmDate) and (@LstYrToDate)
GROUP BY tblIncidents.[801]
HAVING (((tblIncidents.[801])= 1));

--Append Last years 'Client' count to tblSafStatsApd
INSERT INTO tblSafStatsApd ( LstCntClient )
SELECT Count(tblIncidents.InjuryId) AS LstCntClient
FROM tblIncidents
WHERE tblIncidents.Date Between (@LstYrFrmDate) and (@LstYrToDate)
GROUP BY tblIncidents.Status
HAVING (((tblIncidents.Status)= 'Client'));

--Append Last Years 'DH' count to tblSafStatsApd
INSERT INTO tblSafStatsApd ( LstCntDH)
SELECT Count(tblIncidents.InjuryId) AS LstCntDH
FROM tblIncidents
WHERE tblIncidents.Date Between (@LstYrFrmDate) and (@LstYrToDate)
GROUP BY tblIncidents.Status
HAVING (((tblIncidents.Status)= 'DH'));

--Append 'Last Years YTD' count to tblSafStatsApd
INSERT INTO tblSafStatsApd (LstYtd)
SELECT Count(tblIncidents.InjuryId) AS LstYTD
FROM tblIncidents
WHERE tblIncidents.Date Between (@LstYrFrmDate) and (@LstYrToDate) AND (tblIncidents.Status = 'Client' or tblIncidents.Status = 'DH')

--Sum all fields created from the above code and insert into tblSafStats
SELECT Sum(tblSafStatsApd.CurCnt801) AS CurCnt801,
Sum(tblSafStatsApd.CurCntClient) AS CurCntClient,
Sum(tblSafStatsApd.CurCntDH) AS CurCntDH,
Sum(tblSafStatsApd.LstCnt801) AS LstCnt801,
Sum(tblSafStatsApd.LstCntClient) AS LstCntClient,
Sum(tblSafStatsApd.LstCntDH) AS LstCntDH,
Sum(tblSafStatsApd.CurYtd) AS CurYtd,
Sum(tblSafStatsApd.LstYtd) AS LstYtd INTO tblSafStats
FROM tblSafStatsApd;

Great, Thanks, so shouldn't the code below (and attached) work?  It's still underlining all my tableStafStatsApd names...
--Sum all fields created from the above code and insert into tblSafStats
INSERT INTO tblSafStats (CurCnt801, CurCntClient, CurCntDH, LstCnt801, LstCntClient, LstCntDH, CurYtd, LstYtd)
SELECT Sum(tblSafStatsApd.CurCnt801) AS CurCnt801,
Sum(tblSafStatsApd.CurCntClient) AS CurCntClient,
Sum(tblSafStatsApd.CurCntDH) AS CurCntDH,
Sum(tblSafStatsApd.LstCnt801) AS LstCnt801,
Sum(tblSafStatsApd.LstCntClient) AS LstCntClient,
Sum(tblSafStatsApd.LstCntDH) AS LstCntDH,
Sum(tblSafStatsApd.CurYtd) AS CurYtd,
Sum(tblSafStatsApd.LstYtd) AS LstYtd
FROM tblSafStatsApd;
sp.jpg

I don't know how I'm supposed to know when the code is correct if it's gonig to underline everything but when I run the sp it appears to be working...at least most of it.   It's clearing the tables and then appending what I expect.

You've really gone above and beyond so I will close this and repost a related question as needed.  I will max out points and also award to Jared since I used his Truncate idea.

Thank you both for putting up with such a novice.  You were both very helpful!

Thank you both for all your expert help!