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?
Try it like this
INSERT INTO table1 (field1, field3) VALUES (5,10)
Then field2 and field4 should have default values.
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
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
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)
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)
The best way is:
insert your_table
default values
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.
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;
Source: Stackoverflow.com