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

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

Deleting dupes in special cases

I need to delete all rows that match at least one of the account_id values of another row *and* that has the same email address. However, if they have the same email address and none of the account_id values then I need to keep it. I've attached a sample dataset along with the expected results.

I have this:
DELETE [acctID_emailAddress_tmp] FROM [acctID_emailAddress_tmp]
JOIN
(select emailaddress, account_id, max(contact_id_tmp) max_cid
from [acctID_emailAddress_tmp]
group by emailaddress, account_id) AS tempImportTable
ON tempImportTable.[emailaddress] = [acctID_emailAddress_tmp].[emailaddress]
WHERE [acctID_emailAddress_tmp].[contact_id_tmp] < tempImportTable.[max_cid]
AND tempImportTable.[account_id] = [acctID_emailAddress_tmp].[account_id];

but it doesn't work since it's keeping the subset of the dupe row(s).

Can someone shed some light?

TIASo why do you keep 2 instead of 1?

EDIT: You won't be able to answer that one..unless you say that because it comes "first"...is that it?|||Because it comes first :)|||OK

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(temp_id int, contact_id_tmp int, account_id int, emailAddress varchar(255))
GO

INSERT INTO myTable99(temp_id, contact_id_tmp, account_id, emailAddress)
SELECT 1, 1, 3, 'test@.acme.com' UNION ALL
SELECT 2, 1, 5, 'test@.acme.com' UNION ALL
SELECT 3, 1, 10, 'test@.acme.com' UNION ALL
SELECT 4, 2, 12, 'test@.acme.com' UNION ALL
SELECT 5, 2, 3, 'test@.acme.com' UNION ALL
SELECT 6, 2, 9, 'test@.acme.com'
GO

SELECT *
FROM ( SELECT * FROM myTable99 l
WHERE contact_id_tmp IN (
SELECT MAX(contact_id_tmp)
FROM (
SELECT contact_id_tmp, emailAddress
FROM myTable99
GROUP BY contact_id_tmp, emailAddress
HAVING COUNT(*) > 1) AS XXX)
) AS L
JOIN ( SELECT * FROM myTable99 l
WHERE contact_id_tmp NOT IN (
SELECT MAX(contact_id_tmp)
FROM (
SELECT contact_id_tmp, emailAddress
FROM myTable99
GROUP BY contact_id_tmp, emailAddress
HAVING COUNT(*) > 1) AS XXX)
) AS R
ON L.account_id = R.account_Id
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

You can convert that in to a delete...let me know if you can't|||Thanks very much. Looks like this will do the trick if I ever figure out the delete statement for it =\|||Did you cut and paste the example in to QA and run it?

Also, I would use that as SELECT to see the population BEFORE you unleash the delete...oh and make sure you back up the table BEFORE you do the DELETE|||Yes, I ran it in QA and got the expected results. I need to delete R.contact_id_tmp

Currently trying to figure out the delete statement...|||Come on...that's too easy

DELETE FROM myTable99 WHERE contact_id_tmp IN (
SELECT R.contact_id_tmp
FROM ( SELECT * FROM myTable99 l
WHERE contact_id_tmp IN (
SELECT MAX(contact_id_tmp)
FROM (
SELECT contact_id_tmp, emailAddress
FROM myTable99
GROUP BY contact_id_tmp, emailAddress
HAVING COUNT(*) > 1) AS XXX)
) AS L
JOIN ( SELECT * FROM myTable99 l
WHERE contact_id_tmp NOT IN (
SELECT MAX(contact_id_tmp)
FROM (
SELECT contact_id_tmp, emailAddress
FROM myTable99
GROUP BY contact_id_tmp, emailAddress
HAVING COUNT(*) > 1) AS XXX)
) AS R
ON L.account_id = R.account_Id
)

GO

SELECT * FROM myTable99
GO|||OK. This just got uglier... Turns out accounts will be stored as a comma separated list in one field. See the attachment for an example.

Now, is it possible to de-dupe from a comma separated list all in one field? I know I could create a new table and loop over each account-emailAddress combo, but I'd like to know if it's possible to avoid doing this (since it will be much slower) and de-dupe straight from the source table.

Thanks again|||Read this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=udf,csv,string

Then create a new table where all the values are on 1 row, then use the query in this thread|||I tried the UDF but I'm getting 0 rows back. I've attached the sample dataset (1 row) and the UDF call.|||You really should normalized your data...I feel I'm doing more harm than good...

EDIT: This is pure theft from this Article (http://www.sqlteam.com/item.asp?ItemID=2652) written by A most Valuable Yak (http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1176) , Rob Volk (http://weblogs.sqlteam.com/robv/)

USE Northwind
GO

SET NOCOUNT ON
SET NOCOUNT ON
CREATE TABLE myTable99(temp_id int IDENTITY(1,1), contact_id_tmp int, account_id int, emailAddress varchar(255))
CREATE TABLE myTable00(contact_id int, accounts varchar(200), emailAddress varchar(255))
CREATE TABLE Tally(ID int)
GO

INSERT INTO myTable00(contact_id, accounts, emailAddress)
SELECT 1, '1,2,3,10,15', 'test@.acme.com' UNION ALL
SELECT 2, '5,10,20,30', 'test@.acme.com' UNION ALL
SELECT 3, '88,42', 'test@.acme.com' UNION ALL
SELECT 4, '1,2,3', 'test2@.acme.com'
GO

DECLARE @.x int
SELECT @.x = 1
WHILE @.x < 100
BEGIN
INSERT INTO Tally(ID) SELECT @.x
SELECT @.x = @.x + 1
END
GO

INSERT INTO myTable99(contact_id_tmp, Account_Id, emailAddress)
SELECT contact_id AS contact_id_tmp,
NullIf(SubString(',' + accounts + ',' , ID , CharIndex(',' , ',' + accounts + ',' , ID) - ID) , '') AS Account_Id,
emailAddress
FROM Tally, myTable00
WHERE ID <= Len(',' + accounts + ',') AND SubString(',' + accounts + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + accounts + ',' , ID) - ID > 0 --remove this line to keep NULL rows

SELECT * FROM myTable99
GO

-- Now just add the other SQL

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
DROP TABLE Tally
GO|||This is exactly what I needed, but...users have decided to go back and separate each account in individual rows!!

Thanks a lot for your help. I guess I'll check out sqlteam.com from now on before posting here. :)|||Even if they do...the data is still not normalized if you need an operation like this...anyway, it was a neat exercise...|||This is just 1/3 of the entire process. The data is normalized in its final state.

Deleting data with least impact

I have a large database - 33 million rows - VERY WIDE table and am
running out of disk space - lovely ...
I have to move 7 million records off the table - no biggie - I'll just
dts the records to another server.
My problem is deleting the remaining data to make room on the server
without impacting/logging too much.
Any help would be appreciated.
Thanks,
CraigOn Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> I have a large database - 33 million rows - VERY WIDE table and am
> running out of disk space - lovely ...
> I have to move 7 million records off the table - no biggie - I'll just
> dts the records to another server.
> My problem is deleting the remaining data to make room on the server
> without impacting/logging too much.
> Any help would be appreciated.
> Thanks,
> Craig
truncate|||On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> I have a large database - 33 million rows - VERY WIDE table and am
> running out of disk space - lovely ...
> I have to move 7 million records off the table - no biggie - I'll just
> dts the records to another server.
> My problem is deleting the remaining data to make room on the server
> without impacting/logging too much.
> Any help would be appreciated.
> Thanks,
> Craig
you can truncate the table one the data is moved out.|||On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> > I have a large database - 33 million rows - VERY WIDE table and am
> > running out of disk space - lovely ...
> > I have to move 7 million records off the table - no biggie - I'll just
> > dts the records to another server.
> > My problem is deleting the remaining data to make room on the server
> > without impacting/logging too much.
> > Any help would be appreciated.
> > Thanks,
> > Craig
> you can truncate the table one the data is moved out.
I will only need to delete 7 m rows of the 33 m rows ... so I need a
good way to do that.
Craig|||On Sep 12, 11:27 am, Craig <csomb...@.gmail.com> wrote:
> On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>
>
> > On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> > > I have a large database - 33 million rows - VERY WIDE table and am
> > > running out of disk space - lovely ...
> > > I have to move 7 million records off the table - no biggie - I'll just
> > > dts the records to another server.
> > > My problem is deleting the remaining data to make room on the server
> > > without impacting/logging too much.
> > > Any help would be appreciated.
> > > Thanks,
> > > Craig
> > you can truncate the table one the data is moved out.
> I will only need to delete 7 m rows of the 33 m rows ... so I need a
> good way to do that.
> Craig- Hide quoted text -
> - Show quoted text -
I need to look at the delete statement. How long it takes now?|||Do not forget changing your Recovery Model temporarily to "Simple Recovery
Model". And before doing this, backup your Transaction Log, otherwise log
chain will be broken and you will not be able to restore your database to
the point where you changed your recovery model if you encounter a problem.
After your deleting operation, change your Recovery Model back to FULL.
(After changing your recovery model to FULL, back up your transaction log
again to prevent breaking the log chain)
The aim of changing your recovery model is not to log all this 7million
deletion to the transaction log file and blow it up. You change your
recovery model to prevent this. Because in this operation (as you already
lack of free space on your disks) if you keep FULL recovery model, then it
would log all this deletion operation and it probably raise an "out of
space" error and halt the process of deletion or whatever.
Here's a link that you can obtain more info abour Recovery Models from:
http://msdn2.microsoft.com/en-us/library/ms366344.aspx
--
Ekrem Önsoy
"Craig" <csomberg@.gmail.com> wrote in message
news:1189574876.579528.58280@.r29g2000hsg.googlegroups.com...
> On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
>> > I have a large database - 33 million rows - VERY WIDE table and am
>> > running out of disk space - lovely ...
>> > I have to move 7 million records off the table - no biggie - I'll just
>> > dts the records to another server.
>> > My problem is deleting the remaining data to make room on the server
>> > without impacting/logging too much.
>> > Any help would be appreciated.
>> > Thanks,
>> > Craig
>> you can truncate the table one the data is moved out.
>
> I will only need to delete 7 m rows of the 33 m rows ... so I need a
> good way to do that.
> Craig
>|||On Tue, 11 Sep 2007 22:27:56 -0700, Craig wrote:
>On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
>> > I have a large database - 33 million rows - VERY WIDE table and am
>> > running out of disk space - lovely ...
>> > I have to move 7 million records off the table - no biggie - I'll just
>> > dts the records to another server.
>> > My problem is deleting the remaining data to make room on the server
>> > without impacting/logging too much.
>> > Any help would be appreciated.
>> > Thanks,
>> > Craig
>> you can truncate the table one the data is moved out.
>
>I will only need to delete 7 m rows of the 33 m rows ... so I need a
>good way to do that.
>Craig
Hi Craig,
A common technique is to split the delete in batches, like this:
DECLARE @.rc int;
SET @.rc = 1;
WHILE @.rc <> 0
BEGIN;
DELETE TOP(100000)
FROM YourTable
WHERE whatever has to be deleted;
SET @.rc = @.@.ROWCOUNT;
END;
In SQL Server 2000, DELETE TOP(..) is not supported - instead, use SET
ROWCOUNT 100000 in the beginning and SET ROWCOUNT 0 at the end of the
script. Also, for all versions, you might need to experiment to find the
ideal batch size.
IIf your recovery model is full, either switch temporarily to simple (as
sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||> IIf your recovery model is full, either switch temporarily to simple (as
> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
Now, above doesn't jive in a SQL Server forum. I think you meant:
CASE WHEN recovery model is full THEN switch temporarily to simple ...
(Sorry, I couldn't resist. Just playing a bad joke with Hugo, doesn't have anything to do with your
problem, Craig...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:60tge3pj8h8jk3d3kohjahp2gh6s345595@.4ax.com...
> On Tue, 11 Sep 2007 22:27:56 -0700, Craig wrote:
>>On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
>> > I have a large database - 33 million rows - VERY WIDE table and am
>> > running out of disk space - lovely ...
>> > I have to move 7 million records off the table - no biggie - I'll just
>> > dts the records to another server.
>> > My problem is deleting the remaining data to make room on the server
>> > without impacting/logging too much.
>> > Any help would be appreciated.
>> > Thanks,
>> > Craig
>> you can truncate the table one the data is moved out.
>>
>>I will only need to delete 7 m rows of the 33 m rows ... so I need a
>>good way to do that.
>>Craig
> Hi Craig,
> A common technique is to split the delete in batches, like this:
> DECLARE @.rc int;
> SET @.rc = 1;
> WHILE @.rc <> 0
> BEGIN;
> DELETE TOP(100000)
> FROM YourTable
> WHERE whatever has to be deleted;
> SET @.rc = @.@.ROWCOUNT;
> END;
> In SQL Server 2000, DELETE TOP(..) is not supported - instead, use SET
> ROWCOUNT 100000 in the beginning and SET ROWCOUNT 0 at the end of the
> script. Also, for all versions, you might need to experiment to find the
> ideal batch size.
> IIf your recovery model is full, either switch temporarily to simple (as
> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Thu, 13 Sep 2007 09:52:56 +0200, Tibor Karaszi wrote:
>> IIf your recovery model is full, either switch temporarily to simple (as
>> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
>Now, above doesn't jive in a SQL Server forum. I think you meant:
>CASE WHEN recovery model is full THEN switch temporarily to simple ...
Hey, Tibor,
You are not actually using CASE as a *statement*, now are you? Off you
go to the nearest C++ newsgroup!!
>(Sorry, I couldn't resist. Just playing a bad joke with Hugo, doesn't have anything to do with your
>problem, Craig...)
And neither could I ... :-)
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||> You are not actually using CASE as a *statement*, now are you? Off you
> go to the nearest C++ newsgroup!!
I guess my wishes for a closer integration with ANSI SQL PSM syntax influences me... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:rhije3lgrsc2476uhtfig6ue17iiv3b1s8@.4ax.com...
> On Thu, 13 Sep 2007 09:52:56 +0200, Tibor Karaszi wrote:
>> IIf your recovery model is full, either switch temporarily to simple (as
>> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
>>Now, above doesn't jive in a SQL Server forum. I think you meant:
>>CASE WHEN recovery model is full THEN switch temporarily to simple ...
> Hey, Tibor,
> You are not actually using CASE as a *statement*, now are you? Off you
> go to the nearest C++ newsgroup!!
>>(Sorry, I couldn't resist. Just playing a bad joke with Hugo, doesn't have anything to do with
>>your
>>problem, Craig...)
> And neither could I ... :-)
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Deletes From Multiple Tables.

Hello everybody,

We have a Master table in the Db who's PK is referenced in at least 60 tables as FK.
We want to delete all the dependent records in 1 go without using multiple delete statements or any cursor or loop.

Is it possible? Please advice.

Thanks in advance.

Regards,

Ashishuse "Cascade Delete"...|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.

Even wrtting a trigger to delete from all the tables will mean writing some kind of loop or Cursor if we use the system tables.
I can use sys tables but the delete qury using sys tbales needs to execute in 1 go. No cursors or temp tables should be used.

Is there any other way?|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.
Frickin' morons.
My advice is to go work for a company that isn't managed by idiots.|||Now there's the blind dude we all know and love|||No cascading deletes allowed.
No temp tables allowed.
No cursors allowed. (Well, at least they are on the right track with that one...)

Absolute amateurs.

What advice would you give them?|||What advice would you give them?

Well, it realy sounds like a request for homework...but

DECLARE @.FKey sysname, @.keyvalue varchar(8000), @.sql varchar(8000)
SELECT @.FKey = 'EmployeeID', @.keyValue = '1', @.sql = ''

SELECT @.sql = @.sql + 'DELETE FROM ['+o.name+'] WHERE '+@.FKey+'='+@.KeyValue+' GO '
FROM sysreferences r
LEFT JOIN sysobjects o
ON r.fkeyid = o.id
WHERE rkeyid = OBJECT_ID('Employees')

SELECT @.sql

--EXEC(@.sql)|||Thanks dear friends.

Brett, it's not a homework at all.

In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.

Me being the modest developer cannot argue with the people who have set these guidelines. Please forgive those poor souls for their ignorance.

Thanks once again. :)|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things
Using cascading deletes is the best solution for this problem (and is supported by all major DBMS).
If the "world largest" project does not apply the most effectice solutions to such a basic problem, then I'm pretty sure it is going to fail. This is extremely short-sighted and will create more maintenance trouble then they probably are willing to pay for. Why not use flat files, if they don't want to use the features of a database?
(nothing personal against you wash, it's just that I see this kind of stupid decisions all around...)|||Man, i don't mind it as long as they are paying me for the job. [:D]

I do not exactly know what has prompted the TAs to avoid cascade deletes.|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.

reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.|||reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.

Forgive me but i don't work on any such project.|||I have had everyone in my team at my company, including the so called SQL experts working with me, brainstorm on this issue.

It seems without a recursive loop , we cannot achieve this.

Can you give me any idea if we can achieve this using a simple loop.

I want to delete all the dependent records to the Nth level.

Thanks in advance.|||HAve one of your "sql experts" get in on this thread...and did you even look at my code?|||What in the world would drive such requirements? It makes as much sense as "We'd like to retrieve all the data from the database but I can't reference any table names or use the word 'select'".

I'm also guessing that you're going to generate a heap of log activity with this puppy, so you might want to think about doing a set of tables at a time or a set of primary key values, unless you own unlimited disk.|||Thanks Brett. i did use your code but it useful only for the first level of dependencies.

Thankfully people have realized what kind of issues have cropped up due to such requirement and thankfully we are working on some realistic solution.

I would like to thank everyone for chipping in with helpful suggestions and valuable advice.

thanks once again.