How to Add Column In existing Table using sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi All

I am trying to add new column to existing table but i am getting error , i have in all possible ways

my code is

SQL

ALTER TABLE TRTemplate
ADD ( CreatedbyId bigint not null,
      ModifiedId bigint not null,
      CreatedDate date not null,
      ModifiedDate date not null);


and tryed 

ALTER TABLE TRTemplate
ADD column  CreatedbyId bigint not null,
ADD column  ModifiedId bigint not null,
ADD column  CreatedDate date not null,
ADD column   ModifiedDate date not null;

how to it please anybody help me.......

Posted 27-Oct-14 21:46pm

murkalkiran

Updated 27-Oct-14 21:57pm

King Fisher

v2

Add a Solution

Comments

Robert Welliever 28-Oct-14 3:56am

I'm pretty sure you can only do one at a time:
ALTER TABLE dbo.TRTemplate ADD column CreatedbyId Bigint not null;
ALTER TABLE dbo.TRTemplate ADD column ModifiedId Bigint not null;
....

CHill60 28-Oct-14 4:00am

You should always let us know what the actual error is

3 solutions

Solution 1

syntax error

SQL

ALTER TABLE TRTemplate
ADD CreatedbyId bigint null,
 ModifiedId bigint null,
 CreatedDate date null,
 ModifiedDate date null;

if you want to add not null to your column you have to truncate the table .

Posted 27-Oct-14 22:02pm

King Fisher

Updated 28-Oct-14 6:33am

v4

Comments

Maciej Los 28-Oct-14 12:27pm

+5!

King Fisher 29-Oct-14 0:16am

Thank you. but I was right,I was Posted Exactly What Richard Deeming Posted Sir.Is anything wrong with That Answer?

Maciej Los 29-Oct-14 2:17am

Sorry, King_Fisher. It's terrible mistake. I was sure i'm adding my solution, but i edited yours. I reject changes made by me.

King Fisher 29-Oct-14 2:33am

its ok sir, i thought i was Wrong :)

Maciej Los 29-Oct-14 5:21am

No, it's my fault. Forgive me, my Friend ;)

Richard Deeming 28-Oct-14 12:47pm

You can add multiple columns with a single statement. See example "E" in the documentation you linked to. :)

Solution 3

The ALTER TABLE syntax for MS SQL Server[^] allows you to add multiple columns with a single statement. You just need to get the syntax right. :)

Your first code block is the closest - you just need to remove the parentheses from around the column list:

SQL

ALTER TABLE TRTemplate
ADD 
    CreatedbyId bigint not null,
    ModifiedId bigint not null,
    CreatedDate date not null,
    ModifiedDate date not null
;

Posted 28-Oct-14 6:49am

Richard Deeming

Comments

Maciej Los 28-Oct-14 12:53pm

5ed!

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)

OriginalGriff · Accepted Answer · 2014-10-27T22:04:00

Solution 2

The SQL syntax for ALTER TABLE (http://www.w3schools.com/sql/sql_alter.asp[^]) doesn't allow for multiple lines. You have to do it line by line:

SQL

ALTER TABLE TRTemplate
ADD column  CreatedbyId bigint not null;
ALTER TABLE TRTemplate
ADD column  ModifiedId bigint not null;
ALTER TABLE TRTemplate
ADD column  CreatedDate date not null;
ALTER TABLE TRTemplate
ADD column  ModifiedDate date not null;

Posted 27-Oct-14 22:04pm

OriginalGriff

Comments

Maciej Los 28-Oct-14 12:28pm

+5

Richard Deeming 28-Oct-14 12:45pm

The MS SQL syntax[^] does allow you to add multiple columns in one statement.