How to insert a column before existing column in SQL

Question

0.00/5 (No votes)

See more:

I created a table - Programmer

SQL

create TABLE PROGRAMMER(
NAME VARCHAR(16) NOT NULL,
DOB DATE NOT NULL,
DOJ DATE NOT NULL,
SEX VARCHAR(1) NOT NULL,
PROF1 VARCHAR(8) NULL,
PROF2 VARCHAR(8) NULL,
SALARY INT NOT NULL
)

now i want to insert the ID column(ID int not null) before NAME column.how it will be possible.Any idea please let me know.I want to write the query for the same.

Thanking in advance

What I have tried:

i google for the same but there is no idea for inserting the column before existing column.I google its shows inserting the column after existing the column which i dont need.

Posted 1-Apr-16 3:22am

R Harshal

Updated 1-Apr-16 19:08pm

Add a Solution

Comments

CHill60 1-Apr-16 9:37am

Not entirely sure why you would want to do that, the order of columns in the table is meaningless. AFAIK you would have to drop the table and recreate it.

R Harshal 1-Apr-16 9:58am

apart from droping the table ,so you mean to say there is no way to add column before exsisting column.

CHill60 1-Apr-16 10:26am

See Solution 1 - it mentions the only two ways I know of doing it. Neither are particularly nice. See also the comment from RickZeeland - if you have existing import applications that use this table moving the columns around could break them

RickZeeland 1-Apr-16 10:16am

In exceptional cases you can get issues when the column order is not the same. I had a problem with importing CSV data with the BCP (Bulk Copy) utility once.

Karthik_Mahalingam 1-Apr-16 9:45am

you cant do that.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2016-04-01T03:37:00

This is not possible directly, I'm afraid. TransacSQL does not have any instruction regarding the order of columns in tables.

You can do that graphically with SQL Server Management Studio.

If you really want to do it programmatically, you have to create a temporary table which mimics the schema of the original table, adding the new column at the right place in the process, then delete the original, and finally rename the temp table to the name of the original one.

Karthik_Mahalingam · Answer 2 · 2016-04-01T19:08:00

Simple answer is NO , but can be done with some scripts which is not recommendable

alter table - Adding column between two other columns in SQL server [^]
SQL SERVER - How to Add Column at Specific Location in Table [^]
Inserting column between other columns in SQL Server using script - Stack Overflow[^]

How to insert a column before existing column in SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us