How to insert default values in SQL table?

Question

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?

See also [this question](http://stackoverflow.com/questions/25900996) for the case where *all* columns have default values. — stakx - no longer contributing, Sep 17 '14 at 22:07
Please, never post "_doesn't work_" without defining **how**. — underscore_d, Dec 31 '18 at 19:29

score 196 · Answer 1 · answered Jan 08 '12 at 13:06

196

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)

answered Jan 08 '12 at 13:06

gbn

422,506
82
585
676

18

Awesome! This is exactly what I was looking for. Note: you can also specify the columns and values, passing DEFAULT instead of NULL and you'll get the default value if there is a DEFAULT constraint, or NULL if the column is nullable and no default constraint is specified. – John Feb 05 '13 at 18:30
4

This _does_ work, but when I tried to be fancy using `(CASE WHEN This = That THEN 'SpecifiedValue' ELSE DEFAULT END)` it would _not_ work, much to my chagrin. I still had to either prepare multiple Insert-Statements to cover all my scenarios or use Dynamic-SQL. :( – MikeTeeVee Aug 13 '18 at 13:14
For some reason I couldn't make this work with the DB Browser for SQLite. Kept complaining about a syntax error around `DEFAULT`. I had to specify the non-default columns as in the top example, then just provide values for those, and then it finally worked. – Will Matheson Mar 17 '20 at 23:49
The [official documentation](https://www.sqlite.org/lang_insert.html) says nothing about that. Is this use of `DEFAULT` safe? (If it's undocumented, we cannot complain when the functionality is omitted in the future version of sqlite3 without any notice.) – ynn Mar 24 '20 at 06:26
@ynn In addition, the book [*Using SQLite*](http://shop.oreilly.com/product/9780596521196.do) (p.326) says "*There is no way to specify a default value except by leaving the column out of the column list, or knowing what the default value is and explicitly inserting it.*". – ynn Mar 24 '20 at 15:13

aroth · Accepted Answer · 2013-02-24T06:18:14.610

99

Just don't include the columns that you want to use the default value for in your insert statement. For instance:

INSERT INTO table1 (field1, field3) VALUES (5, 10);

...will take the default values for field2 and field4, and assign 5 to field1 and 10 to field3.

edited Feb 24 '13 at 06:18

answered Jan 08 '12 at 12:11

aroth

54,026
20
135
176

2

What if column has not defined default values and is also set to "NOT ALLOW NULLS"? – Danon Jan 23 '17 at 20:47
1

@binki Maybe I should rephrase my comment: "aroth: your answer isn't complete, because of possible exception when the column has not defined default values and is also set to 'NOT ALLOW NULLS'" – Danon Jan 01 '19 at 13:04
4

@Danon - The OP states "I want to insert a row which has the default values for field2 and field4" and asks "How can I tell the database to use the default value for the column when I insert a row?". You're correct that this answer doesn't cover the case when default values _aren't_ defined, however as the question is explicitly _not_ asking about that case it's not really accurate to describe the answer as incomplete. You're talking about a completely different question (which is best asked as a separate question). – aroth Jan 01 '19 at 13:48
Why does SO bury this accepted answer with almost 100 upvotes at the 6th position? – John Aug 22 '22 at 16:03

score 61 · Answer 3 · edited Aug 23 '21 at 16:11

61

This works if all the columns have associated defaults and one does not want to specify the column names:

insert into your_table
default values

edited Aug 23 '21 at 16:11

spongebob

8,370
15
50
83

answered Jun 25 '17 at 06:12

inon

1,689
1
19
34

score 8 · Answer 4 · answered Jan 08 '12 at 12:10

8

Try it like this

INSERT INTO table1 (field1, field3) VALUES (5,10)

Then field2 and field4 should have default values.

answered Jan 08 '12 at 12:10

fkerber

1,032
9
24

score 6 · Answer 5 · answered Aug 10 '21 at 00:07

I had a case where I had a very simple table, and I basically just wanted an extra row with just the default values. Not sure if there is a prettier way of doing it, but here's one way:

This sets every column in the new row to its default value:

INSERT INTO your_table VALUES ()

Note: This is extra useful for MySQL where INSERT INTO your_table DEFAULT VALUES does not work.

score 3 · Answer 6 · answered Jan 08 '12 at 12:10

If your columns should not contain NULL values, you need to define the columns as NOT NULL as well, otherwise the passed in NULL will be used instead of the default and not produce an error.

If you don't pass in any value to these fields (which requires you to specify the fields that you do want to use), the defaults will be used:

INSERT INTO 
  table1 (field1, field3) 
VALUES   (5,10)

Biddut · Answer 7 · 2018-02-11T13:04:08.690

1

You can write in this way

GO
ALTER TABLE Table_name  ADD
column_name decimal(18, 2) NOT NULL CONSTRAINT Constant_name DEFAULT 0
GO
ALTER TABLE Table_name SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

edited Feb 11 '18 at 13:04

answered Feb 08 '18 at 19:23

Biddut

418
1
6
17

Arthur Dent · Answer 8 · 2021-01-26T18:49:29.047

0

CREATE TABLE #dum (id int identity(1,1) primary key, def int NOT NULL default(5), name varchar(25))

-- this works
INSERT #dum (def, name) VALUES (DEFAULT, 'jeff')

SELECT * FROM #dum;

DECLARE @some int 

-- this *doesn't* work and I think it should
INSERT #dum (def, name)
VALUES (ISNULL(@some, DEFAULT), 'george')

SELECT * FROM #dum;

edited Jan 26 '21 at 18:49

answered Jan 26 '21 at 18:41

Arthur Dent

1
1

score 0 · Answer 9 · edited Jan 08 '12 at 12:27

0

To insert the default values you should omit them something like this :

Insert into Table (Field2) values(5)

All other fields will have null or their default values if it has defined.

edited Jan 08 '12 at 12:27

marc_s

732,580
175
1,330
1,459

answered Jan 08 '12 at 12:17

Beatles1692

5,214
34
65

score -6 · Answer 10 · edited Jul 14 '15 at 08:43

-6

CREATE PROC SP_EMPLOYEE                             --By Using TYPE parameter and CASE  in Stored procedure
(@TYPE INT)
AS
BEGIN
IF @TYPE=1
BEGIN
SELECT DESIGID,DESIGNAME FROM GP_DESIGNATION
END
IF @TYPE=2
BEGIN
SELECT ID,NAME,DESIGNAME,
case D.ISACTIVE when 'Y' then 'ISACTIVE' when 'N' then 'INACTIVE' else 'not' end as ACTIVE
 FROM GP_EMPLOYEEDETAILS ED 
  JOIN  GP_DESIGNATION D ON ED.DESIGNATION=D.DESIGID
END
END

edited Jul 14 '15 at 08:43

Brian Tompsett - 汤莱恩

5,753
72
57
129

answered Jun 30 '15 at 10:26

ram

1

7

I don't think this an answer. – Giulio Caccin May 18 '17 at 11:37

How to insert default values in SQL table?

10 Answers10

Linked

Related