Deleting Manual Data Entries

When developing a system one usually has to enter test data into the
database, and usually you have to start out with doing it manually. At least,
I always find myself in that situation having developed an empty database
which needs to be filled with test data for the application development
process to go smoothly.
A problem with relation databases when entering data manually is
dependencies. I am well aware of not having to enforce key dependencies and I
know they can be removed (and then yet again added) to table relations. But I
wish to do things as quickly and smoothly as possible.
I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
2005. One problem when manually entering data seems to stick and never go
away, and it is quite frustrating. When having created a database structure
(or, perhaps someone else has!) I start entering data. Sometimes you are too
quick and start entering data in tables in the wrong order so that you end up
with a record half filled in with one or a few mandatory dependency keys to
other [empty] tables. And here's the problem.
It is a catch 22 situation since I cannot leave the field blank (dependency
key required!) and I cannot enter a value. And the problem is SQL Server
requires one of the two solutions - it is not possible to delete the record
altogether and start anew. Actually, it isn't even possible to close the SQL
Server management studio - you HAVE TO enter a value yet ALL VALUES ARE
ILLEGAL.
In this situation there is only two things you can do: either you force the
SQL Server management studio to shut down using the task manager, or you
start adding records to the dependency table(s). The former is not a good
solution, and the latter isn't necessarily feasible (some complex systems
have too large data hierarchies to make it workable).
I was hoping this issue would be resolved for the 2005 version, but it
isn't. There has to be a way to delete a memory-based not completed record
without the whole SQL Server crashing. And since identity seed and default
value columns aren't filled in immediately upon finishing the record (they
were in 7.0 and 2000!) my guess is that the record isn't physically saved
until later. So why create this stupid catch 22 situation? It is very, very
frustrating!Per
> I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
> 2005. One problem when manually entering data seems to stick and never go
> away, and it is quite frustrating. When having created a database
> structure
> (or, perhaps someone else has!) I start entering data. Sometimes you are
> too
> quick and start entering data in tables in the wrong order so that you end
> up
> with a record half filled in with one or a few mandatory dependency keys
> to
> other [empty] tables. And here's the problem.
I'm little bit confused with your arguments. If you know the database's
structure , it is easy to fill a 'parent' table first and then all child
tables in order to get it filled in the right way. Have you created a
diagram of the databse to see what is a relationship between tables, and if
you define a column does not accept NULL , you must provide a value
,otherwise allow NULLs to the column
I have just starting a new application which works against a database and
need to insert sample data , so first I have all doucumenation about the
database ,
a diagram, dependencies , data intgerity and etc.... so i 've just easily
filled the data
> I was hoping this issue would be resolved for the 2005 version, but it
> isn't. There has to be a way to delete a memory-based not completed record
> without the whole SQL Server crashing. And since identity seed and default
> value columns aren't filled in immediately upon finishing the record (they
> were in 7.0 and 2000!) my guess is that the record isn't physically saved
> until later. So why create this stupid catch 22 situation? It is very,
> very
> frustrating!
I try to avoid entering the data directly from SSMS , whu not ctreate a
script that does inserting?
"Per Bylund" <Per Bylund@.discussions.microsoft.com> wrote in message
news:8BB178FE-1C18-4B3E-8F70-38D96016A79E@.microsoft.com...
> When developing a system one usually has to enter test data into the
> database, and usually you have to start out with doing it manually. At
> least,
> I always find myself in that situation having developed an empty database
> which needs to be filled with test data for the application development
> process to go smoothly.
> A problem with relation databases when entering data manually is
> dependencies. I am well aware of not having to enforce key dependencies
> and I
> know they can be removed (and then yet again added) to table relations.
> But I
> wish to do things as quickly and smoothly as possible.
> I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
> 2005. One problem when manually entering data seems to stick and never go
> away, and it is quite frustrating. When having created a database
> structure
> (or, perhaps someone else has!) I start entering data. Sometimes you are
> too
> quick and start entering data in tables in the wrong order so that you end
> up
> with a record half filled in with one or a few mandatory dependency keys
> to
> other [empty] tables. And here's the problem.
> It is a catch 22 situation since I cannot leave the field blank
> (dependency
> key required!) and I cannot enter a value. And the problem is SQL Server
> requires one of the two solutions - it is not possible to delete the
> record
> altogether and start anew. Actually, it isn't even possible to close the
> SQL
> Server management studio - you HAVE TO enter a value yet ALL VALUES ARE
> ILLEGAL.
> In this situation there is only two things you can do: either you force
> the
> SQL Server management studio to shut down using the task manager, or you
> start adding records to the dependency table(s). The former is not a good
> solution, and the latter isn't necessarily feasible (some complex systems
> have too large data hierarchies to make it workable).
> I was hoping this issue would be resolved for the 2005 version, but it
> isn't. There has to be a way to delete a memory-based not completed record
> without the whole SQL Server crashing. And since identity seed and default
> value columns aren't filled in immediately upon finishing the record (they
> were in 7.0 and 2000!) my guess is that the record isn't physically saved
> until later. So why create this stupid catch 22 situation? It is very,
> very
> frustrating!|||"Uri Dimant" wrote:
> I'm little bit confused with your arguments. If you know the database's
> structure , it is easy to fill a 'parent' table first and then all child
> tables in order to get it filled in the right way. Have you created a
> diagram of the databse to see what is a relationship between tables, and if
> you define a column does not accept NULL , you must provide a value
> ,otherwise allow NULLs to the column
> I have just starting a new application which works against a database and
> need to insert sample data , so first I have all doucumenation about the
> database , a diagram, dependencies , data intgerity and etc... so i've just
> easily filled the data
There is nothing to be confused about. I'm working in large projects with
many developers developing different parts of the application at the same
time - they all need sample data to develop and test their components or
functions. Even though I do use diagrams (and I do print them) this
limitation in SQL Server is quite frustrating - there are always people
getting stuck in the catch 22 having to forcefully shut down their SQL Server
client.
Imagine a project with 20 or 30 developers (or more) who start working in
the morning and some of them (perhaps 50%) start entering sample data. Quite
a few of them will get stuck with the stupid "you have to enter data but all
data entered is illegal" catch 22.
Of course, if everybody was well structured and thought through everything
first and started doing only after thinking it through - then we would have
no problem. But you cannot develop a software requiring the user to use it in
only one way. There will always be people stressed out forgetting to check
dependencies (or perhaps even not understanding dependencies). I am sometimes
in a rush myself and get caught by SQL Server's "catch 22 feature."
It should be fairly easy supporting a delete function for non-finished
records with dependencies. There is already such a function, but it doesn't
work (you cannot use it because SQL Server will not allow you to delete a
half finished record!) - so it must be a bug.
I believe I am a pretty good developer and I have quite a long experience
from using SQL Server's last four major versions. Even though I should avoid
this catch 22, I still get caught sometimes (but not too often). It is
something Microsoft should look into and fix.|||Per
Well , MS should perfom some internal work to make sure that the data you
entered in the right way, so why do we have CONSTRAINTS and many others
thing to pervent violation of the data integrity. Do you send a request to
MS?
So ,in your case I had also a project as you desribed, we just filled a
"central" database with sample data and then every developer had just
restored the database on his workstation, I see what is you point , so what
will be happened if the developer would want to add/remove /alter data? Well
, one thing I can say IT IS BY DESIGN
"Per Bylund" <PerBylund@.discussions.microsoft.com> wrote in message
news:3E4A1D5B-A970-4ACA-AE13-2CA14ECEAD9C@.microsoft.com...
> "Uri Dimant" wrote:
>> I'm little bit confused with your arguments. If you know the database's
>> structure , it is easy to fill a 'parent' table first and then all child
>> tables in order to get it filled in the right way. Have you created a
>> diagram of the databse to see what is a relationship between tables, and
>> if
>> you define a column does not accept NULL , you must provide a value
>> ,otherwise allow NULLs to the column
>> I have just starting a new application which works against a database
>> and
>> need to insert sample data , so first I have all doucumenation about the
>> database , a diagram, dependencies , data intgerity and etc... so i've
>> just
>> easily filled the data
> There is nothing to be confused about. I'm working in large projects with
> many developers developing different parts of the application at the same
> time - they all need sample data to develop and test their components or
> functions. Even though I do use diagrams (and I do print them) this
> limitation in SQL Server is quite frustrating - there are always people
> getting stuck in the catch 22 having to forcefully shut down their SQL
> Server
> client.
> Imagine a project with 20 or 30 developers (or more) who start working in
> the morning and some of them (perhaps 50%) start entering sample data.
> Quite
> a few of them will get stuck with the stupid "you have to enter data but
> all
> data entered is illegal" catch 22.
> Of course, if everybody was well structured and thought through everything
> first and started doing only after thinking it through - then we would
> have
> no problem. But you cannot develop a software requiring the user to use it
> in
> only one way. There will always be people stressed out forgetting to check
> dependencies (or perhaps even not understanding dependencies). I am
> sometimes
> in a rush myself and get caught by SQL Server's "catch 22 feature."
> It should be fairly easy supporting a delete function for non-finished
> records with dependencies. There is already such a function, but it
> doesn't
> work (you cannot use it because SQL Server will not allow you to delete a
> half finished record!) - so it must be a bug.
> I believe I am a pretty good developer and I have quite a long experience
> from using SQL Server's last four major versions. Even though I should
> avoid
> this catch 22, I still get caught sometimes (but not too often). It is
> something Microsoft should look into and fix.
>|||Per Bylund wrote:
> When developing a system one usually has to enter test data into the
> database, and usually you have to start out with doing it manually. At least,
> I always find myself in that situation having developed an empty database
> which needs to be filled with test data for the application development
> process to go smoothly.
> A problem with relation databases when entering data manually is
> dependencies. I am well aware of not having to enforce key dependencies and I
> know they can be removed (and then yet again added) to table relations. But I
> wish to do things as quickly and smoothly as possible.
> I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
> 2005. One problem when manually entering data seems to stick and never go
> away, and it is quite frustrating. When having created a database structure
> (or, perhaps someone else has!) I start entering data. Sometimes you are too
> quick and start entering data in tables in the wrong order so that you end up
> with a record half filled in with one or a few mandatory dependency keys to
> other [empty] tables. And here's the problem.
> It is a catch 22 situation since I cannot leave the field blank (dependency
> key required!) and I cannot enter a value. And the problem is SQL Server
> requires one of the two solutions - it is not possible to delete the record
> altogether and start anew. Actually, it isn't even possible to close the SQL
> Server management studio - you HAVE TO enter a value yet ALL VALUES ARE
> ILLEGAL.
> In this situation there is only two things you can do: either you force the
> SQL Server management studio to shut down using the task manager, or you
> start adding records to the dependency table(s). The former is not a good
> solution, and the latter isn't necessarily feasible (some complex systems
> have too large data hierarchies to make it workable).
> I was hoping this issue would be resolved for the 2005 version, but it
> isn't. There has to be a way to delete a memory-based not completed record
> without the whole SQL Server crashing. And since identity seed and default
> value columns aren't filled in immediately upon finishing the record (they
> were in 7.0 and 2000!) my guess is that the record isn't physically saved
> until later. So why create this stupid catch 22 situation? It is very, very
> frustrating!
Perhaps you should stop trying to use Management Studio/Enterprise
Manager to enter data into your tables as if they were spreadsheets, and
instead script your INSERT statements. Creating your test data in this
fashion, with scripts, will prevent you from getting caught up in the
GUI's error trapping, and will also give you the ability of re-using the
test data for future development.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Tue, 4 Jul 2006 02:14:02 -0700, Per Bylund <Per
Bylund@.discussions.microsoft.com> wrote:
(snip)
>It is a catch 22 situation since I cannot leave the field blank (dependency
>key required!) and I cannot enter a value. And the problem is SQL Server
>requires one of the two solutions - it is not possible to delete the record
>altogether and start anew.
Hi Per,
The reason that you can't delete the row, is that it's not inserted into
the database yet. So instead of trying to delete the row, why don't yu
just cancel the (at that moment still unfinished) attempt to insert?
Hit ESC. This should remove the new roow input. (Unless yoou have just
change the value in one field - in that case, the first ESC only undoes
the change to that field and aborting the complete row requires a second
ESC).
--
Hugo Kornelis, SQL Server MVP