CODEWITHSUNDEEP
sqlsql-server
user131983
user131983

Reputation: 3927

Trouble using the Insert Into Syntax in SQL

On this site (http://www.w3schools.com/sql/sql_insert.asp) it says that I can use the SQL Insert statement using the Syntax:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

I tried doing this as follows:

create table Trade
(
    TradeId INT PRIMARY KEY, -- define a column of type int, primary key can't be null
    Symbol varchar(20) NOT NULL, --define a column of type varchar. Not Null indicates that this column must always have a value
    TradeAmount decimal(15, 3), -- total digits are 15, 3 or which are decimal points
    Filled BIT NOT NULL default(0), -- we specify a default of 0 on the column
)
Go


INSERT INTO Trade  
VALUES ('GOOG', 10.235785, 1)

However, I get an error

Column name or number of supplied values does not match table definition

and I am unsure why this is the case.

Thanks

Upvotes: 1

Views: 55

Answers (2)

Leandro Bardelli
Leandro Bardelli

Reputation: 11578

You didn't define the first column as autoincremented, so if you don't send it, table is expecting 4 fields and you are only sending 3.

This should work:

Insert into Trade  VALUES (1, 'GOOG', 10.235785, 1)

Or you can create the table in this way, adding IDENTITY(1,1) (for SQL Server):

http://www.w3schools.com/sql/sql_autoincrement.asp

create table Trade
(
    TradeId INT IDENTITY(1,1) PRIMARY KEY, -- define a column of type int, primary key can't be null
    Symbol varchar(20) NOT NULL, --define a column of type varchar. Not Null indicates that this column must always have a value
    TradeAmount decimal(15, 3), -- total digits are 15, 3 or which are decimal points
    Filled BIT NOT NULL default(0), -- we specify a default of 0 on the column
)

Upvotes: 4

dobriy_santa
dobriy_santa

Reputation: 76

Also, you can describe columns directly before VALUES sequence:

INSERT INTO Trade (Symbol, TradeAmount, Filled) VALUES (
    ('GOOG', 10, 1),
    ('MSFT', 7 , 0)
)

In this case you'll not need to manage the IDENTITY value by yourself, SQL Server will increase it automatically for each new row.

Upvotes: 1

Related Questions