2

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. – a_horse_with_no_name Mar 30 '14 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. – Anti-weakpasswords Mar 30 '14 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 '14 at 6:17
6

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.

0

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;
go

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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