Trouble using the Insert Into Syntax in SQL

Question

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

Bad habits to kick: using SELECT * / omit the column list

marc_s
– marc_s

2014-11-10 05:18:39 +00:00
Commented Nov 10, 2014 at 5:18 — marc_s
– marc_s, Commented Nov 10, 2014 at 5:18

marc_s · Accepted Answer · 2024-04-11 16:51:16Z

4

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
)

edited Apr 11, 2024 at 16:51

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 10, 2014 at 1:35

Leandro Bardelli

11.7k16 gold badges88 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dobriy_santa · Accepted Answer · 2014-11-10 01:51:16Z

1

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.

answered Nov 10, 2014 at 1:51

dobriy_santa

763 bronze badges

Collectives™ on Stack Overflow

Trouble using the Insert Into Syntax in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related