Just Learned - Insert default values in table's all columns

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

Insert default values in table's all columns

May 27 2011 3:36AM by Paresh Prajapati

We can insert all the default values in tables without specifiying values while insert in table, how? See here we have tables defined all columns with default,

CREATE TABLE defaultval(
    id int default 0, 
    name varchar(10) default 'test', 
    cdate datetime default getdate()
);

INSERT defaultval DEFAULT VALUES;

6 Comments

I use default values on the columns of a table in production, and the reasoning I follow is that "my table needs a default value of 0, so I should be ensuring that default values are defined always".

The insertion of default values is an approach I find useful when generating test data (I have only used it in production once).

commented on May 19 2012 10:07AM

Nakul Vachhrajani

4 · 33% · 10564

Thanks Paresh,

this was new to me.

But what happens if a or more columns don't have a default? Will be instead NULL inserted?

Greetings,

Michael

commented on May 21 2012 3:23AM

Michael Mikic

584 · 0% · 61

@Michael: Yes, a NULL would be inserted. That's because NULL is the default value for most datatypes.

commented on May 21 2012 9:30AM

Nakul Vachhrajani

4 · 33% · 10564

Nakul could you explain the scenario where u used this. Most tables will have PK and this won't help.

commented on May 24 2012 12:54AM

Sudeep Raj

12 · 13% · 4286

@Sudeep: Yes, most tables have PK-FK relationships and hence this approach won't work, but it does work when inserting data for columns that store flags. For example, if a system implements soft-delete, most records that get inserted are active records. Therefore, the BIT flag that would be storing this value can have a DEFAULT value of 0. That way, the application does not have to hard-code the 0.

That's just one of the scenarios where DEFAULT values can be used.

commented on May 24 2012 12:40PM

Nakul Vachhrajani

4 · 33% · 10564

Thanks Nakul :)

commented on May 25 2012 2:51AM

Sudeep Raj

12 · 13% · 4286

Your Comment

Sign Up or Login to post a comment.

"Insert default values in table's all columns " rated 5 out of 5 by 17 readers +Paresh Prajapati