Hi I have the following table:

tblMeasurement

StationID (char(7))
DateTime (smalldatetime)
ValidMeasurement (bit)
measurement (integer)

now the original Access table had the first three as PKs. When transfering
it from Access I lost the PK info and I also want to modify the data type
choices that were done automatically. When i try to save the changes in SQL
Server it complains about duplicates.

ValidMeasurement is of type bit. The reason is that it can happen to have
data  that are not yet validated but still of interest and use. Now, when
updating the database, we can have data from the same station, same datetime
that are validated BUT there already exist old data that are not validated.
So I want to include the ValidMeasurement field as part of the PK. SQL
Server complains that you can NOT have a bit type as part of a PK.


Should I convert the data type to char(1)?
Should I create a SP to only update data when there doesnt exist an
equivalent measurement thats validated?(how would I do that?)

Any comments/suggestions would be Greately appreciated!
TIA

-steve

Please always tell us the version of SQL Server you are using. In SQL
Server 2000 you certainly can have a BIT datatype as part of a PRIMARY
KEY. Enterprise Manager won't let you do it though. Don't use
Enterprise Manager to modify tables because it has too many problems
and limitations. Use an ALTER TABLE statement:

ALTER TABLE tblMeasurement
ADD CONSTRAINT pk_measurement
PRIMARY KEY (stationid, [datetime], validmeasurement)

I can't say I like this design. Why would you want to keep the
non-validated measurement as well as the validated one? Why not just
update the valid attribute? (You wouldn't need to make a part of the
key at all in that case). Personally I would avoid using BIT here. BIT
has some peculiar behaviour and CHAR(1) is often a more convenient way
to record status.

DATETIME is a reserved word. Don't use it as a column name. It's a
pretty uninformative column name anyway because it doesn't tell us what
it's the date and time of.

Thanx for the fast and long response!

Well I am working alone creating the DB, Interface, getting the data from
various sources , etc. so time does not allow me to master or look for the
best solutions either.

I actually use date_time , I should have been more careful wording my
question and I also purposefuly hid a couple of other details.
For example, there is also a field called flag (one character, that can
signify: T(race), M(isssing), E(stimated), etc. for the various
measurements. And a field called Parameter which is a 3 character code
signifying the parameter we are measuring.e.g. 101 for Temperature, 233 for
Wind velocity, etc.

Would you be able to guide me *without wasting much of your time* in the
following:

These are meteorological data and I would *like* to have the following:

Update the data in such a way that:
1) IF there is (from the *same* Station, DateTime and Parameter) a
measurement with Valid = False, then we delete it and put the new     VALID
value

2) IF there is no "equivalent" Valid measurement, then we just insert the
Valid=False for now  till a valid measurement comes along (if ever)

Non-valid doesnt mean BAD, it means it has not yet been confirmed by a team
that "validates" data. Most times the validated data IS the same as the
non-validated. So I need this information.

In other words,  we dont have to have BOTH a valid and not valid
measurement, but if *either* exists we should!

-How can i automate this process so that people with not much experience can
update the database from csv or ASCII files?

-I will not use Ent. Manager for the table updates as you suggested.

With the new scheme of things, Valid will not have to be PK, do I still keep
it as Bit or its better space and speed-wise as char(1) ?

I would appreciate if you could point out a couple of sites/links as I do
not have a lot of time to become an "expert".

Thanx again and my apologies if i bombarded you with questions.

-steve

message de news: 1115907628.253886.293***@g14g2000cwa.googlegroups.com...
Show quoteHide quote

On Thu, 12 May 2005 11:04:19 -0400, steve wrote:

(snip)
Hi Steve,

If it helps you, it's not wasted! :-)


UPDATE Measurement
SET    Value = @NewValue
     , Valid = 'F'    -- for False
WHERE  StationID = @StationID
AND    DateTime = @DateTime    -- Note: better change this
                                --       to non reserved word

This will change the measurement if it already exists, or do nothing if
it doesn't exist yet. It will also change the status from valid to
non-valid. If you don't want that, than remove the ", Valid = 'F'" line
to keep the status unchanged. And optionally add "AND Valid = 'F'" at
the end to prevent changing a valid measurement.


INSERT INTO Measurement (StationID, DateTime, Valid, Value)
SELECT @StationID, @DateTime, 'F', @NewValue
WHERE NOT EXISTS (SELECT *
                  FROM   Measurement
                  WHERE  StationID = @StationID
                  AND    DateTime = @DateTime)

This will insert the measurement if none exists, and do nothing if there
already is a measurement at the specified station and datetime (either
valid or not valid).


The best is probably to write a stored procedure that imports the CSV or
ASCII file in a staging table (check BULK IMPORT or bcp.exe in Books
Online). Then copy the data over, using a variation of the commands
above:

-- Step 1: update values for all measurements that already exist
UPDATE      m
SET         Value = s.NewValue
-- maybe remove the line below?
          , Valid = 'F'
FROM        Measurement AS m
INNER JOIN  Staging AS s
      ON    s.StationID = m.StationID
      AND   s.DateTime = m.DateTime
-- maybe add the line below?
-- WHERE    m.Valid = 'F'

-- Step 2: add values for all new measurements
INSERT INTO Measurement (StationID, DateTime, Valid, Value)
SELECT      s.StationID, s.DateTime, 'F', s.NewValue
FROM        Stagins AS s
WHERE NOT EXISTS (SELECT *
                  FROM   Measurement AS m
                  WHERE  m.StationID = s.StationID
                  AND    m.DateTime = s.DateTime)

If you put this all in a stored proc (and add some error handling), it
can be operated by anyone without needing any DB knowledge.


I'd use CHAR(1), since it allows you to add more statuses later, should
the need ever arise.

Best, Hugo

Thank you all so much!
I *really* appreciate the help and time.

I see that I still have a lot of work ahead to read, absorb and try the new
material.

-steve

vnJge.28282$L31.89***@wagner.videotron.net...
Show quoteHide quote

SLOW UDF
Resequence column
Why do I have to declare these stored proc variables multiple time
Linked Server Yields inconsistent results
The value of @@Error after Select statement