allow null and insert

[Jon Newton]

I have a simple table with:
first, char , allow null=false
middle, char , allow null=false
last, char , allow null=false

When inserting a record, sql=insert in table (first,last)
values('bob','jones')

I get an err msg like "Cannot insert the value NULL into column 'last'
... column does not allow nulls..."

For now I can not change the insert statement to specify a value for
the missing colum. Does anyone know a solution to this?

The only solutions I can think of is to set the columns to allow null
or set a default value for each column. is there any way to perform
either of these operations en masse on a large set of tables? Doing
this by hand is unsavory since I'm dealing w/ ~100 tables, some of
which have around 100-200 columns. it's a legacy database :( .

or perhaps there is a way to specify these properties when i import
the table definitions? (tables are coming from old as400/db2)

thanks much

[BP Margolin]

Jon,

Check whether you have a trigger on the table, and whether the error message
is actually coming from the trigger.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Jon Newton" <j...@scansoftware.com> wrote in message
news:94bcb733.01112...@posting.google.com...

[Jon Newton]

BP,

there is no trigger on the table. Am I correct in thinking that the
cause is due to the allow null property on the columns?

ps. using clean install of sql server 7, setup with default
configuration. Created tables by importing via DTS import wizard from
DB2

"BP Margolin" <bpm...@attglobal.net> wrote in message news:<3c041...@news3.prserv.net>...

[Ross Neilson]

> > > The only solutions I can think of is to set the columns to allow null
> > > or set a default value for each column. is there any way to perform
> > > either of these operations en masse on a large set of tables? Doing
> > > this by hand is unsavory since I'm dealing w/ ~100 tables, some of
> > > which have around 100-200 columns. it's a legacy database :( .

If you can set the column to temporarily allow NULLs until you can sort
things out at a later date, then I would suggest that. Otherwise, you could
set up a loop that goes through each table name,
inserts it into a 'template' dynamic SQL string (using Replace), and then
execute that SQL using sp_ExecuteSQL. The SQL statement would be ALTER TABLE
etc, to change the table to the required spec, ie allowing NULLs on that
column or adding a DEFAULT value.

That's what I reckon anyway, I'm sure BP Margolin will have an idea too...

Regards,

Ross

[BP Margolin]

Jon,

My apologies ... I misread your original post. I was interpreting "allow
null=false" as meaning that the column was defined as NULLable ... when in
fact it is defined as NOT NULL.

Yes, you are absolutely correct. Since the column is defined as NOT NULL,
you must supply a value for the column. Consider adding a DEFAULT constraint
for the column for the times that you do not wish to explicitly supply a
value. In T-SQL terms, the CREATE TABLE would look something like:

create table SimpleTable
(
first char(??) NOT NULL ,
middle char(??) NOT NULL DEFAULT (''),
last char(??) NOT NULL
)

This will automatically assign an empty string (which is different from a
NULL) to the "middle" column without you having to change your INSERT.