CODEWITHSUNDEEP
sqlsql-server
Konrad Viltersten
Konrad Viltersten

Reputation: 39298

How not to store a value in a column?

I've got me this nice table.

create table Records (
  Id uniqueidentifier primary key,
  Occasion datetime not null,
  Information varchar(999) default ' '
)

When I execute the first line line below, I get an error. When I execute the second, I see NULL in the cell. How should I insert stuff into the table so that the default white space kicks in?

insert into Records values (newid(), convert(varchar(21), getdate(), 121))
insert into Records values (newid(), convert(varchar(21), getdate(), 121), null)

The error message is the defaulty one but I'll put it in here anyway, in case someone asks.

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

Upvotes: 2

Views: 95

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 221370

By using the DEFAULT keyword:

INSERT INTO Records VALUES (newid(), CONVERT(varchar(21), getdate(), 121), DEFAULT)

Or, better, by specifying the exact columns with the INSERT statement

INSERT INTO Records (Id, Occasion) 
VALUES (newid(), CONVERT(varchar(21), getdate(), 121))

Or:

INSERT INTO Records (Id, Occasion, Information) 
VALUES (newid(), CONVERT(varchar(21), getdate(), 121), DEFAULT)

Why is it better?

  1. Because you shouldn't rely on the order of columns in your table.
  2. Because you shouldn't rely on the fact that the table will never have any new or removed columns (which is when your query will break again).

Also interesting: SQL Column definition : default value and not null redundant?

Upvotes: 2

Akshey Bhat
Akshey Bhat

Reputation: 8545

insert into Records (
  Id 
  Occasion 
)
values (newid(), convert(varchar(21), getdate()), 121))

Try this.

when you don't specify column names in insert query, sql-server expects values for all the columns. that is why your first query is throwing error as you have supplied value for two columns only, whereas your second query is running correctly

Upvotes: 1

CuriousBeing
CuriousBeing

Reputation: 1632

Try this:

insert into Records values (newid(), convert(varchar(21), getdate()), 121)

You have to close the ( of convert.

Upvotes: -2

Related Questions