What tool are you using?
>--Original Message--
>I have a table wherein previous entries on some rows were
deleted but the
>fields doesn't go away ex.
>tbl_name (one column table only)
>row1 name 1
>row2 (the entry is deleted but this is still showing a
blank space)
>row3 (the entry is deleted but this is still showing a
blank space)
>row4 (the entry is deleted but this is still showing a
blank space)
>row5 name 2
>How do i delete rows 2-4 in tbl_name so that it will only
show two entries
>row1 and row5 which should move into position 2 basically
deleting rows 2-4
>which contains no data and wont allow me to enter data in
them?
>thanks....
>.
>
When you delete the rows, it sounds to me like you are really only updating
the value of the data to a blank rather than actually removing the row.
The procedure in your application is probably doing an update like:
UPDATE tblName
SET columnName = ''
WHERE columnName = '<some criteria>'
The procedure in your application should be doing a DELETE like:
DELETE tblName
WHERE columnName = '<some criteria>'
To get rid of the currently empty rows, you could run something like:
DELETE tblName
WHERE LENGTH(columnName) = 0
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:08ff01c47afa$4c7e3a10$a301280a@.phx.gbl...[vbcol=seagreen]
> What tool are you using?
> deleted but the
> blank space)
> blank space)
> blank space)
> show two entries
> deleting rows 2-4
> them?
sql
Showing posts with label entries. Show all posts
Showing posts with label entries. Show all posts
Thursday, March 29, 2012
Deleting rows
What tool are you using?
>--Original Message--
>I have a table wherein previous entries on some rows were
deleted but the
>fields doesn't go away ex.
>tbl_name (one column table only)
>row1 name 1
>row2 (the entry is deleted but this is still showing a
blank space)
>row3 (the entry is deleted but this is still showing a
blank space)
>row4 (the entry is deleted but this is still showing a
blank space)
>row5 name 2
>How do i delete rows 2-4 in tbl_name so that it will only
show two entries
>row1 and row5 which should move into position 2 basically
deleting rows 2-4
>which contains no data and wont allow me to enter data in
them?
>thanks....
>.
>When you delete the rows, it sounds to me like you are really only updating
the value of the data to a blank rather than actually removing the row.
The procedure in your application is probably doing an update like:
UPDATE tblName
SET columnName = ''
WHERE columnName = '<some criteria>'
The procedure in your application should be doing a DELETE like:
DELETE tblName
WHERE columnName = '<some criteria>'
To get rid of the currently empty rows, you could run something like:
DELETE tblName
WHERE LENGTH(columnName) = 0
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:08ff01c47afa$4c7e3a10$a301280a@.phx.gbl...[vbcol=seagreen]
> What tool are you using?
>
> deleted but the
> blank space)
> blank space)
> blank space)
> show two entries
> deleting rows 2-4
> them?
>--Original Message--
>I have a table wherein previous entries on some rows were
deleted but the
>fields doesn't go away ex.
>tbl_name (one column table only)
>row1 name 1
>row2 (the entry is deleted but this is still showing a
blank space)
>row3 (the entry is deleted but this is still showing a
blank space)
>row4 (the entry is deleted but this is still showing a
blank space)
>row5 name 2
>How do i delete rows 2-4 in tbl_name so that it will only
show two entries
>row1 and row5 which should move into position 2 basically
deleting rows 2-4
>which contains no data and wont allow me to enter data in
them?
>thanks....
>.
>When you delete the rows, it sounds to me like you are really only updating
the value of the data to a blank rather than actually removing the row.
The procedure in your application is probably doing an update like:
UPDATE tblName
SET columnName = ''
WHERE columnName = '<some criteria>'
The procedure in your application should be doing a DELETE like:
DELETE tblName
WHERE columnName = '<some criteria>'
To get rid of the currently empty rows, you could run something like:
DELETE tblName
WHERE LENGTH(columnName) = 0
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:08ff01c47afa$4c7e3a10$a301280a@.phx.gbl...[vbcol=seagreen]
> What tool are you using?
>
> deleted but the
> blank space)
> blank space)
> blank space)
> show two entries
> deleting rows 2-4
> them?
Sunday, March 25, 2012
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
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
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 u
p
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 i
f
> 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 ju
st
> 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 Serve
r
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 i
n
only one way. There will always be people stressed out forgetting to check
dependencies (or perhaps even not understanding dependencies). I am sometime
s
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:
> 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 lea
st,
> 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 an
d I
> know they can be removed (and then yet again added) to table relations. Bu
t 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 structur
e
> (or, perhaps someone else has!) I start entering data. Sometimes you are t
oo
> 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 t
o
> other [empty] tables. And here's the problem.
> It is a catch 22 situation since I cannot leave the field blank (dependenc
y
> 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 recor
d
> altogether and start anew. Actually, it isn't even possible to close the S
QL
> 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 th
e
> 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, ver
y
> 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
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 u
p
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 i
f
> 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 ju
st
> 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 Serve
r
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 i
n
only one way. There will always be people stressed out forgetting to check
dependencies (or perhaps even not understanding dependencies). I am sometime
s
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:
> 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 lea
st,
> 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 an
d I
> know they can be removed (and then yet again added) to table relations. Bu
t 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 structur
e
> (or, perhaps someone else has!) I start entering data. Sometimes you are t
oo
> 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 t
o
> other [empty] tables. And here's the problem.
> It is a catch 22 situation since I cannot leave the field blank (dependenc
y
> 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 recor
d
> altogether and start anew. Actually, it isn't even possible to close the S
QL
> 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 th
e
> 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, ver
y
> 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
Thursday, March 22, 2012
Deleting entries under a "Category" that is being deleted??
Hello. I have a simple project using 3 tables: Categories, Subcategories, and Items. I have just gotten my insert, edit, and delete functions to work, but I've noticed a problem I hope someone can help with:
When I delete a certain category (lets say "Restaurants"), the subcategories and items that were in that category still remain in the database/table. (If I delete "Restaurants", the subcategories "Italian", "Seafood", etc - as well as any items in those subcategories - are not deleted).
So what would I need to do to delete any Subcategory that is in the deleted Category (shares a CategoryID) and then delete all Items in those Subcategories?
For reference, all Subcategories in a Category have reference to that CategoryID, and all Items in a Subcategory have a "SubcategoryID" field. I understand that I need to traverse the tables and remove all Subcategories with the CategoryID being deleted, but since the Items do not have a reference to the CategoryID, how would I delete those as well?
Thanks to whoever can help me out here!
First of all, you should add constraints to your tables. They will prevent you from removing Categorys if you have reletad Subcategorys in order to maintain referential integrity.
And then you have 2 options, the first is that in the constraint you declare that in deleting a Category, all releted SubCategorys (and Items) are deleted also.
The other option is that you delete the first all items in the table item that are related to subcategorys that are related to the category that you want to delete, then delete all items in the table subcategory that are related to the category that you want to delete and finally delete the category form the table category:
DELETE FROM item WHERE item.subcategoryID In (SELECT subcategory.subcategoryID FROM category INNER JOIN subcategory ON category.categoryID = subcategory.categoryID WHERE category.categoryID = @.categoryID);
DELETE FROM subcategory WHERE subcategory.category In (SELECT category.categoryID FROM category WHERE category.categoryID = @.categoryID);
DELETE FROM category WHERE category.categoryID = @.categoryID;
|||Thanks alot for the reply! Actually, a bit after I posted the question I began to do something very similar to what your 2nd option above. What I thought to do was to remove the "Delete" button on the GridView and replace it with a Select Button whose text was "Delete", and then when the selectedIndexChanged event for the GridView is called (meaning when the user clicks the select button (which says "Delete"), I used this statement to delete the "items" in that category:
DELETE FROM items WHERE items.subcategoryID IN (SELECT subcategories.subcategoryID FROM subcategories WHERE subcategories.CategoryID = " & Me.GridView1.SelectedValue
However, when I tried to run this, I got an error since there were no actual Items in the Items table who were children of the selected category to delete. So I began to write a FOR EACH statement to go through the Items table and search for any items who were nested in that category, and if there were any, then call the above command. This seems like alot of work though, and I'm really interested in these "Constraints" you spoke of.
Could you please give more info/examples of how to declare and use a constraint on a delete command such as what I want to do? If possible, I'd like to just have the constraint declare that if a category is deleted, all subcategories and items under that category are also deleted. If this can be done automatically, that would be perfect!
I've never heard of or seen these constraints in action before though, so any help or links you can give will help alot! I'm done with work for the day, so take your time and I will check them out tomorrow! Thanks!
|||For more information about constraints search the internet, keywords "SQL SERVER FOREIGN KEY CONSTRAINTS", For example:
http://technet.microsoft.com/en-us/library/ms175464.aspx
If you want more information about constraints that will also delete related records in other tables, search for "SQL SERVER CASCADING CONSTRAINTS", for example:
http://technet.microsoft.com/en-us/library/ms186973.aspx
But the error that you got when running the delete statement, is not because there where no records. If there are records they will be deleted, if not, there will be no error. I think that the SQL statement is not correct, I think you forgot to add the ")" and the end!
And when using parameters (in this case the categoryID), it's better to useparameterized Queries!
|||Ah, yeah I just forgot to type the ) in the post (it wasn't directly copied from the code). I'm not sure what the error pertained to, however I have read up on the Cascading Delete funtion and implemented it, and it works great! I didn't think to look into a solution using SQL Server instead of Visual Studio.
Thanks alot for your help!
Deleting duplicate records
I have a table with 25000 records from which to delete 600
duplicate records. for example, col2 has some values with
2 or more entries. When I execute the below script, I
keep getting errors as indicated below. What is the best
way to delete these 600 duplicates.
script file:
SELECT DISTINCT *
INTO test
FROM livetable
GROUP BY col2
HAVING COUNT(col2) > 1
DELETE livetable
WHERE col2
IN (SELECT col2
FROM test)
INSERT Documents_Convert
SELECT *
FROM test
DROP TABLE test
ERROR MESSAGE:
-- when I run the first part of the script, i get the
bellow error:
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col1' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col2' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
column 'livetable.col3' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.Hi:
I think the error is becasue the misunderstand of select..group by
statement.
If you want to use group by statement, the content you select should in
the group by or have a function tell the machine how to modify the data.
Best Wishes
Wei Ci Zhou|||I believe that you are using group by uneccessarily.
the distinct should give you a unique subset
try omitting the group by clause .
Scott J Davis
Ruprect@.satx.rr.com
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community
Website: http://www.sqlJunkies.com/newsgroups/|||Scott,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
SQL Servers Largest Community Website:
http://www.sqlJunkies.com/newsgroups/
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
select..group by
select should in
modify the data.
This might be helpful..
http://www.developerfusion.com/show/1976/
Regards
Thirumal Reddy
help
field) in (....)
And After I read the BOL, it seems that we can use in statement in one
field, do you have any suggestion?
duplicate records. for example, col2 has some values with
2 or more entries. When I execute the below script, I
keep getting errors as indicated below. What is the best
way to delete these 600 duplicates.
script file:
SELECT DISTINCT *
INTO test
FROM livetable
GROUP BY col2
HAVING COUNT(col2) > 1
DELETE livetable
WHERE col2
IN (SELECT col2
FROM test)
INSERT Documents_Convert
SELECT *
FROM test
DROP TABLE test
ERROR MESSAGE:
-- when I run the first part of the script, i get the
bellow error:
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col1' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col2' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
column 'livetable.col3' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.Hi:
I think the error is becasue the misunderstand of select..group by
statement.
If you want to use group by statement, the content you select should in
the group by or have a function tell the machine how to modify the data.
Best Wishes
Wei Ci Zhou|||I believe that you are using group by uneccessarily.
the distinct should give you a unique subset
try omitting the group by clause .
Scott J Davis
Ruprect@.satx.rr.com
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community
Website: http://www.sqlJunkies.com/newsgroups/|||Scott,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
quote:
>--Original Message--
>I believe that you are using group by uneccessarily.
>the distinct should give you a unique subset
>try omitting the group by clause .
>Scott J Davis
>Ruprect@.satx.rr.com
>--
>Posted using Wimdows.net NntpNews Component - Posted from
SQL Servers Largest Community Website:
http://www.sqlJunkies.com/newsgroups/
quote:|||Wei,
>.
>
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
quote:
>--Original Message--
>Hi:
> I think the error is becasue the misunderstand of
select..group by
quote:
>statement.
> If you want to use group by statement, the content you
select should in
quote:
>the group by or have a function tell the machine how to
modify the data.
quote:|||Hi Laila,
>Best Wishes
>Wei Ci Zhou
>
>.
>
This might be helpful..
http://www.developerfusion.com/show/1976/
Regards
Thirumal Reddy
quote:
>--Original Message--
>Wei,
>What should I do to delete these records' Can you
help
quote:|||It seems that the sample does not work on my machine when I use (field,
>with a better script?
>Thanks.
>Laila
>
>
>select..group by
you[QUOTE]
>select should in
>modify the data.
>.
>
field) in (....)
And After I read the BOL, it seems that we can use in statement in one
field, do you have any suggestion?
Deleting duplicate records
I have a table with 25000 records from which to delete 600
duplicate records. for example, col2 has some values with
2 or more entries. When I execute the below script, I
keep getting errors as indicated below. What is the best
way to delete these 600 duplicates.
script file:
SELECT DISTINCT *
INTO test
FROM livetable
GROUP BY col2
HAVING COUNT(col2) > 1
DELETE livetable
WHERE col2
IN (SELECT col2
FROM test)
INSERT Documents_Convert
SELECT *
FROM test
DROP TABLE test
ERROR MESSAGE:
-- when I run the first part of the script, i get the
bellow error:
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col1' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col2' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
column 'livetable.col3' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.Hi:
I think the error is becasue the misunderstand of select..group by
statement.
If you want to use group by statement, the content you select should in
the group by or have a function tell the machine how to modify the data.
Best Wishes
Wei Ci Zhou|||I believe that you are using group by uneccessarily.
the distinct should give you a unique subset
try omitting the group by clause .
Scott J Davis
Ruprect@.satx.rr.com
--
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community Website: http://www.sqlJunkies.com/newsgroups/|||Scott,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
>--Original Message--
>I believe that you are using group by uneccessarily.
>the distinct should give you a unique subset
>try omitting the group by clause .
>Scott J Davis
>Ruprect@.satx.rr.com
>--
>Posted using Wimdows.net NntpNews Component - Posted from
SQL Servers Largest Community Website:
http://www.sqlJunkies.com/newsgroups/
>.
>|||Wei,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
>--Original Message--
>Hi:
> I think the error is becasue the misunderstand of
select..group by
>statement.
> If you want to use group by statement, the content you
select should in
>the group by or have a function tell the machine how to
modify the data.
>Best Wishes
>Wei Ci Zhou
>
>.
>|||Hi Laila,
This might be helpful..
http://www.developerfusion.com/show/1976/
Regards
Thirumal Reddy
>--Original Message--
>Wei,
>What should I do to delete these records' Can you
help
>with a better script?
>Thanks.
>Laila
>
>>--Original Message--
>>Hi:
>> I think the error is becasue the misunderstand of
>select..group by
>>statement.
>> If you want to use group by statement, the content
you
>select should in
>>the group by or have a function tell the machine how to
>modify the data.
>>Best Wishes
>>Wei Ci Zhou
>>
>>.
>.
>|||It seems that the sample does not work on my machine when I use (field,
field) in (....)
And After I read the BOL, it seems that we can use in statement in one
field, do you have any suggestion?
duplicate records. for example, col2 has some values with
2 or more entries. When I execute the below script, I
keep getting errors as indicated below. What is the best
way to delete these 600 duplicates.
script file:
SELECT DISTINCT *
INTO test
FROM livetable
GROUP BY col2
HAVING COUNT(col2) > 1
DELETE livetable
WHERE col2
IN (SELECT col2
FROM test)
INSERT Documents_Convert
SELECT *
FROM test
DROP TABLE test
ERROR MESSAGE:
-- when I run the first part of the script, i get the
bellow error:
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col1' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col2' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
column 'livetable.col3' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.Hi:
I think the error is becasue the misunderstand of select..group by
statement.
If you want to use group by statement, the content you select should in
the group by or have a function tell the machine how to modify the data.
Best Wishes
Wei Ci Zhou|||I believe that you are using group by uneccessarily.
the distinct should give you a unique subset
try omitting the group by clause .
Scott J Davis
Ruprect@.satx.rr.com
--
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community Website: http://www.sqlJunkies.com/newsgroups/|||Scott,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
>--Original Message--
>I believe that you are using group by uneccessarily.
>the distinct should give you a unique subset
>try omitting the group by clause .
>Scott J Davis
>Ruprect@.satx.rr.com
>--
>Posted using Wimdows.net NntpNews Component - Posted from
SQL Servers Largest Community Website:
http://www.sqlJunkies.com/newsgroups/
>.
>|||Wei,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
>--Original Message--
>Hi:
> I think the error is becasue the misunderstand of
select..group by
>statement.
> If you want to use group by statement, the content you
select should in
>the group by or have a function tell the machine how to
modify the data.
>Best Wishes
>Wei Ci Zhou
>
>.
>|||Hi Laila,
This might be helpful..
http://www.developerfusion.com/show/1976/
Regards
Thirumal Reddy
>--Original Message--
>Wei,
>What should I do to delete these records' Can you
help
>with a better script?
>Thanks.
>Laila
>
>>--Original Message--
>>Hi:
>> I think the error is becasue the misunderstand of
>select..group by
>>statement.
>> If you want to use group by statement, the content
you
>select should in
>>the group by or have a function tell the machine how to
>modify the data.
>>Best Wishes
>>Wei Ci Zhou
>>
>>.
>.
>|||It seems that the sample does not work on my machine when I use (field,
field) in (....)
And After I read the BOL, it seems that we can use in statement in one
field, do you have any suggestion?
Deleting duplicate entries in nightmare-table
Hello
I was given the task to "filter away" duplicate rows in a 10 million row
table with about 30 columns :S And the table has no primary key, no
constrains, nothing, and I need to find a way to clear that mess up, in a
way... sigh
You guys must think, alright, this is easy, just group by, well well, that
would be too easy, since each row isnt really "unique", lets continue the
mess....
Lets say the table contains 5 columns, A B C D E
In the new table there shall be a CHECK(A,B,C), that combination is unique.
But in the old table there are duplicates of that, and the values of D and E
may be any value on each row. (Headache yet?) Ppl who create these kinds of
heaptables should be lined up and shot :(
Lemme post some test DLL:
CREATE TABLE #Test (
A int,
B int,
C int,
D int,
E int
)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,8)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,6)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,2,1,1,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,1,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
SELECT * FROM #Test
/*
DESIRED RESULT
A B C D E
1,1,1,9,8
1,2,1,1,4
3,2,1,1,4
*/
DROP TABLE #Test
And everything is so screwed up it doesnt "matter" which values D and E has
(or the other 20 columns) as long as they had the value it had before. And
all columns are like varchar(x) in the table, so keep that in mind :S> And everything is so screwed up it doesnt "matter" which values D and E
> has
> (or the other 20 columns) as long as they had the value it had before.
This doesn't make sense to me, which value did it have before?
Anyway, I'll try, can you do this:
SELECT A,B,C,MAX(D),MAX(E)
FROM table
GROUP BY A,B,C;
?
There is no ANY() function, so you either need to choose an aggregate, or
maybe you could use a correlated subquery with ORDER BY CHECKSUM(NEWID())
but I'm not clear that would work, nor without better requirements am I
inclined to try.
A|||Lasse Edsvik wrote:
> Hello
> I was given the task to "filter away" duplicate rows in a 10 million row
> table with about 30 columns :S And the table has no primary key, no
> constrains, nothing, and I need to find a way to clear that mess up, in a
> way... sigh
> You guys must think, alright, this is easy, just group by, well well, that
> would be too easy, since each row isnt really "unique", lets continue the
> mess....
> Lets say the table contains 5 columns, A B C D E
> In the new table there shall be a CHECK(A,B,C), that combination is unique
.
> But in the old table there are duplicates of that, and the values of D and
E
> may be any value on each row. (Headache yet?) Ppl who create these kinds o
f
> heaptables should be lined up and shot :(
> Lemme post some test DLL:
>
> CREATE TABLE #Test (
> A int,
> B int,
> C int,
> D int,
> E int
> )
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,8)
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,6)
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,2,1,1,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,1,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
>
> SELECT * FROM #Test
> /*
> DESIRED RESULT
>
> A B C D E
> 1,1,1,9,8
> 1,2,1,1,4
> 3,2,1,1,4
> */
> DROP TABLE #Test
>
> And everything is so screwed up it doesnt "matter" which values D and E ha
s
> (or the other 20 columns) as long as they had the value it had before. And
> all columns are like varchar(x) in the table, so keep that in mind :S
Always tell us what version of SQL Server you are using.
In SQL Server 2005:
WITH T (row_num) AS
(SELECT ROW_NUMBER() OVER
(PARTITION BY a,b,c ORDER BY a,b,c,d,e)
FROM #Test)
DELETE FROM T
WHERE row_num > 1;
Google for "delete duplicates" and you'll find lots of other solutions
in the archives of this group.
Test it out and make sure you have a current backup first :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
sry, forgot that :) I'm using SQL 2000
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1147360308.771189.249980@.i40g2000cwc.googlegroups.com...
> Lasse Edsvik wrote:
a
that
the
unique.
and E
of
has
And
> Always tell us what version of SQL Server you are using.
> In SQL Server 2005:
> WITH T (row_num) AS
> (SELECT ROW_NUMBER() OVER
> (PARTITION BY a,b,c ORDER BY a,b,c,d,e)
> FROM #Test)
> DELETE FROM T
> WHERE row_num > 1;
> Google for "delete duplicates" and you'll find lots of other solutions
> in the archives of this group.
> Test it out and make sure you have a current backup first :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi Aaron,
consider raw data like this:
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,6)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,8)
then the query
SELECT A,B,C,MAX(D),MAX(E)
FROM table
GROUP BY A,B,C;
will produce a row
1,1,1,9,8
which is not present in the original data. Does it make sence?|||alter table #test add f timestamp
go
select * from #test
go
select A,B,C,D,E from #test where not exists(select 1 from #test t1
where t1.a=#test.a
and t1.b=#test.b
and t1.c=#test.c
and t1.f>#test.f)
A B C D E
-- -- -- -- --
1 1 1 -1 6
1 2 1 1 4
3 2 1 3 4
(3 row(s) affected)|||> which is not present in the original data. Does it make sence?
I don't know, I don't think the requirements were specific enough to make
you right or to make me wrong. I was just offering one possible solution.
I was given the task to "filter away" duplicate rows in a 10 million row
table with about 30 columns :S And the table has no primary key, no
constrains, nothing, and I need to find a way to clear that mess up, in a
way... sigh
You guys must think, alright, this is easy, just group by, well well, that
would be too easy, since each row isnt really "unique", lets continue the
mess....
Lets say the table contains 5 columns, A B C D E
In the new table there shall be a CHECK(A,B,C), that combination is unique.
But in the old table there are duplicates of that, and the values of D and E
may be any value on each row. (Headache yet?) Ppl who create these kinds of
heaptables should be lined up and shot :(
Lemme post some test DLL:
CREATE TABLE #Test (
A int,
B int,
C int,
D int,
E int
)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,8)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,6)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,2,1,1,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,1,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
SELECT * FROM #Test
/*
DESIRED RESULT
A B C D E
1,1,1,9,8
1,2,1,1,4
3,2,1,1,4
*/
DROP TABLE #Test
And everything is so screwed up it doesnt "matter" which values D and E has
(or the other 20 columns) as long as they had the value it had before. And
all columns are like varchar(x) in the table, so keep that in mind :S> And everything is so screwed up it doesnt "matter" which values D and E
> has
> (or the other 20 columns) as long as they had the value it had before.
This doesn't make sense to me, which value did it have before?
Anyway, I'll try, can you do this:
SELECT A,B,C,MAX(D),MAX(E)
FROM table
GROUP BY A,B,C;
?
There is no ANY() function, so you either need to choose an aggregate, or
maybe you could use a correlated subquery with ORDER BY CHECKSUM(NEWID())
but I'm not clear that would work, nor without better requirements am I
inclined to try.
A|||Lasse Edsvik wrote:
> Hello
> I was given the task to "filter away" duplicate rows in a 10 million row
> table with about 30 columns :S And the table has no primary key, no
> constrains, nothing, and I need to find a way to clear that mess up, in a
> way... sigh
> You guys must think, alright, this is easy, just group by, well well, that
> would be too easy, since each row isnt really "unique", lets continue the
> mess....
> Lets say the table contains 5 columns, A B C D E
> In the new table there shall be a CHECK(A,B,C), that combination is unique
.
> But in the old table there are duplicates of that, and the values of D and
E
> may be any value on each row. (Headache yet?) Ppl who create these kinds o
f
> heaptables should be lined up and shot :(
> Lemme post some test DLL:
>
> CREATE TABLE #Test (
> A int,
> B int,
> C int,
> D int,
> E int
> )
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,8)
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,6)
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,2,1,1,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,1,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
>
> SELECT * FROM #Test
> /*
> DESIRED RESULT
>
> A B C D E
> 1,1,1,9,8
> 1,2,1,1,4
> 3,2,1,1,4
> */
> DROP TABLE #Test
>
> And everything is so screwed up it doesnt "matter" which values D and E ha
s
> (or the other 20 columns) as long as they had the value it had before. And
> all columns are like varchar(x) in the table, so keep that in mind :S
Always tell us what version of SQL Server you are using.
In SQL Server 2005:
WITH T (row_num) AS
(SELECT ROW_NUMBER() OVER
(PARTITION BY a,b,c ORDER BY a,b,c,d,e)
FROM #Test)
DELETE FROM T
WHERE row_num > 1;
Google for "delete duplicates" and you'll find lots of other solutions
in the archives of this group.
Test it out and make sure you have a current backup first :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
sry, forgot that :) I'm using SQL 2000
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1147360308.771189.249980@.i40g2000cwc.googlegroups.com...
> Lasse Edsvik wrote:
a
that
the
unique.
and E
of
has
And
> Always tell us what version of SQL Server you are using.
> In SQL Server 2005:
> WITH T (row_num) AS
> (SELECT ROW_NUMBER() OVER
> (PARTITION BY a,b,c ORDER BY a,b,c,d,e)
> FROM #Test)
> DELETE FROM T
> WHERE row_num > 1;
> Google for "delete duplicates" and you'll find lots of other solutions
> in the archives of this group.
> Test it out and make sure you have a current backup first :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi Aaron,
consider raw data like this:
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,6)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,8)
then the query
SELECT A,B,C,MAX(D),MAX(E)
FROM table
GROUP BY A,B,C;
will produce a row
1,1,1,9,8
which is not present in the original data. Does it make sence?|||alter table #test add f timestamp
go
select * from #test
go
select A,B,C,D,E from #test where not exists(select 1 from #test t1
where t1.a=#test.a
and t1.b=#test.b
and t1.c=#test.c
and t1.f>#test.f)
A B C D E
-- -- -- -- --
1 1 1 -1 6
1 2 1 1 4
3 2 1 3 4
(3 row(s) affected)|||> which is not present in the original data. Does it make sence?
I don't know, I don't think the requirements were specific enough to make
you right or to make me wrong. I was just offering one possible solution.
Tuesday, February 14, 2012
Delete older entries with duplicate names
Assume I have the following table.
id name
-- ----
1 John
2 Josh
3 Mike
4 John
5 Dana
6 Josh
7 John
I want to delete the older entries of the duplicate names. So in this instance, I want to delete id 1, 2 and 4.
Thanks ahead of time...drop table table1
go
create table table1(id int
,iname varchar(10))
go
insert table1 select 1,'John'
insert table1 select 2,'John'
insert table1 select 3,'Mike'
insert table1 select 4,'John'
insert table1 select 5,'Dana'
insert table1 select 6,'Josh'
insert table1 select 7,'John'
go
select *
--delete
from table1
where iname in (select iname from table1 group by iname having count(*)>1)
and id not in (select max(id) from table1 group by iname having count(*)>1)|||Is there not a way to do it programmatically without defining which names to re-insert? I have a few hundred rows of duplicates|||Originally posted by jiggle it
Is there not a way to do it programmatically without defining which names to re-insert? I have a few hundred rows of duplicates
What do you mean "to re-insert"? Just run last query and all older reconds for duplicates will be gone. ;)|||I found the answer
http://aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=186|||DELETE Table1
WHERE id IN
(SELECT A.id from Table1 A, Table1 B
WHERE A.name = B.name
AND A.id < B.id)
May not be as efficient but requires less typing, which is a plus in my book )
id name
-- ----
1 John
2 Josh
3 Mike
4 John
5 Dana
6 Josh
7 John
I want to delete the older entries of the duplicate names. So in this instance, I want to delete id 1, 2 and 4.
Thanks ahead of time...drop table table1
go
create table table1(id int
,iname varchar(10))
go
insert table1 select 1,'John'
insert table1 select 2,'John'
insert table1 select 3,'Mike'
insert table1 select 4,'John'
insert table1 select 5,'Dana'
insert table1 select 6,'Josh'
insert table1 select 7,'John'
go
select *
--delete
from table1
where iname in (select iname from table1 group by iname having count(*)>1)
and id not in (select max(id) from table1 group by iname having count(*)>1)|||Is there not a way to do it programmatically without defining which names to re-insert? I have a few hundred rows of duplicates|||Originally posted by jiggle it
Is there not a way to do it programmatically without defining which names to re-insert? I have a few hundred rows of duplicates
What do you mean "to re-insert"? Just run last query and all older reconds for duplicates will be gone. ;)|||I found the answer
http://aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=186|||DELETE Table1
WHERE id IN
(SELECT A.id from Table1 A, Table1 B
WHERE A.name = B.name
AND A.id < B.id)
May not be as efficient but requires less typing, which is a plus in my book )
Subscribe to:
Posts (Atom)