?
Solved

SQL Server 2005 Allow Nulls = False

Posted on 2011-05-12
9
Medium Priority
?
475 Views
Last Modified: 2012-08-13
Good Day Experts

I am in a bit of a quandry here.  I have a SQL Server 2005 table that has 234 fields.  All of the fields have the Allow Nulls property set to false.  I will only need to send data to about 20 fields at the most.  Short of writing ' ' to each of the fields I am not sending data to, is there anything I can dynamically do in the query that would do that for me so I don't have to manually for 200+ fields?

Thanks,
jimbo99999
0
Comment
Question by:Jimbo99999
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35746156
If possible you could alter the table definition and default the fields to ''

Failing that there isn't an easy way that I know of. Each field would need to be explicitly set to '' otherwise it will complain.

Lee
0
 
LVL 10

Expert Comment

by:GlobaLevel
ID: 35746171
Without knowing more... Just write an update statement on those 234 fields some that separates them from the other ones:

Update t
Set clientid= 'x'
From table1 t
Where t.unique_for_234_rows = 'i'
0
 
LVL 10

Expert Comment

by:GlobaLevel
ID: 35746194
Oops I misread not 234 but 20
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 35746349
Why not set the columns to allow NULL?  Isn't that truly the easiest option?

Also, You might want to consider breaking that 234 column table into multiple tables.  That way, you could have the 20 required columns in a Master table and put the other columns, organized into logical groupings, into other tables.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35746459

You can set default values for the other columns or if they can be null, just remove the not null requirement
0
 
LVL 3

Assisted Solution

by:chandrasekar1
chandrasekar1 earned 600 total points
ID: 35746532
jimbo99999 , u have not mentioned in which way ur inserting data to the table, as per my thinking there are possibly 3 ways to achieve this

1. While creating the Table columns, go to the Design -> select the column -> Option called "Default value or Binding" -> ('') -> save the table, this will solve ur problem

 Default column values in MSSQL
or follow the below other steps....

2. if u are constructing the Query from frond end then add '' empty quotes in the query which are the fields does not have values
3. as usual, if u used Store procedure to insert the data, then
ALTER PROC [dbo].[Name]
(
@Name NVARCHAR(64)
,@MachineName VARCHAR(64) = ' '
,@MachineIP VARCHAR(20) = ' '
as per above script, from front u will pass value to @Name, the rest of the 2 fields inserted as null or ''
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 200 total points
ID: 35747837
Guys,

At the risk of starting a "To NULL or Not To NULL" argument, IMHO, there is a legitimate reason for NULLs to be allowed in this case.  If you don't have any data for a set of columns, that is completely different from having data for most of the columns in the set but not for others.  If you stuff a space or an empty string into the columns instead of allowing NULLs, then how do you know whether there was no data or the was data but it happened to be a space?

Also, if there are sets of columns that tend to have data when other sets don't have data, then why not break those sets of columns out into separate columns?
0
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 1200 total points
ID: 35752487
Please set default value for those fields as ''
0
 
LVL 3

Expert Comment

by:chandrasekar1
ID: 35820634
Thanks jimbo99999, have a nice day :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question