Alter Table Columns And Datatypes Without Deleting The Table

Introduction

There is no need to delete a whole table in case of further updates in Table column datatypes and any new added columns.

Description

In most cases, real time scenarios are based on the requirement that the new columns should be added or change the data type or data type range values.
 
Steps to follow

Create a table. 
  1. Create Table tblContribution    
  2. (    
  3.  Article nvarchar(50),    
  4.  Author nvarchar(50),    
  5.  Counts int    
  6. )   
Insert some dummy records.
 
 
 
Then execute the below query.
  1. select * from tblContribution  
There are a total of 3 columns. I need an extra  "TodayDate" column with data type "datetime" except SQL query." Now, there is a column with data type "datetime" except SQL query.
 
 
 

We got some error, as shown above during new added columns.
 
In this case, we should write some SQL query. 
  1. alter table tblContribution add TodayDate datetime 
Now, execute the query given below. We will get a new column with the data type.
  1. select * from tblContribution  
 
 
Again, without SQL query, we should change the data type of "TodayDate" column data type "datetime" to "datetimeoffset". 

 
 
We got some error like above during column datatype change and range value.
 
In this case, we should write some SQL query.
  1. ALTER TABLE [tblContribution] ALTER COLUMN [TodayDate] datetimeoffset  
See the table structure, what has happened.
 
 
By executing SQL statement mentioned below, we will get the details of the table updated status.
  1. sp_help 'tblContribution' 
 
Summary
  1. Why we are getting error messages while adding new columns, changing datatype and range value of an existing table.
  2. How to prevent this by writing SQL query.
  3. How to alter table columns and datatypes and range values without deleting the table.
X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now