Problem inserting with not null fields

Question

Problem inserting with not null fields

Dannygr

SSC Eights!

Points: 984
More actions
July 21, 2005 at 1:57 am

#66777

I need to insert 10 fields into a table that has 200 fields. The problem is I only need to insert into 10 fields but all fields are set to ‘not null’. Is there a way I can do the insert without having to put a space character into all the not null fields?

Thanks
Danny

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Do the not null columns have defaults defined on them?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dannygr SSC Eights! Points: 984 More actions · Answer 2

No they do not have defaults defined on them.

Cheers

Danny

Phil Parkin SSC Guru Points: 246959 More actions · Answer 3

There's something fishy going on here - presumably the fields were defined as not null for a reason, in which case you should be populating them with sensible data. Or the fields should be changed to allow nulls and your insert will work as you want. Or set them up with defaults as implied by GilaMonster.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Dannygr SSC Eights! Points: 984 More actions · Answer 4

An accounting application uses the database I am trying to insert into. The design is terrible. However I can automate data input by inserting data into this particular table. I did not realise what GilaMonster was saying. I think that will work.

Thanks

Danny

CJohnson-232084 Default port Points: 1492 More actions · Answer 5

Danny,

If an accounting app is using this table you need to be VERY sure that these fields are not used.

I know some accounting apps are abysmally designed from a normalization point of view. They are often written by accountants, not trained programmers/DBAs. However, accounting systems (automated or manual) by necessity have realtionships that cannot be ignored if you want to produce accurate books-of-record. I once heard one of our accounting directors complain that he couldn't make a one-sided journal entry.

I don't want to belabor the point, but tinkering with or subverting the system that produces a company's financial statements and is used in filing tax reports/returns is an excellent way to begin the search for a new job or a good laywer.