I am still finding my feet in SQL Server, and I am trying to do a simple column addition and it is throwing an error. I am trying to add the KinAdr3 column after the KinAdr2 column but it is throwing the following error
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'AFTER'.
Below is the SQL statement I am trying to execute
ALTER TABLE TBL_MEDICAL_Patient
ADD KinAdr3 nVARCHAR(50)
AFTER KinAdr2
Thanks in advance
The reason is simple as the above syntax is valid in MySQL but is not valid in SQL Server. In SQL Server following syntax is valid:
ALTER TABLE tablename ADD columnname INT
However, a user wanted to add the column between two of the columns. SQL Server is relational engine. The order of the column should not matter in any of the T-SQL operations. It does not matter in most of the cases (except when the table is extra large, and it has many NULL columns it impacts the size of the table). In reality whenever user wants to add a column to the table, he/she should just the column and later retrieve the column in a specific order in the table using column names.
It is always a good idea to specify the name of the column in the T-SQL query (using * is indeed a bad idea but that is out of scope of this blog post).
For more details please see the SQL SERVER – How to Add Column at Specific Location in Table
afterinalter table. You can only add it to the end