How to insert default values in SQL table?

tsql insert default
Zbigniew Wazeliniak picture Zbigniew Wazeliniak · Jan 8, 2012 · Viewed 165.9k times · Source

I have a table like this:

create table1 (field1 int,
               field2 int default 5557,
               field3 int default 1337, 
               field4 int default 1337)

I want to insert a row which has the default values for field2 and field4.

I've tried insert into table1 values (5,null,10,null) but it doesn't work and ISNULL(field2,default) doesn't work either.

How can I tell the database to use the default value for the column when I insert a row?

Answer

gbn picture gbn · Jan 8, 2012

Best practice it to list your columns so you're independent of table changes (new column or column order etc)

insert into table1 (field1, field3)  values (5,10)

However, if you don't want to do this, use the DEFAULT keyword

insert into table1 values (5, DEFAULT, 10, DEFAULT)

Related questions

Inserting multiple rows in a single SQL query?

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "…

Read More
Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?

I know I've done this before years ago, but I can't remember the syntax, and I can't find it anywhere due to pulling up tons of help docs and articles about "bulk imports". Here's what I want to do, but …

Read More
SELECT INTO a table variable in T-SQL

Got a complex SELECT query, from which I would like to insert all rows into a table variable, but T-SQL doesn't allow it. Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries. …

Read More