Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-09-14
21
Medium Priority
?
441 Views
Last Modified: 2012-09-14
I'm trying to create a stored procedure that deletes 2 tables and then re-creates them but without the data so the rest of the code in the sp can fill the tables.  The code I have (shown in part below) works the first time if I have already deleted the tables.  But when I run it again I get this error...
     Msg 2714, Level 16, State 6, Procedure spSafetyStatsYTDCompare, Line 85
     There is already an object named 'tblSafStats' in the database.

And for tblSafStatsApd there is no error, the new data gets added but it also still has the old stuff.  So it's not deleting the contents.  Please help me fix this?



USE [Safety]
GO
/****** Object:  StoredProcedure [dbo].[spSafetyStatsYTDCompare]    Script Date: 09/14/2012 09:17:17 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblSafStatsApd]') AND type in (N'U'))
DROP TABLE [dbo].[tblSafStatsApd]
GO

USE [Safety]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblSafStatsApd](
      [CurCnt801] [int] NULL,
      [CurCntClient] [int] NULL,
      [CurCntDH] [int] NULL,
      [LstCnt801] [int] NULL,
      [LstCntClient] [int] NULL,
      [LstCntDH] [int] NULL,
      [CurYTD] [int] NULL,
      [LstYtd] [int] NULL
) ON [PRIMARY]

GO

USE [Safety]
GO

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

USE [Safety]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblSafStats](
      [CurCnt801] [int] NULL,
      [CurCntClient] [int] NULL,
      [CurCntDH] [int] NULL,
      [LstCnt801] [int] NULL,
      [LstCntClient] [int] NULL,
      [LstCntDH] [int] NULL,
      [CurYtd] [int] NULL,
      [LstYtd] [int] NULL
) ON [PRIMARY]

GO

ALTER PROCEDURE [dbo].[spSafetyStatsYTDCompare]
etc.
.
.
0
Comment
Question by:BobRosas
  • 10
  • 9
  • 2
21 Comments
 
LVL 12

Accepted Solution

by:
Jared_S earned 600 total points
ID: 38399865
Can you truncate the tables instead of dropping and recreating them?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38399925
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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38399937
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.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:BobRosas
ID: 38399956
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.
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 1400 total points
ID: 38399984
If you need to truncate the tables with every execution of that stored procedure then you need to add the TRUNCATE inside the procedure and not auside it. Also within a stored procedure you cannot use GO. So you should have something like:

ALTER PROCEDURE [dbo].[spSafetyStatsYTDCompare]
-- truncate th etable first and here you can use dbo and is recommended 
TRUNCATE TABLE dbo.tblSafStatsApd 
...
-- insert rows, also you can use dbo here
INSERT INTO dbo.tblSafStatsApd ( CurCnt801 )   (all my tables and fields are underlined.
...

Open in new window


Remember the dbo qualifies a table as being part of a schema/owner but is NOT part fo the name. In normal SQL statements you can use qualification like:

schema.tablename

but when you look for a table in the sysobject table schema and the name and the are 2 different columns.
0
 

Author Comment

by:BobRosas
ID: 38400023
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
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38400052
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.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38400065
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

Open in new window

0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 1400 total points
ID: 38400082
You can do the DECALRE and initializations in one step each, only instead of SET you should use SELECT. SET only does 1 at a time:

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)) 

Open in new window

0
 

Author Comment

by:BobRosas
ID: 38400085
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.
0
 

Author Comment

by:BobRosas
ID: 38400095
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?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38400100
What errors? I didn't see any errors yet.
0
 

Author Comment

by:BobRosas
ID: 38400122
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
0
 

Author Comment

by:BobRosas
ID: 38400157
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
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38400205
Just checked back on this thread. Did this table drop on a previous execution without being recreated?
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 1400 total points
ID: 38400236
Is that error happening when you execute the procedure or when you execute the script with the ALTER... procedure? Make sure you don't have anything in that script except the ALTER... procedure part.

You say here:

-- 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...

but the tblSafStats (in your comment is tblSafStas, missing t but I assume is the same table) already exists because you truncated it at the top of the procedure and that didn't generate error.

Make sure you don't have any CREATE table in your procedure or something like

SELECT * INTO tblSafStats FROM...
0
 

Author Comment

by:BobRosas
ID: 38400545
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;
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 1400 total points
ID: 38400614
SELECT INTO tbl FROM... actualy tries to creates the table on the spot but it already exists. In that case you either check if exists and drop it, as you intended initially or, if you want to keep the table an just truncate it as the code is now then you will have to change the SELECT INTO with:

INSERT INTO tblSafStats (cols here)
SELECT cols here FROM ...

so basically you will use the exact same select from the SELECT INTO tblSafStats statement except for the INTO tblSafStats part, which you will simply remove.
0
 

Author Comment

by:BobRosas
ID: 38400665
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
0
 

Author Comment

by:BobRosas
ID: 38400684
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!
0
 

Author Closing Comment

by:BobRosas
ID: 38400692
Thank you both for all your expert help!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question