How to Insert DEFAULT Constraint Values into a Table in SQL Server

SQL DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.

When a Field is declared as DEFAULT , it will take the default value specified , we no need to insert this value in the INSERT INTO statement.So we have to ignore or skip it.This we can do as follows :

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Customers

(

C_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255) DEFAULT 'Hyderabad'

Gender varchar(50)

)

INSERT Statement for Table with DEFAULT Constraint values

When a Field is declared as DEFAULT , it will take the default value specified , we no need to insert this value in the INSERT INTO statement.So we have to ignore or skip it.This we can do as follows :

INSERT INTO Customers values (123,'Excel','Reddy',DEFAULT,'Male')

Here , In the INSERT INTO statement we passed DEFAULT as a value for the DEFAULT Value City.,so that it will take the Default value specified(Hyderabad) in the Table Creation.

--------------------------------------------------------------------------------------------------------

How to List out Sub Category Items based on Main Category in Excel

How to show Sub Category Items of corresponding Main Category in Excel
Let us suppose we have the Main Regions in Column A starting from Range $A$2.,and the Column B contains the Sub Regions starting from Range  $B$2.

Requirement :
In Column $D$2 , we have a Dropdown list of Main Regions , when we select one Main Region , then the corresponding Sub Regions have to display in the Cells following to the $D$2.which means from $D$3,$D$4..
When Sub Regions are over for that Main Region , the Formula will display '-' in the Cells.

This Can be done Using the Following Formula :
=IF(OFFSET($A$1,(MATCH($D$2,$A:$A,0)-1)+ROW()-3,0)=$D$2,OFFSET($A$1,(MATCH($D$2,$A:$A,0)-1)+ROW()-3,1),"_")

Note :
Since we start writing the Formula from $D$3 , we have use the ROW()-3 .
Similarly if we start writing the Formula from $D$10 , we have to use the ROW()-10.

Then only it will fetch the correct Sub Region corresponding that Main Region from the corresponding Row.

Example :
The following Example replicates the above Requirement.

Important Note :

This is a Very useful in Reporting Requirement.

Thanks, TAMATAM