?
Solved

add an IF EXISTS

Posted on 2008-02-06
19
Medium Priority
?
392 Views
Last Modified: 2010-05-18
nightly job steps:
  1. drop tablename_YYYYMMDD  (yesterday's table)
  2. select * into tableNEW  (current 7 days)
  3. drop indexes
  4. rename tables (current tablename to 'tablename_YYYYMMDD', tableNEW to 'tablename')
  5. recreate the indexes

I had the renames just fine, but I had syntax problems w/the DROP, received this expert advice:

--Rename Table With Current Date
DECLARE @sql varchar(4000),
      @date varchar(8)
 
SELECT @sql = '
EXEC SP_RENAME ''tablename'', ''tablename_@date'''
SELECT @sql = REPLACE(@sql, '@date', CONVERT(VARCHAR,GETDATE(),112))
EXEC(@sql)
 
--Rename New Table
EXEC SP_RENAME 'tableNew', 'tablename'
 
--Drop Yesterdays Table
SELECT @sql = 'DROP TABLE dbo.tablename_' + CONVERT(varchar, DATEADD(d, -1, GETDATE()), 112)
EXEC(@sql)


This is all good, I am just trying to get an IF EXISTS in there, on each of the renames/drops.  I figured this would work on the rename of tableNEW:

--Rename New Table
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[tableNEW]'))
EXEC SP_RENAME 'tableNew', 'tablename'

but, i'm not sure about the DROP of yesterday's table, or the current day table rename.

Any thoughts?
0
Comment
Question by:dbaSQL
  • 12
  • 7
19 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 20832520
Can you not just check the object name

IF EXISTS(SELECT 'x' FROM dbo.sysobjects WHERE Name = 'Employees')

Cheers, Andrew
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20832634
yes, i assume so.  but what of the dynamic sql?  that's my concern, I'm not sure how to do it syntactically
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20832697
this works:

SELECT @sql = 'IF EXISTS(SELECT 1 FROM database..sysobjects WHERE NAME = ''tablename'')
EXEC SP_RENAME ''tablename'', ''tablename_@date'''
SELECT @sql = REPLACE(@sql, '@date', CONVERT(VARCHAR,GETDATE(),112))
EXEC(@sql)

but, i am struggling to do the same here:

--Drop Yesterdays Table
SELECT @sql = 'DROP TABLE dbo.tablename_' + CONVERT(varchar, DATEADD(d, -1, GETDATE()), 112)
EXEC(@sql)
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 28

Expert Comment

by:TextReport
ID: 20832825
Just tested it in NorthWind with your an my setting and my table EMPLOYEES3 was dropped, I had to refresh the tables list though.
Cheers, Andrew


SELECT @sql = 'DROP TABLE dbo.tablename_' + CONVERT(varchar, DATEADD(d, -1, GETDATE()), 112)
print @sql --Returns DROP TABLE dbo.tablename_20080205
SELECT @sql = 'DROP TABLE dbo.' + 'EMPLOYEES3'
print @sql -- Returns DROP TABLE dbo.EMPLOYEES3
EXEC(@sql)

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20832853
Possibly I was unclear.  My DROP works just fine as-is.  I am simply trying to preface it with an IF EXISTS.
0
 
LVL 28

Accepted Solution

by:
TextReport earned 1400 total points
ID: 20832975
Not sure I follow exactly what you are driving at but can you try the code below
Cheers, Andrew
DECLARE @TableName AS VARCHAR(64)
SET @@TableName = 'TableName_' + CONVERT(varchar(8), DATEADD(d, -1, GETDATE()), 112)
SELECT @sql = 'IF EXISTS(SELECT 1 FROM database..sysobjects WHERE NAME = ' + CHAR(39) + @TableName + CHAR(39) + ')
DROP TABLE dbo.' + @TableName
PRINT @SQL
EXEC(@sql)
 
The Print Returns
IF EXISTS(SELECT 1 FROM database..sysobjects WHERE NAME = 'TableName_20080205')
DROP TABLE dbo.TableName_20080205

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20833211
It PRINTs out fine.  But, when i attempt to exec, I get this:

Server: Msg 15335, Level 11, State 1, Procedure sp_rename, Line 342
Error: The @newname value 'tablename' is already in use as a object name and would cause a duplicate that is not permitted.


possibly my ordering of things isn't correct?  the 2nd chunk - rename new table -- is that it?

/* Rename Table With Current Date  */
DECLARE @sql varchar(4000),
      @date varchar(8),
      @tablename varchar(25)
 
SELECT @sql = 'IF EXISTS(SELECT 1 FROM database..sysobjects WHERE NAME = ''tablename'')
EXEC SP_RENAME ''tablename'', ''tablename_@date'''
SELECT @sql = REPLACE(@sql, '@date', CONVERT(VARCHAR,GETDATE(),112))
EXEC(@sql)
--PRINT(@sql)
 
/* Rename New Table */
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[tableNEW]'))
EXEC SP_RENAME 'tableNew', 'tablename'
 
/* Drop Yesterdays Table */
SET @tablename = 'tablename_'+CONVERT(VARCHAR(8),DATEADD(d,-1,GETDATE()),112)
SELECT @sql = 'IF EXISTS(SELECT 1 FROM database..sysobjects WHERE NAME = '+CHAR(39)+@tablename + CHAR(39)+')
DROP TABLE tablename_'+CONVERT(VARCHAR,DATEADD(d,-1,GETDATE()),112)

EXEC(@sql)
--PRINT(@sql)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20833275
And, if i comment out the 2nd chunk -- rename new table -- it exec's without error.  It simply does nothing.  No tables are renamed, dropped, nothing.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20833357
yeah, i can't seem to get around this:

Server: Msg 15335, Level 11, State 1, Procedure sp_rename, Line 342
Error: The @newname value 'tablename' is already in use as a object name and would cause a duplicate that is not permitted.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20833442
I tried to wrap it inside BEGIN/END, it prints just fine.  but it won't execute, same error, msg 15335
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20833605
got it!  my bad.  i hadn't referenced tableNEW properly.  we're good now.  
thank you very much
0
 
LVL 28

Expert Comment

by:TextReport
ID: 20833613
OK tried and tested in SQL Server 2000. I think the issues you are having is getting the ' and two ' in the right place as some of the commands require the ', in this case you need to use '' or CHAR(39) to get a '

Hope this helps, Andrew
use northwind
DECLARE @SQL VARCHAR(8000)
 
DECLARE @TableName AS VARCHAR(64)
SET @TableName = 'Employees3'
SELECT @sql = 'IF NOT EXISTS(SELECT 1 FROM dbo.sysobjects WHERE NAME = ' + CHAR(39) + @TableName + CHAR(39) + ')
BEGIN
    EXEC sp_rename ''employees2'', ' + CHAR(39) + @TableName + CHAR(39) + '
SELECT * FROM ' + @TableName + '
    EXEC sp_rename ' + CHAR(39) + @TableName + CHAR(39) + ', ''employees2''
END'
PRINT @SQL
EXEC(@sql)

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20833639
this works.  what do you think?


/* drop/rename several tables */
DECLARE @sql1 varchar(1000),
        @sql2 varchar(1000),
      @sql3 varchar(1000),
      @date varchar(8),
      @tablename varchar(25)
 
/* Rename table w/current date */
SELECT @sql1 = 'IF EXISTS(SELECT 1 FROM database..sysobjects WHERE NAME = ''tablename'')
EXEC SP_RENAME ''tablename'', ''tablename_@date'' '
SELECT @sql1 = REPLACE(@sql1, '@date', CONVERT(VARCHAR,GETDATE(),112))

/* Rename New Table  */
SELECT @sql2 = '
IF EXISTS(SELECT * FROM database..sysobjects WHERE NAME = ''tableNEW'')
EXEC SP_RENAME ''tableNew'',''tablename'' '

/* Drop Yesterdays Table */
SET @tablename = 'tablename_'+CONVERT(VARCHAR(8),DATEADD(d,-1,GETDATE()),112)
SELECT @sql3 = '
IF EXISTS(SELECT 1 FROM database..sysobjects WHERE NAME = '+CHAR(39)+@tablename + CHAR(39)+')
DROP TABLE tablename_'+CONVERT(VARCHAR,DATEADD(d,-1,GETDATE()),112)
 
PRINT(@sql1 + @sql2 + @sql3)
--EXEC(@sql1 + @sql2 + @sql3)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20833726
yep, this works just fine.  whether the table in question exists or not, it runs as needed.
thank you for you assistance, i do appreciate it.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 20833733
It's all personal preference but I would remove the need for the REPLACE and also I find it much easier to use CHAR(39) to include a ' in my string, especially when working in VBA as you have to deal with both single and double quotes.
Well Done, Andrew
/* drop/rename several tables */
DECLARE @sql1 varchar(1000),
        @sql2 varchar(1000),
      @sql3 varchar(1000),
      @date varchar(8),
      @tablename varchar(25)
 
/* Rename table w/current date */
SELECT @sql1 = 'IF EXISTS(SELECT 1 FROM database..sysobjects WHERE NAME = ''tablename'')
EXEC SP_RENAME ''tablename'', ' + CHAR(39) + CONVERT(VARCHAR,GETDATE(),112) + CHAR(39)
 
/* Rename New Table  */
SELECT @sql2 = '
IF EXISTS(SELECT * FROM database..sysobjects WHERE NAME = ''tableNEW'') 
EXEC SP_RENAME ''tableNew'',''tablename'' '
 
/* Drop Yesterdays Table */
SET @tablename = 'tablename_'+CONVERT(VARCHAR(8),DATEADD(d,-1,GETDATE()),112)
SELECT @sql3 = '
IF EXISTS(SELECT 1 FROM database..sysobjects WHERE NAME = '+CHAR(39)+@tablename + CHAR(39)+') 
DROP TABLE tablename_'+CONVERT(VARCHAR,DATEADD(d,-1,GETDATE()),112)
 
PRINT(@sql1 + @sql2 + @sql3)
--EXEC(@sql1 + @sql2 + @sql3)

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20834115
Andrew?
0
 
LVL 28

Expert Comment

by:TextReport
ID: 20834149
"Andrew?" sorry don't understand. Please elaborate.
Cheers, Andrew
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20834186
nope, nope, you didn't misunderstand, i did.

when you said 'well done, andrew'
i didn't take it as 'well done.  andrew'

i thought that's what you were calling me

my bad.  over thought it.
all good now

thank you for your help, andrew
0
 
LVL 28

Expert Comment

by:TextReport
ID: 20834214
very funny... glad to of helped
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

601 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