How To Check Table Exist And Then Rename It

This question is similar to many other question but it have many other things to. I Have to create a query in which I have many thing: First check if table is already exist in the

Solution 1:

You have more options, one is to do the whole thing using dynamic queries. You can also take a look at SQLCMD. I will show you a quick mock up of the dynamic SQL solution.

DECLARE @TableSchema sys.sysname = N'dbo';
DECLARE @TableName sys.sysname = N'x';
DECLARE @BackupTable sys.sysname = @TableName + '_' + CONVERT(VARCHAR(32), GETDATE(), 112);


DECLARE @SQL NVARCHAR(MAX) = N'

DECLARE @TableWithSchema NVARCHAR(256) = QUOTENAME(@TableSchema) + ''.'' + QUOTENAME(@TableName);

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = @TableSchema
             AND  TABLE_NAME = @TableName))
BEGIN
  EXEC sp_rename @TableWithSchema, @BackupTable, ''OBJECT''
END

CREATE TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + '(
/* Column definitions here*/
);
';

EXEC sp_executesql
    @stmt = @SQL
  , @params = N'@TableSchema sys.sysname, @TableName sys.sysname, @BackupTable sys.sysname'
  , @TableSchema = @TableSchema
  , @TableName = @TableName
  , @BackupTable = @BackupTable
;

/* Do BCP here */

Please note, that 112 date format (see convert) does not contain time value, therefore you want to change it in order to allow the script to run multiple times a day. You can go for example with select REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(32), GETDATE(), 120), ' ', ''), ':', ''), '-', '') (yyyyMMddHHmmss) instead

As always, be careful and double check your code when you are working with dynamic queries!