-1

I am trying to add a column in between columns and getting the following error:

Msg 259, Level 16, State 1, Line 10: Ad hoc updates to system catalogs are not allowed.

This is what I have done:

select * from 
[INFORMATION_SCHEMA].COLUMNS
where TABLE_NAME = 'Customers' 

Alter table Customers  
Add ContactName varchar(20)

Update INFORMATION_SCHEMA.COLUMNS
set ORDINAL_POSITION = 3
where  TABLE_NAME = 'Customers ' and COLUMN_NAME = 'ContactName'

The column should move to the third position.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Md Meraj Alam
  • 11
  • 1
  • 4
  • https://stackoverflow.com/q/5327545/4524485 – Kiran Desai Apr 18 '19 at 10:10
  • Possible duplicate of [Adding column between two other columns in SQL server](https://stackoverflow.com/questions/5327545/adding-column-between-two-other-columns-in-sql-server) – Shadow Apr 18 '19 at 10:20
  • 1
    Pls pay attention when you tag your question, incorrect tagging (MySQL instead of sql-server) caused two users two post answers that are not useful to you and wasted their time. – Shadow Apr 18 '19 at 10:22
  • You don't directly modify the system tables/views - EVER. – SMor Apr 18 '19 at 12:47
  • 1
    And learn to search the internet for any error messages you encounter. You're not the first to try this hack and the solution is always the same - recreate the table. – SMor Apr 18 '19 at 12:48

2 Answers2

0
Alter table Customers  
Add ContactName varchar(20)  AFTER `columnthatisprevioustoadded`
Whencesoever
  • 2,218
  • 15
  • 26
  • The question was incorrectly labelled as MySQL, it is related to ms sql server (see the error message and the [...]). If it was related to MySQL, then it were a duplicate. – Shadow Apr 18 '19 at 10:19
0

Alter command by default create column at the end of the table 

 Alter table Customers  
Add ContactName varchar(20)

You need to use AFTER which place a column in a certain position

Alter table Customers  
Add ContactName varchar(20) after column_name_exist_in_position2

column_name_exist_in_position2 = your table 2nd position column name

Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • @MdMerajAlam here is the online demo https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=74b125221cfd674943301b5cdb64e735 check it – Zaynul Abadin Tuhin Apr 18 '19 at 09:37
  • The question was incorrectly labelled as MySQL, it is related to ms sql server (see the error message and the [...]). If it was related to MySQL, then it were a duplicate. – Shadow Apr 18 '19 at 10:20