title: How to check table exist and then rename it
body: <p>This question is similar to many other question but it have many other things to.</p>
  
  <p>I Have to create a query in which I have many thing:</p>
  
  <blockquote>
    <p>First check if table is already exist in the database or not which I know we can get from this</p>
  </blockquote>
  
  <pre><code>IF (EXISTS (SELECT * 
               FROM INFORMATION_SCHEMA.TABLES 
               WHERE TABLE_SCHEMA = 'TheSchema' 
               AND  TABLE_NAME = 'x'))
  </code></pre>
  
  <blockquote>
    <p>Second I need to update the table name if it is already an existing table </p>
  </blockquote>
  
  <pre><code>EXEC sp_rename 'x','y'
  </code></pre>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <pre><code>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
  </code></pre>
  
  <p>I know each and every thing separately I just need your guidance how can I achieve all this in one single Sql query.</p>
  
  <p>For example:</p>
  
  <blockquote>
    <p>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.</p>
    
    <p>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.</p>
  </blockquote>
  
  <p>Updated Code:</p>
  
  <pre><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
  </code></pre>
  
  <p>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.</p>
  
  <p>Any help appreciated !!</p>