CODEWITHSUNDEEP
sql-server
Oz Cohen
Oz Cohen

Reputation: 963

sql server update table

the table:

CREATE TABLE [dbo].[User] (
    [UserName]  NVARCHAR (100) NOT NULL,
    [Pasword]   NVARCHAR (100) NOT NULL,
    [Name]      TEXT           NOT NULL,
    [LastName]  TEXT           NOT NULL,
    [Location]  TEXT           NOT NULL,
    [profesion] TEXT           NOT NULL,
    [Email]     NVARCHAR (50)  NOT NULL,
    [Gender]    TEXT           NOT NULL,
    PRIMARY KEY CLUSTERED ([UserName] ASC)
);

i want to update to:

CREATE TABLE [dbo].[User] (
    [UserName]  NVARCHAR (100) NOT NULL,
    [Pasword]   NVARCHAR (100) NOT NULL,
    [Name]      TEXT           NOT NULL,
    [LastName]  TEXT           NOT NULL,
    [Location]  TEXT           NOT NULL,
    [profesion] TEXT           NOT NULL,
    [Email]     NVARCHAR (50)  NOT NULL,
    [Gender]    TEXT           NOT NULL,
    [moneyinmillions] INT NOT NULL, 
    PRIMARY KEY CLUSTERED ([UserName] ASC)
);

the problem:

an error occurred while the batch was being executed

thanks for the help

Upvotes: 1

Views: 85

Answers (3)

Raktim Biswas
Raktim Biswas

Reputation: 4077

Ways to Insert a column in your existing Table

  • Use the ALTER TABLE Statement

Do the following:

ALTER TABLE [dbo].[User]
ADD [moneyinmillions] INT NOT NULL

  • Using the Table Designer

    1. In Object Explorer, right-click the table (here, User table) to which you want to add columns and choose Design.
    2. Click in the first blank cell in the moneyinmillions column.
    3. Press the TAB key to go to the Data Type cell and select a Data Type from the dropdown.
    4. When you are finished adding columns, from the File menu, choose Save table name (User).

  • Using DROP TABLE and Re-Creating the Table

    DROP TABLE [dbo].[User]

and then Execute the statements below:

    CREATE TABLE [dbo].[User] (
    [UserName]  NVARCHAR (100) NOT NULL,
    [Pasword]   NVARCHAR (100) NOT NULL,
    [Name]      TEXT           NOT NULL,
    [LastName]  TEXT           NOT NULL,
    [Location]  TEXT           NOT NULL,
    [profesion] TEXT           NOT NULL,
    [Email]     NVARCHAR (50)  NOT NULL,
    [Gender]    TEXT           NOT NULL,
    [moneyinmillions] INT NOT NULL, 
    PRIMARY KEY CLUSTERED ([UserName] ASC));

(Note: The DROP Table Statement will remove the table definition and all the data, indexes, triggers, constraints, and permission specifications for that table. So, if you have data entry in some fields/columns, then do not use the DROP TABLE Statement because you'll loose all the data).

Upvotes: 1

Chathuranga Ranasinghe
Chathuranga Ranasinghe

Reputation: 549

Did you know that you can right click on the table and open the design view to add/remove columns to or from a table ??

Upvotes: 0

Lews Therin
Lews Therin

Reputation: 3777

In the interest of answering your question, here is the code you would want to add the moneyinmillions column to the User table:

ALTER TABLE [User]
ADD [moneyinmillions] INT NOT NULL;

Upvotes: 1

Related Questions