Monday, August 5, 2013

Trick to Insert Default Values

Introduction

I have a table with Identity Columns and others have some Default values. I want to insert only the default values with the table.
In this article we are going to discuss it. Hope it will be interesting.






How I Do That

Step – 1 [ Create the Base Table ]

IF OBJECT_ID('tempdb..#temp_Table')IS NOT NULL
   BEGIN
       DROP TABLE #temp_Table;
   END
GO

CREATE TABLE #temp_Table
     (
         IDNO        INT     NOT NULL IDENTITY PRIMARY KEY,
         ENTRYDATE   DATE    NOT NULL DEFAULT GETDATE(),
         SEX         CHAR(1) NOT NULL DEFAULT('M')
      );

Here if we see the table object definition carefully we find that

IDNO
IDENTITY in nature (Auto Increment)
ENTRYDATE
With Default Values GETDATE(), always give the current date.
SEX
With Default Values ‘M’

So we are not going to insert anything, we are just using the Default values only to populate the table.

Step-2 [ How We DO THAT ]

INSERT INTO #temp_Table DEFAULT VALUES;

Step-3 [ Observing the Output ]


SELECT * FROM #temp_Table;

IDNO        ENTRYDATE  SEX
----------- ---------- ----
1           2015-06-10  M


Hope  you understand it.