CODEWITHSUNDEEP
t-sql
Zbigniew Wazeliniak
Zbigniew Wazeliniak

Reputation: 1161

How to insert default values in SQL table?

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?

Upvotes: 111

Views: 264214

Answers (11)

Enrique
Enrique

Reputation: 115

There are 3 ways to achieve this:

First, specify the columns you want to fill out and omit the ones that you want to have their default value:

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

Second, don't specify any columns and use the DEFAULT keyword for the columns that you want to have their default value:

INSERT INTO table1 VALUES (5, DEFAULT, 10, DEFAULT);

Third, and what I would recommend if you want to be meticulous, combine both ways:

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

Upvotes: 2

inon
inon

Reputation: 1772

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

Upvotes: 67

Chris
Chris

Reputation: 59501

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.

Upvotes: 8

Arthur Dent
Arthur Dent

Reputation: 1

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;

Upvotes: 0

Biddut
Biddut

Reputation: 457

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

Upvotes: 1

ram
ram

Reputation: 1

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

Upvotes: -7

aroth
aroth

Reputation: 54806

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.

Upvotes: 100

gbn
gbn

Reputation: 432210

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)

Upvotes: 204

Beatles1692
Beatles1692

Reputation: 5320

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.

Upvotes: 0

Oded
Oded

Reputation: 498972

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)

Upvotes: 3

fkerber
fkerber

Reputation: 1042

Try it like this

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

Then field2 and field4 should have default values.

Upvotes: 8

Related Questions