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

User · Answer

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

User · Answer

The best way is   insert your table default values

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try it like this  INSERT INTO table1  field1  field3  VALUES  5 10    Then field2 and field4 should have default values

User · Answer

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

[tsql] How to insert default values in SQL table?

Examples related to tsql

Examples related to insert

Examples related to default