Ask the Expert

Generating rows with default values

I have created a table xyz as

create table xyz
(eno  char(1) default '1');

I want to insert this default value many times (i.e. 10) into table xyz. How can I do it?

    Requires Free Membership to View

The simple answer is just to do 10 inserts --

insert into xyz (eno) values ('1');
insert into xyz (eno) values ('1');
insert into xyz (eno) values ('1');
insert into xyz (eno) values ('1');
insert into xyz (eno) values ('1');
insert into xyz (eno) values ('1');
insert into xyz (eno) values ('1');
insert into xyz (eno) values ('1');
insert into xyz (eno) values ('1');
insert into xyz (eno) values ('1');

One thing I'm not sure of is whether you can avoid mentioning the column here. If you have to mention it, then you have to assign a value, and that makes the default value useless. If you can avoid mentioning the column, then the default value can be anything and you would never have to change the SQL.

Consider a similar case with

create table abc
 ( foo integer 
 , bar char(1) not null default '1' );

In this example, you are allowed to write

insert into abc (foo) values (1);
insert into abc (foo) values (2);
insert into abc (foo) values (3);
insert into abc (foo) values (4); etc.

The inserted rows would each have '1' in the bar column, assigned as the default. In other words, you don't have to mention a column that has a default value (or is nullable).

But in your case, the table has only one column, so not mentioning it would require syntax such as

insert into xyz () values ();

... or

insert into xyz values;

... and as I said, that might not work.

Incidentally, if you happen to have a table of INTEGERS lying about, such as

create table integers 
   (i integer);
insert into integers (i) values (1);
insert into integers (i) values (2);
insert into integers (i) values (3);
insert into integers (i) values (4); etc.

... then you can generate all the inserts in one query --

insert into xyz 
  select '1' from integers where i < 11;

This is pretty sneaky, but nobody said the SELECT list actually has to include any columns from the table being selected from. Here, there will simply be one row in the result set for every row that passes the WHERE condition. Each result row consists of a column that contains the value '1' and these are then inserted into the xyz table.

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in March 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: