How do I insert data into a row in SQL?

Question

I am following a tutorial and learning MVC from a book, where I was told to create a table using this script, which I did. But now I want to add an entire row to my Pet table, but I am unable to do it.

Script used to create all my tables.

CREATE TABLE [dbo].[Setting] (
    [Id] INT NOT NULL IDENTITY(1, 1)
    ,[Key] VARCHAR(50) NOT NULL
    ,[Value] VARCHAR(500) NULL
    ,CONSTRAINT [PK_Setting] PRIMARY KEY ([Id])
    );

CREATE TABLE [dbo].[PetType] (
    [PetTypeID] INT NOT NULL IDENTITY(1, 1)
    ,[PetTypeDescription] VARCHAR(50) NULL
    ,CONSTRAINT [PK_PetType] PRIMARY KEY ([PetTypeID])
    );

CREATE TABLE [dbo].[Status] (
    [StatusID] INT NOT NULL IDENTITY(1, 1)
    ,[Description] VARCHAR(50) NOT NULL
    ,CONSTRAINT [PK_Status] PRIMARY KEY ([StatusID])
    );

CREATE TABLE [dbo].[Pet] (
    [PetID] INT NOT NULL IDENTITY(1, 1)
    ,[PetName] VARCHAR(100) NOT NULL
    ,[PetAgeYears] INT NULL
    ,[PetAgeMonths] INT NULL
    ,[StatusID] INT NOT NULL
    ,[LastSeenOn] DATE NULL
    ,[LastSeenWhere] VARCHAR(500) NULL
    ,[Notes] VARCHAR(1500) NULL
    ,[UserId] INT NOT NULL
    ,CONSTRAINT [PK_Pet] PRIMARY KEY ([PetID])
    ,CONSTRAINT [FK_Pet_Status] FOREIGN KEY ([StatusID]) REFERENCES [Status]([StatusID])
    ,CONSTRAINT [FK_Pet_User] FOREIGN KEY ([UserId]) REFERENCES [UserProfile]([UserId])
    );

CREATE TABLE [dbo].[PetPhoto] (
    [PhotoID] INT NOT NULL IDENTITY(1, 1)
    ,[PetID] INT NOT NULL
    ,[Photo] VARCHAR(500) NOT NULL CONSTRAINT [DF_PhotoFile] DEFAULT '/content/pets/no-image.png'
    ,[Notes] VARCHAR(500) NULL
    ,CONSTRAINT [PK_PetPhoto] PRIMARY KEY ([PhotoID])
    ,CONSTRAINT [FK_PetPhoto_Pet] FOREIGN KEY ([PetID]) REFERENCES [Pet]([PetID])
    );

CREATE TABLE [dbo].[Message] (
    [MessageID] INT NOT NULL
    ,[UserId] INT NOT NULL
    ,[MessageDate] DATETIME NOT NULL
    ,[From] VARCHAR(150) NOT NULL
    ,[Email] VARCHAR(150) NOT NULL
    ,[Subject] VARCHAR(150) NULL
    ,[Message] VARCHAR(1500) NOT NULL
    ,CONSTRAINT [PK_Message] PRIMARY KEY ([MessageID])
    ,CONSTRAINT [FK_Message_User] FOREIGN KEY ([UserId]) REFERENCES [UserProfile]([UserId])
    );

I want to add some random values(for testing) into my Pet table's first row. This is the Pet table's first row as an image for further clarity.

I tried using this script to add values to my table.

INSERT INTO Pet VALUES ('1', 'Fido', '12', '4', '1', '12/07/2004', 'New York', 'nothing', '1')

But I got an error saying

An explicit value for the identity column in table 'Pet' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Now I am fairly new to SQL and I am unable to figure this out. I looked at other SO answers where people said something about SET IDENTITY_INSERT, but this didn't work for me as well. I believe I misunderstood other SO answer since I am fairly new to database languages. So need your help.

Thanks

Answer 1

PetID is defined as IDENTITY so you cannot specify a value to INSERT in that column unless you set "IDENTITY_INSERT" option to ON.

You have two options:

• Dont specify that column/value and let SQL generate it for you.
• Set IDENTITY_INSERT to ON before your INSERT operation.

Answer 2

In SQL Server identity is used for autoincrement. identity(1,1) means the starting value for the column will be 1 and will be incremented by 1. You can change it to desired value for example identity(5,2) starts the value at 5 and increments by 2. You no need to specify an explicit value for setting this column, it will be automatically assigned a unique value.

In mysql you can use AUTO_INCREMENT

Refer w3schools page for details sql autoincrement

Answer 3

Another very cool way to add rows/edit table (including editting deleting rows) is to use Microsoft SQL Management Studio Express. I didn't know about this until I'd been learning SQL for years. Basically expand the tree structure to the left, right-click on a table and choose Edit Table. When you get going with SQL more, you can edit Stored Procedures in here and pretty much anything SQL else you can can think of.

I've blurred out the actual database names but this gives you the gist of it :-