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 database or not which I know we can get from this

IF (EXISTS (SELECT * 
             FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = 'TheSchema' 
             AND  TABLE_NAME = 'x'))

Second I need to update the table name if it is already an existing table

EXEC sp_rename 'x','y'

Third I have to check if it is not exist create it and then load the data from text file into database using bcp for that I know code is like this.

Create table x(id int, number varchar(20))

Declare @cmd varchar(200)
Set @cmd='BCP master.dbo.x IN 'filePath' -S -T f -t, -C -E'
EXEC master..XP_CMDSHELL @cmd

I know each and every thing separately I just need your guidance how can I achieve all this in one single Sql query.

For example:

I don't have any table with name 'x' in database, so my query first check if their is any table with the name x or not if no then it will create one and load data from bcp, if table is already existing then it will update the name of the table and then load the data using bcp.

Once the file loaded and we have updated name of the table query will run again and it check the table name and same process so I guess I cant take any hard coded value in my query.

Updated Code:

Declare @z varchar(100)
Set @z='x' 

IF Object_ID(@z) is null
Print 'Table not EXISTS' 
Else Print 'EXISTS'

 Declare @cmd varchar(200)
Set @cmd='BCP @z IN 'filePath' -S -T f -t, -C -E'
EXEC master..XP_CMDSHELL @cmd

DECALRE @name varchar(200)
set @name= @z+'_'+(convert(varchar(16),GETDATE(),112))
EXEC sp_Rename @z,@name

First 2 time it work fine although I m working only on the existing table. In 3rd time it will check for x table but in 2nd attempt it is already update to x_systemdate now I just need to know how can I change it every time query run.

Any help appreciated !!

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!