add an IF EXISTS

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?
LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
Can you not just check the object name

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

Cheers, Andrew
0
dbaSQLAuthor Commented:
yes, i assume so.  but what of the dynamic sql?  that's my concern, I'm not sure how to do it syntactically
0
dbaSQLAuthor Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

TextReportCommented:
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
dbaSQLAuthor Commented:
Possibly I was unclear.  My DROP works just fine as-is.  I am simply trying to preface it with an IF EXISTS.
0
TextReportCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbaSQLAuthor Commented:
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
dbaSQLAuthor Commented:
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
dbaSQLAuthor Commented:
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
dbaSQLAuthor Commented:
I tried to wrap it inside BEGIN/END, it prints just fine.  but it won't execute, same error, msg 15335
0
dbaSQLAuthor Commented:
got it!  my bad.  i hadn't referenced tableNEW properly.  we're good now.  
thank you very much
0
TextReportCommented:
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
dbaSQLAuthor Commented:
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
dbaSQLAuthor Commented:
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
TextReportCommented:
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
dbaSQLAuthor Commented:
Andrew?
0
TextReportCommented:
"Andrew?" sorry don't understand. Please elaborate.
Cheers, Andrew
0
dbaSQLAuthor Commented:
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
TextReportCommented:
very funny... glad to of helped
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.