sql server new users: nothing is the inserted value -- sql server new users

Re: nothing is the inserted value

Rich
2/22/2006 9:22:28 AM

Hello Tom,

old data to be inserted:

RecordID Date Comments
-------------------------------------------------------------------
1 1/1/2006 From New York
see log
2 1/2/2006 Ohio was not on
line see log
3 3/10/2006 Ohio connection
lost see log

This is the new table that will except the data:

recordID IPAddress HopID Date Comments

[recordID] [int] NOT NULL ,
[IPAddress] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[HopID] [int] NOT NULL ,
[Date] [datetime] NOT NULL ,
[Comments] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

Notice I have two extra fields that in the new database which willnot except
nulls. My question was how do I insert empty into a field which will not
except a null?

+++++++++++++++++++++++++++

[quoted text, click to view]

Re: nothing is the inserted value

Rich
2/22/2006 10:55:39 AM

Hello Andrew,

1) sure, going forward as records come into this "store bought application"
the fields will be filled with whatever the frontend places into them.
2) for now, I have to migrate what little data is in the old system to this
new one and most of the fields do not map. The data just is not in the old
system.
3) I thought I had seen some place that CTRL-ZERO was empty but I have not
been able to verify that, besides, how could I place that in the insert query
when I migrate all of the old data anyway?

Rich

[quoted text, click to view]

Re: nothing is the inserted value

Andrew J. Kelly
2/22/2006 1:01:18 PM

You see that is the whole point of declaring a column NOT NULL. It means
you must put something into it. If the business rules state these columns
must have valid data then you need to provide that data. If it doesn't
matter you should either not declare them as NOT NULL or provide a DEFAULT
Constraint for these two columns. The question is what should the default
value be? That is something I can't say since this is your db and your
business rules will dictate that. But if there are no business rules and
other applications aren't expecting something specific here you can make the
default of ten spaces for the IP address and 0 for the HopID. But my guess
is this will break something else.

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Re: nothing is the inserted value

Rich
2/22/2006 1:46:27 PM

Hello Andrew,
yes I am using DTS. I suspect that placing some default data such as a zero
or a space in the field which is appropriate is my only option. If the field
is set for NOT NULL, you just have to place something there.

[quoted text, click to view]

Re: nothing is the inserted value

Andrew J. Kelly
2/22/2006 4:23:42 PM

How are you migrating the data? If you use DTS or SSIS you can simply add
spaces and a 0 for those two columns.

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Re: nothing is the inserted value

Andrew J. Kelly
2/22/2006 5:31:42 PM

Actually probably the easiest way to deal with this is to edit the table
definition and add a default constraint for those two columns with the
appropriate values. Then if the DTS package just references the other 3
columns in the insert it will fill them in with the defaults. If your
existing data is a mixture of some of these columns populated and some not
you have to use a transform in the package or add it into the query. You
probably can use something like this:

INSERT INTO NewTable ([RecordID], [Date],[Comments],[HopID],[IPAddress])

SELECT [RecordID], [Date],[Comments], COALESCE([HopID],0),
COALESCE([IPAddress],' ')
FROM OldTable

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Re: nothing is the inserted value

Stu
2/22/2006 8:50:14 PM

Sorry to butt in, but just caught this; although Andrew's advice is
probably the easiest way to fix your data import issues, be careful
when editing tables in vendor-supplied applications. If you have a
long-term support agreement with the vendor, you may violate your Terms
of Service by editing their database without prior approval. Of
course, you may already be doing so by adding your own pre-existing
data to the application; I'm just reminding you that there may be
other factors surrounding this problem.

If it's a stand-alone application that you purchased with no ongoing
support, AND you feel comfortable changing the default constraints,
then Andrew's advice is good.

Stu

Re: nothing is the inserted value

Lawrence Garvin
2/23/2006 2:18:31 PM

Quite frankly, I'd be asking the /vendor/ what their recommendation is for
importing historical data, since the nature of these two columns is such
that many customers probably have not captured the data.

[quoted text, click to view]

and.. btw.... who defines an IPAddress as TEN characters???????

Last I checked, an IPAddress required up to 15 characters! aaa.bbb.ccc.ddd

[quoted text, click to view]

Re: nothing is the inserted value

Mike Hodgson
2/24/2006 12:00:00 AM

[quoted text, click to view]
Agreed.

[quoted text, click to view]
Actually, if you're getting picky, I think it would be more accurate to
defined an IP address as a 32-bit integer, but most people think in
character strings when it comes to IP addresses rather than decimal values.

--
*mike hodgson*

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.