I have several tables with different number of columns, each can be either real or int, both not null (I can define them all real if required).

I would like to write a query that inserts a row with all zeros.

INSERT [TablesName] DEFAULT VALUES works good for nullable columns.

I can't use INSERT INTO table1 (field1, field2) VALUES (0, 0.0); as I don't know the number of columns.

Is there a query that would insert a new row with default values for not null tables?

  • 1
    How can you not know the number of columns of a table? Btw: the nullable attribute is independent of the default value of a column.
    – user1822
    Mar 30, 2014 at 11:16
  • use sp_help, or join sys.tables to sys.columns, or use the GUI, but determine the structure of the table before you write an insert. Mar 30, 2014 at 19:53
  • 1
    I am writing a C# function that accepts a table name, truncates it and then initializing it with a single row - the row should contain all zeros.
    – Ofiris
    Mar 31, 2014 at 6:17

2 Answers 2


Put defaults on the columns, so that your DEFAULT VALUES works.

In your table definitions just put DEFAULT 0 after each column definition - should be easy.


In Sql Server there is also a bit more flexibility. You can specify that a specific column use it's default value in the VALUES clause, like this:

create table #t(x varchar(1) not null default 'x',y varchar(1) not null default 'y');

insert into #t(x,y) values(default,default);

select * from #t;
drop table #t;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.