DTS to .net: record

Showing posts with label record. Show all posts

Tuesday, March 27, 2012

Duplicate records

I have duplicate records where the duplicate data spans multiple
columns. I'm not sure how to delete the record I don't.
I have a table where the duplicate row is the opposite of the original:
Column 1 Column 2 Column 3 Column 4 Column 5
(Original) Data 1 Data 2 Data 3 Data 4 Data 5
(Duplicate) Data 5 Data 4 Data 3 Data 2 Data 1
(Original) Data 1 Data 2 Data 6 Data 7 Data 8
(Duplicate) Data 8 Data 7 Data 6 Data 2 Data 1
I don't need the duplicate record.
Thanks in advance,
SteveWithout asking how on earth s situation like this came about, the way to
delete the dupes is to delete using a self-join, to find, for each record
with a dupe, the duplicate which needs t obe deleted
Delete D
From Table D Join Table T
On D.Column1 = T.Column5
And D.Column2 = T.Column4
And D.Column3 = T.Column3
"stevem12@.hotmail.com" wrote:

> I have duplicate records where the duplicate data spans multiple
> columns. I'm not sure how to delete the record I don't.
>
> I have a table where the duplicate row is the opposite of the original:
>
> Column 1 Column 2 Column 3 Column 4 Column 5
> (Original) Data 1 Data 2 Data 3 Data 4 Data 5
> (Duplicate) Data 5 Data 4 Data 3 Data 2 Data 1
> (Original) Data 1 Data 2 Data 6 Data 7 Data 8
> (Duplicate) Data 8 Data 7 Data 6 Data 2 Data 1
>
> I don't need the duplicate record.
>
> Thanks in advance,
> Steve
>|||will this work?
select distinct
column1 as youruniquevaluefield
from yourtable
union
select distinct
column2 as youruniquevaluefield
from yourtable
union
select distinct
column3 as youruniquevaluefield
from yourtable
union
etc.... you get my point?
stevem12@.hotmail.com wrote:
> I have duplicate records where the duplicate data spans multiple
> columns. I'm not sure how to delete the record I don't.
>
> I have a table where the duplicate row is the opposite of the original:
>
> Column 1 Column 2 Column 3 Column 4 Column 5
> (Original) Data 1 Data 2 Data 3 Data 4 Data 5
> (Duplicate) Data 5 Data 4 Data 3 Data 2 Data 1
> (Original) Data 1 Data 2 Data 6 Data 7 Data 8
> (Duplicate) Data 8 Data 7 Data 6 Data 2 Data 1
>
> I don't need the duplicate record.
>
> Thanks in advance,
> Steve
>|||Unfortunately this did not work. It deletes all records.
CBretana wrote:
> Without asking how on earth s situation like this came about, the
way to
> delete the dupes is to delete using a self-join, to find, for each
record
> with a dupe, the duplicate which needs t obe deleted
>
> Delete D
> From Table D Join Table T
> On D.Column1 = T.Column5
> And D.Column2 = T.Column4
> And D.Column3 = T.Column3
> "stevem12@.hotmail.com" wrote:
>
original:
Column 5
5
1
8
1|||On 22 Apr 2005 19:54:11 -0700, stevem12@.hotmail.com wrote:

>I have duplicate records where the duplicate data spans multiple
>columns. I'm not sure how to delete the record I don't.
Hi Steve,
I don't understand this part. Please post table structure and sample
data (as CREATE TABLE and INSERT statements) and required output (see
www.aspfaq.com/5006).

>I have a table where the duplicate row is the opposite of the original:
>
> Column 1 Column 2 Column 3 Column 4 Column 5
>(Original) Data 1 Data 2 Data 3 Data 4 Data 5
>(Duplicate) Data 5 Data 4 Data 3 Data 2 Data 1
>(Original) Data 1 Data 2 Data 6 Data 7 Data 8
>(Duplicate) Data 8 Data 7 Data 6 Data 2 Data 1
>
>I don't need the duplicate record.
Try if this works:
DELETE FROM MyTable
WHERE EXISTS
(SELECT *
FROM MyTable AS d
-- Recognise duplicates
WHERE d.Column1 = MyTable.Column5
AND d.Column2 = MyTable.Column4
AND d.Column3 = MyTable.Column3
AND d.Column4 = MyTable.Column2
AND d.Column5 = MyTable.Column1
-- Delete only "lowest" (when ordered by Column1, Column2, ...)
AND ( d.Column1 > MyTable.Column1
OR (d.Column1 = MyTable.Column1 AND d.Column2 > MyTable.Column2)))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Take a bow Hugo! That did it!
Thank you very much.
Steve|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. My guess is that you meant sometime like this:
CREATE TABLE Foobar
(c1 INTEGER NOT NULL,
c2 INTEGER NOT NULL,
c3 INTEGER NOT NULL,
c4 INTEGER NOT NULL,
c5 INTEGER NOT NULL,
PRIMARY KEY (c1,c2,c3,c4,c5));
I am not sure how you define a duplicate versus an original. Are the
columns ordered in some way like (c1 < c2 < c3< c4 < c5)? Or will
either do? What about (1,1,1,1,1) and its mirror duplicate
(1,1,1,1,1), which blows up Hugo's solution?
If you do not have to preserve the column ordering, I would sort each
row then get rid of the duplicates with a SELECT DISTINCT.

Monday, March 26, 2012

Duplicate record trigger

-- duplicate record check

SET @.step = 'Duplicate record'

IF EXISTS (

SELECTi.myID, i.Type

FROMINSERTED i INNER JOIN

myDB.dbo.myTable c ON i.myID = c.myID

GROUP BY i.myID, i.Type

HAVING(COUNT(*) > 1) AND (i.Type = 'In')

)

BEGIN

ROLLBACK transaction

RAISERROR('Error: step: %s.rollback is done.', 16, 1, @.step)

Return

END

What is problem?

Moving to the T-SQL forum.|||
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
ENDsql

Duplicate record trigger

-- duplicate record check

SET @.step = 'Duplicate record'

IF EXISTS (

SELECT i.myID, i.Type

FROM INSERTED i INNER JOIN

myDB.dbo.myTable c ON i.myID = c.myID

GROUP BY i.myID, i.Type

HAVING (COUNT(*) > 1) AND (i.Type = 'In')

)

BEGIN

ROLLBACK transaction

RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)

Return

END

What is problem?

You may not use the SET statement and I also did not see an aggregate function with your GROUP BY which means it is not a GROUP BY but a standard DISTINCT. The link below covers what you need to know and a sample of what you can do in SQL Server. Hope this helps.

http://www.sommarskog.se/error-handling-I.html

Duplicate record trigger

This is part of my trigger on table T1. I am trying to check if the records
inserted to T1 is available in myDB.dbo.myTable or not (destination table).
If it is available rollback T1. It does not do that although I insert the
same records twice.
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT i.myID, i.Type
FROM INSERTED i INNER JOIN
myDB.dbo.myTable c ON i.myID = c.myID
GROUP BY i.myID, i.Type
HAVING (COUNT(*) > 1) AND (i.Type = 'In')
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
Return
END
What is problem?JIM. H. wrote:
> This is part of my trigger on table T1. I am trying to check if the records
> inserted to T1 is available in myDB.dbo.myTable or not (destination table).
> If it is available rollback T1. It does not do that although I insert the
> same records twice.
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.Type
> FROM INSERTED i INNER JOIN
> myDB.dbo.myTable c ON i.myID = c.myID
> GROUP BY i.myID, i.Type
> HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> Return
> END
> What is problem?
Why not just add a unique constraint to the table instead?
--
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
--|||Jim,
It seems like if your goal is to prohibit the addition of rows having
duplicate values for columns MyID and [Type], then a UNIQUE constraint would
have less performance impact than a trigger.
Preventing the duplicate row from being added to the table is far better
than trying to determine if a duplicate row HAS been added, and then
removing it after the fact.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:876403CD-C4A3-4FB9-B278-CD792B39D1B2@.microsoft.com...
> This is part of my trigger on table T1. I am trying to check if the
> records
> inserted to T1 is available in myDB.dbo.myTable or not (destination
> table).
> If it is available rollback T1. It does not do that although I insert the
> same records twice.
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.Type
> FROM INSERTED i INNER JOIN
> myDB.dbo.myTable c ON i.myID = c.myID
> GROUP BY i.myID, i.Type
> HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> Return
> END
> What is problem?
>|||Hi Jim
I asked in your previous thread that you posted example DDL and data along
with statements you expected to work and those that you don't want to work.
Without doing so you have got the same replies as before a UNIQUE constraint
would be the best solution. For example:
CREATE DATABASE TESTDB
GO
USE TESTDB
GO
CREATE TABLE myTable ( MyId int NOT NULL,
myType char(2) NOT NULL,
CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
INSERT INTO myTable ( MyId, myType )
VALUES( 2, 'In' )
INSERT INTO myTable ( MyId, myType )
VALUES( 2, 'Ot' )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
duplicate key in object 'dbo.myTable'.
The statement has been terminated.
*/
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
duplicate key in object 'dbo.myTable'.
The statement has been terminated.
*/
/* If this the above statement should have been allowed then you can not use
a unique constraint, but can make the exception in the trigger */
ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
GO
CREATE TRIGGER trg_myTable ON myTable
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT i.myID, i.myType
FROM INSERTED i
JOIN dbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myType
= 'In'
GROUP BY i.myID, i.myType
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
Return
END
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
/*
Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
Error: step: Duplicate record. rollback is done.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
/* But this still works */
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
UPDATE myTable
SET myType = 'In'
WHERE MyId = 2
AND myType = 'Ot'
/*
Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
Error: step: Duplicate record. rollback is done.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
John
"JIM.H." wrote:
> This is part of my trigger on table T1. I am trying to check if the records
> inserted to T1 is available in myDB.dbo.myTable or not (destination table).
> If it is available rollback T1. It does not do that although I insert the
> same records twice.
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.Type
> FROM INSERTED i INNER JOIN
> myDB.dbo.myTable c ON i.myID = c.myID
> GROUP BY i.myID, i.Type
> HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> Return
> END
> What is problem?
>|||Hi John,
Thank you very much for your help. Here is a little bit more detail. The
trigger is on T1. T1 is inserted records by an external system. T1.Type can
be â'Inâ', or â'NotInâ' If it is In I need to insert this to a remote database
too which is myDB.dbo.myTable. There is no myTable.Type field in remote
database.
1. So if T1.myID=1 and T1.Type=â'Inâ' comes more than one and myID=1 is
already available in remote database, return error to the external system and
do not accept insert into T1.
2. If T1.myID=1 and T1.Type=â'NotInâ' can come more than one continue without
error.
How should I do this?
So in the example you gave
INSERT INTO T1 ( MyId, Type )
VALUES( 1, 'In' )
This should not let another (1,â'Inâ') inserted.
INSERT INTO T1 ( MyId, Type )
VALUES( 1, 'Ot' )
This can be many times, there should not be any restriction on this, because
of this I could not use unique constraints on T1.
It seem your trg_myTable example is the same as my example (am I missing
anything) but it does not seem that is working, I am able insert (1,â'Inâ')
more than one.
Any idea would be greatly appreciated.
"John Bell" wrote:
> Hi Jim
> I asked in your previous thread that you posted example DDL and data along
> with statements you expected to work and those that you don't want to work.
> Without doing so you have got the same replies as before a UNIQUE constraint
> would be the best solution. For example:
> CREATE DATABASE TESTDB
> GO
> USE TESTDB
> GO
>
> CREATE TABLE myTable ( MyId int NOT NULL,
> myType char(2) NOT NULL,
> CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 2, 'In' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 2, 'Ot' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> /*
> Msg 2627, Level 14, State 1, Line 1
> Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> duplicate key in object 'dbo.myTable'.
> The statement has been terminated.
> */
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> /*
> Msg 2627, Level 14, State 1, Line 1
> Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> duplicate key in object 'dbo.myTable'.
> The statement has been terminated.
> */
> /* If this the above statement should have been allowed then you can not use
> a unique constraint, but can make the exception in the trigger */
> ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
> GO
> CREATE TRIGGER trg_myTable ON myTable
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.myType
> FROM INSERTED i
> JOIN dbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myType
> = 'In'
> GROUP BY i.myID, i.myType
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> Return
> END
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> /*
> Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> Error: step: Duplicate record. rollback is done.
> Msg 3609, Level 16, State 1, Line 1
> The transaction ended in the trigger. The batch has been aborted.
> */
> /* But this still works */
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> UPDATE myTable
> SET myType = 'In'
> WHERE MyId = 2
> AND myType = 'Ot'
> /*
> Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> Error: step: Duplicate record. rollback is done.
> Msg 3609, Level 16, State 1, Line 1
> The transaction ended in the trigger. The batch has been aborted.
> */
>
> John
> "JIM.H." wrote:
> > This is part of my trigger on table T1. I am trying to check if the records
> > inserted to T1 is available in myDB.dbo.myTable or not (destination table).
> > If it is available rollback T1. It does not do that although I insert the
> > same records twice.
> >
> > -- duplicate record check
> > SET @.step = 'Duplicate record'
> > IF EXISTS (
> > SELECT i.myID, i.Type
> > FROM INSERTED i INNER JOIN
> > myDB.dbo.myTable c ON i.myID = c.myID
> > GROUP BY i.myID, i.Type
> > HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> > )
> > BEGIN
> > ROLLBACK transaction
> > RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> > Return
> > END
> >
> > What is problem?
> >
> >|||Hi Jim
The trigger is not the same as your code. As T1 it would be
CREATE TRIGGER trg_T1 ON T1
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT *
FROM inserted i
JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[Type] = 'In'
GROUP BY i.myID, i.[Type]
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
RETURN
END
This would allow duplicates for any value of MyId and a Type of 'NotIn', but
only one occurance of a value of Type of 'In' with any single value of MyId.
If the table in your remote database can only be populated by this procedure
all you need to do is
insert the values into that database through the trigger
CREATE TRIGGER trg_T1 ON T1
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT *
FROM inserted i
JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[Type] = 'In'
GROUP BY i.myID, i.[Type]
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
ELSE
BEGIN
INSERT INTO myDB.dbo.myTable ( myId )
SELECT MyId
FROM inserted i
LEFT JOIN myDB.dbo.myTable c ON i.myID =c.myID
WHERE c.MyId IS NULL
AND i.[Type] = 'In'
END
RETURN
END
If records can be inserted into the remote database independent of this
process then would not be sufficient.
John
"JIM.H." wrote:
> Hi John,
> Thank you very much for your help. Here is a little bit more detail. The
> trigger is on T1. T1 is inserted records by an external system. T1.Type can
> be â'Inâ', or â'NotInâ' If it is In I need to insert this to a remote database
> too which is myDB.dbo.myTable. There is no myTable.Type field in remote
> database.
> 1. So if T1.myID=1 and T1.Type=â'Inâ' comes more than one and myID=1 is
> already available in remote database, return error to the external system and
> do not accept insert into T1.
> 2. If T1.myID=1 and T1.Type=â'NotInâ' can come more than one continue without
> error.
> How should I do this?
> So in the example you gave
> INSERT INTO T1 ( MyId, Type )
> VALUES( 1, 'In' )
> This should not let another (1,â'Inâ') inserted.
> INSERT INTO T1 ( MyId, Type )
> VALUES( 1, 'Ot' )
> This can be many times, there should not be any restriction on this, because
> of this I could not use unique constraints on T1.
> It seem your trg_myTable example is the same as my example (am I missing
> anything) but it does not seem that is working, I am able insert (1,â'Inâ')
> more than one.
> Any idea would be greatly appreciated.
>
> "John Bell" wrote:
> > Hi Jim
> >
> > I asked in your previous thread that you posted example DDL and data along
> > with statements you expected to work and those that you don't want to work.
> > Without doing so you have got the same replies as before a UNIQUE constraint
> > would be the best solution. For example:
> >
> > CREATE DATABASE TESTDB
> > GO
> >
> > USE TESTDB
> > GO
> >
> >
> > CREATE TABLE myTable ( MyId int NOT NULL,
> > myType char(2) NOT NULL,
> > CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 1, 'In' )
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 2, 'In' )
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 2, 'Ot' )
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 1, 'Ot' )
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 1, 'In' )
> >
> > /*
> > Msg 2627, Level 14, State 1, Line 1
> > Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> > duplicate key in object 'dbo.myTable'.
> > The statement has been terminated.
> > */
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 1, 'Ot' )
> > /*
> > Msg 2627, Level 14, State 1, Line 1
> > Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> > duplicate key in object 'dbo.myTable'.
> > The statement has been terminated.
> > */
> >
> > /* If this the above statement should have been allowed then you can not use
> > a unique constraint, but can make the exception in the trigger */
> > ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
> > GO
> >
> > CREATE TRIGGER trg_myTable ON myTable
> > FOR INSERT,UPDATE
> > AS
> > BEGIN
> > DECLARE @.step varchar(30)
> > -- duplicate record check
> > SET @.step = 'Duplicate record'
> > IF EXISTS (
> > SELECT i.myID, i.myType
> > FROM INSERTED i
> > JOIN dbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myType
> > = 'In'
> > GROUP BY i.myID, i.myType
> > HAVING COUNT(*) > 1
> > )
> > BEGIN
> > ROLLBACK transaction
> > RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> > END
> > Return
> > END
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 1, 'In' )
> >
> > /*
> > Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> > Error: step: Duplicate record. rollback is done.
> > Msg 3609, Level 16, State 1, Line 1
> > The transaction ended in the trigger. The batch has been aborted.
> > */
> >
> > /* But this still works */
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 1, 'Ot' )
> >
> > UPDATE myTable
> > SET myType = 'In'
> > WHERE MyId = 2
> > AND myType = 'Ot'
> >
> > /*
> > Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> > Error: step: Duplicate record. rollback is done.
> > Msg 3609, Level 16, State 1, Line 1
> > The transaction ended in the trigger. The batch has been aborted.
> > */
> >
> >
> > John
> >
> > "JIM.H." wrote:
> >
> > > This is part of my trigger on table T1. I am trying to check if the records
> > > inserted to T1 is available in myDB.dbo.myTable or not (destination table).
> > > If it is available rollback T1. It does not do that although I insert the
> > > same records twice.
> > >
> > > -- duplicate record check
> > > SET @.step = 'Duplicate record'
> > > IF EXISTS (
> > > SELECT i.myID, i.Type
> > > FROM INSERTED i INNER JOIN
> > > myDB.dbo.myTable c ON i.myID = c.myID
> > > GROUP BY i.myID, i.Type
> > > HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> > > )
> > > BEGIN
> > > ROLLBACK transaction
> > > RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> > > Return
> > > END
> > >
> > > What is problem?
> > >
> > >|||Thanks John. I will try to implement this.
"John Bell" wrote:
> Hi Jim
> The trigger is not the same as your code. As T1 it would be
> CREATE TRIGGER trg_T1 ON T1
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT *
> FROM inserted i
> JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[Type] = 'In'
> GROUP BY i.myID, i.[Type]
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> RETURN
> END
> This would allow duplicates for any value of MyId and a Type of 'NotIn', but
> only one occurance of a value of Type of 'In' with any single value of MyId.
> If the table in your remote database can only be populated by this procedure
> all you need to do is
> insert the values into that database through the trigger
> CREATE TRIGGER trg_T1 ON T1
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT *
> FROM inserted i
> JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[Type] = 'In'
> GROUP BY i.myID, i.[Type]
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> ELSE
> BEGIN
> INSERT INTO myDB.dbo.myTable ( myId )
> SELECT MyId
> FROM inserted i
> LEFT JOIN myDB.dbo.myTable c ON i.myID => c.myID
> WHERE c.MyId IS NULL
> AND i.[Type] = 'In'
> END
> RETURN
> END
> If records can be inserted into the remote database independent of this
> process then would not be sufficient.
> John
>
> "JIM.H." wrote:
> > Hi John,
> > Thank you very much for your help. Here is a little bit more detail. The
> > trigger is on T1. T1 is inserted records by an external system. T1.Type can
> > be â'Inâ', or â'NotInâ' If it is In I need to insert this to a remote database
> > too which is myDB.dbo.myTable. There is no myTable.Type field in remote
> > database.
> > 1. So if T1.myID=1 and T1.Type=â'Inâ' comes more than one and myID=1 is
> > already available in remote database, return error to the external system and
> > do not accept insert into T1.
> > 2. If T1.myID=1 and T1.Type=â'NotInâ' can come more than one continue without
> > error.
> > How should I do this?
> >
> > So in the example you gave
> > INSERT INTO T1 ( MyId, Type )
> > VALUES( 1, 'In' )
> > This should not let another (1,â'Inâ') inserted.
> >
> > INSERT INTO T1 ( MyId, Type )
> > VALUES( 1, 'Ot' )
> > This can be many times, there should not be any restriction on this, because
> > of this I could not use unique constraints on T1.
> >
> > It seem your trg_myTable example is the same as my example (am I missing
> > anything) but it does not seem that is working, I am able insert (1,â'Inâ')
> > more than one.
> > Any idea would be greatly appreciated.
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Jim
> > >
> > > I asked in your previous thread that you posted example DDL and data along
> > > with statements you expected to work and those that you don't want to work.
> > > Without doing so you have got the same replies as before a UNIQUE constraint
> > > would be the best solution. For example:
> > >
> > > CREATE DATABASE TESTDB
> > > GO
> > >
> > > USE TESTDB
> > > GO
> > >
> > >
> > > CREATE TABLE myTable ( MyId int NOT NULL,
> > > myType char(2) NOT NULL,
> > > CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 1, 'In' )
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 2, 'In' )
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 2, 'Ot' )
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 1, 'Ot' )
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 1, 'In' )
> > >
> > > /*
> > > Msg 2627, Level 14, State 1, Line 1
> > > Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> > > duplicate key in object 'dbo.myTable'.
> > > The statement has been terminated.
> > > */
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 1, 'Ot' )
> > > /*
> > > Msg 2627, Level 14, State 1, Line 1
> > > Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> > > duplicate key in object 'dbo.myTable'.
> > > The statement has been terminated.
> > > */
> > >
> > > /* If this the above statement should have been allowed then you can not use
> > > a unique constraint, but can make the exception in the trigger */
> > > ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
> > > GO
> > >
> > > CREATE TRIGGER trg_myTable ON myTable
> > > FOR INSERT,UPDATE
> > > AS
> > > BEGIN
> > > DECLARE @.step varchar(30)
> > > -- duplicate record check
> > > SET @.step = 'Duplicate record'
> > > IF EXISTS (
> > > SELECT i.myID, i.myType
> > > FROM INSERTED i
> > > JOIN dbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myType
> > > = 'In'
> > > GROUP BY i.myID, i.myType
> > > HAVING COUNT(*) > 1
> > > )
> > > BEGIN
> > > ROLLBACK transaction
> > > RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> > > END
> > > Return
> > > END
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 1, 'In' )
> > >
> > > /*
> > > Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> > > Error: step: Duplicate record. rollback is done.
> > > Msg 3609, Level 16, State 1, Line 1
> > > The transaction ended in the trigger. The batch has been aborted.
> > > */
> > >
> > > /* But this still works */
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 1, 'Ot' )
> > >
> > > UPDATE myTable
> > > SET myType = 'In'
> > > WHERE MyId = 2
> > > AND myType = 'Ot'
> > >
> > > /*
> > > Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> > > Error: step: Duplicate record. rollback is done.
> > > Msg 3609, Level 16, State 1, Line 1
> > > The transaction ended in the trigger. The batch has been aborted.
> > > */
> > >
> > >
> > > John
> > >
> > > "JIM.H." wrote:
> > >
> > > > This is part of my trigger on table T1. I am trying to check if the records
> > > > inserted to T1 is available in myDB.dbo.myTable or not (destination table).
> > > > If it is available rollback T1. It does not do that although I insert the
> > > > same records twice.
> > > >
> > > > -- duplicate record check
> > > > SET @.step = 'Duplicate record'
> > > > IF EXISTS (
> > > > SELECT i.myID, i.Type
> > > > FROM INSERTED i INNER JOIN
> > > > myDB.dbo.myTable c ON i.myID = c.myID
> > > > GROUP BY i.myID, i.Type
> > > > HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> > > > )
> > > > BEGIN
> > > > ROLLBACK transaction
> > > > RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> > > > Return
> > > > END
> > > >
> > > > What is problem?
> > > >
> > > >

Duplicate record removal

I can identify the duplicate rows and show number of duplication by using
this
SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
FROM USERS
GROUP BY [USER_NUMBER], [EMAIL]
HAVING Count([USER_NUMBER]) > 1
I can't figure out how to delete duplicate records and keep one distinct
record with smallest ID
Example:
TABLE: USERS
ID USER_NUMBER EMAIL
1 12 a@.a.com
2 12 a@.a.com
3 13 b@.b.com
4 14 c@.c.com
5 14 c@.c.com
Output:
ID USER_NUMBER EMAIL
1 12 a@.a.com
3 13 b@.b.com
4 14 c@.c.com
Thanks,
HowardDELETE FROM users
WHERE EXISTS
(SELECT *
FROM users AS U
WHERE U.user_number = users.user_number
AND U.email = users.email
AND U.id < users.id) ;
Nulls, if any, will be ignored.
David Portas
SQL Server MVP
--|||Hi Howard,
Try this statement
Delete From USERS
Where [USER_NUMBER] NOT In
(Select [USER_NUMBER] From USERS Users_Out Where [USER_NUMBER]
IN (Select Min(Users_In.[USER_NUMBER]) From USERS Users_In Where
Users_In.[EMAIL] = Users_Out.[EMAIL]
)
)
PLEASE NOTE : I have not tested this statement so please run the following
query and ensure that this is what you want to be deleted.
Select * From USERS
Where [USER_NUMBER] NOT In
(Select [USER_NUMBER] From USERS Users_Out Where [USER_NUMBER]
IN (Select Min(Users_In.[USER_NUMBER]) From USERS Users_In Where
Users_In.[EMAIL] = Users_Out.[EMAIL]
)
)
Do mail if this post help s you.
Vishal Khajuria
9886170165
IBM Bangalore
"Howard" wrote:

> I can identify the duplicate rows and show number of duplication by using
> this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>
>|||thanks for your replies
I solved the problem.
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OlFbAlQ6FHA.3588@.TK2MSFTNGP15.phx.gbl...
>I can identify the duplicate rows and show number of duplication by using
>this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>|||Hi,
This will solve your query ...
delete from test where test.id not in (select top 1 b.id from test b
where (test.user_number=b.user_number) and
(test.email=b.email))
Regards,
Predrag Stojanovic
Analist/Programer
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OlFbAlQ6FHA.3588@.TK2MSFTNGP15.phx.gbl...
> I can identify the duplicate rows and show number of duplication by using
> this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>|||You missed out the ORDER BY. TOP 1 may not retrieve the minimum value
of ID unless you use ORDER BY.
Also, notice that this version will not delete any rows for a
particular user_name if a NULL ID exists for that user_name. Probably
that's not likely - I'd guess that ID is the PRIMARY KEY - but that's
one potential catch with TOP and with NOT IN.
David Portas
SQL Server MVP
--|||Here is the process:
1. SELECT * INTO #Temp1 FROM [Table1]
2. TRUNCATE TABLE [Table1]
3. CREATE UNIQUE INDEX [Index1] ON [Table1] (Unique Column Names) WITH
IGNORE_DUP_KEY
4. INSERT INTO [Table1] (Column Names) SELECT (Column Names) FROM [Table1]
5. DROP INDEX [Table1].[Index1]
Substitute your own names for those within the [].
Unique Column Names are those columns that uniquely identify each row.
Column Names is the full list of columns in your table.
HTH,
Mike
"Howard" wrote:

> I can identify the duplicate rows and show number of duplication by using
> this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>
>sql

Duplicate record question

In order to check that a new users ID does not already exist in the database I thought it would be a good idea to put the Insert into a Try Catch statement so that I can test for the duplicate record exception and inform the user accordingly. I was also trying to avoid querying the data base before executing the Insert.

The problem is what to actually test for. When the code throws the exception it is a big long string . .

"Violation of PRIMARY KEY constraint 'PK_Users_2__51'. Cannot insert duplicate key in object 'Users'"

I just thought that there has to be something simplar to test for than comparing the exception to the above string.

Can anyone tell me of a better way of doing this ?

(by the way I am only using Web Matrix and MSDE in case it matters)

MarkI would use a stored procedure, check for dups in the SP, and then return a code indicating success or failure.|||Isn't the error code that you'd compare against?

Duplicate Record Problem

I am working on a web application that utilizes a sql server database. One of the tables is a large text file that is imported through a DTS package from a Unix server. For whatever reason, the Unix box dumps quite a few duplicate records in the nightly run and these are in turn pulled into the table. I need to get rid of these duplicates, but can't seem to get a workable solution. the query that is needed to get the records is:
SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,
tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTime
FROM tblAppointments CROSS JOIN
tblPTDEMO2
WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)
AND tblAppointments.Insurance1 = 'MED'
AND tblAppointments.ApptTypeID <> 'MTG'
AND tblAppointments.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By tblAppointments.ApptDate
My first thought was to try to get a Select DISTINCT to work, but couldn't figure out how to do this with the query. My next thought was to try to set up constraints on the table, but, since there are duplicates, the DTS package fails. I assume there is a way to set up the transformations in a way to get this to work, but I'm not enough of an expert with SQL Server to figure this out on my own. I guess the other way to do this is to write some small script or application to do this, but I suspect there must be an easier way for those who know what they are doing. Any help on this topic would be greatly appreciated. Thanks.In SQL Server duplicates are eliminated by using Unique constraint or Index on the index you can add IGNORE_DUP_KEY option and make it all inserts because that option will not affect update statements. And they are very slow but the UNION operator also eliminates duplicates by applying implict Distinct. Run a search for all of the above in SQl Server BOL(books online). Hope this helps.|||For an import approach, I'd suggest inserting the records into atemporary table first. Then select out the unique records and insertthem into your destination table.
For the above query, would something like this work?
SELECT DISTINCT
PatientID,
MRNumber,
PatientFirstName,
PatientLastName,
PatientDOB,
PatientSex,
NewPatient,
...etc, etc, etc...
FROM
(
SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,
tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
tblPTDEMO2.Ins2CertNmbr,tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr,tblAppointments.ApptDate, tblAppointments.ApptTime
FROM tblAppointments CROSS JOIN
tblPTDEMO2
WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)
AND tblAppointments.Insurance1 = 'MED'
AND tblAppointments.ApptTypeID <> 'MTG'
AND tblAppointments.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By tblAppointments.ApptDate
) AS SQ|||

I have re-written this a bit to remove the CROSS JOIN syntax which I think is less efficient than the INNER JOIN that is implied in the WHERE clause:
SELECT A.PatientID, D.MRNumber, A.PatientFirstName, A.PatientLastName, A.PatientDOB, A.PatientSex,
A.NewPatient, A.HomePhone, A.WorkPhone, A.Insurance1, D.Ins1CertNmbr, A.Insurance2,
D.Ins2CertNmbr, A.Insurance3, D.Ins3CertNmbr, A.ApptDate, A.ApptTime
FROM tblAppointments A INNER JOIN tblPTDEMO2 D on A.PatientID = D.MRNumber
WHERE A.Insurance1 = 'MED'
AND A.ApptTypeID <> 'MTG'
AND A.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By A.ApptDate

Now, I assume that the duplicates are in thetblPTDEMO2table. Tell me more about these: are the IDs duped?

|||Thanks for your response. The query you wrote is conceptually what I am trying to get, but it throws a syntax error. I've played with a number of variations of this and I can't seem to get it to work.
The other option of using a temporary table during the DTS package is viable, but once again, I can't seem to get the query right. I think what I want is something like:
Insert Into tblAppointments AppointmentKey, PatientFirstName, PatientLastName, ...VALUES (Select DISTINCT AppointmentKey, PatientFirstName, PatientLastName, ...)
but once again, I can't seem to get the syntax correct. Any help would be greatly appreciated. Thanks.|||Thanks for your response. Actually, the tblAppointments table is the one with the duplicates, and yes, the IDs are duplicated as well.

Duplicate Record

Hi guys how do you hide duplicate records, how would I do a select statement for that
In (SELECT [AccountNo] FROM [2006_CheckHistory] As Tmp GROUP BY [AccountNo] HAVING Count(*)>1 )
I have about had it with this database I have been asked to make a report out ofwhat do you want to do ?

Lists only unique AccountNo ?
select distinct AccountNo from [2006_CheckHistory]|||i personally don't "hide" duplicate rows, i prevent them from happening|||so there was nothing I could do about the duplicates, they are making the query difficult to create, and this thing is driving me crazy. Some took data and just threw it into a database and called it a day (was a VB programmer)

what do you want to do ?

Lists only unique AccountNo ?
select distinct AccountNo from [2006_CheckHistory]

I'm trying to make the AccountNo unique, you dont need it entered more then once in a table.|||I want to delete them I just want to create a query that shows me all the duplicates and then delete them, leaving only one AccountNo instead of several. Does that make sense??|||okay, you want one row per AccountNo

what about the other columns? which values would you like from those?

here's an example of the problem you could be facing --
AccountNo Name Age City
1234 Fred 23 Chicago
1234 Fred 23 Waukegan
1234 Fred 26 Chicago
1234 Todd 23 Chicago
5678 Mary 16 Milwaukee
5678 Mary 15 Milwaukee
5678 Mary 17 Milwaukeenow how are we supposed to know which row to keep?|||Yeah, the problem that I need to work around is when I have it as a report I cant create a total per AccountNo it just totals it for all the records rather then per account... like if there is an account for number 000111222 then I cant get the total amount for just that record. I need one record to show and its total for that year and this is such a BIG PAIN. I didnt create this database so its a matter of some serious fixing. The problem is gettign this done before the 22nd of January...ACCCKKKK HELP PLEASE!!!!!!!!!!!!!!

Then as soon as I'm done I'm going to do some serious restructuring|||It seems that you are talking about one table, but think about those tables which are related with this one.

Whatever you plan, plan considering all related tables in the database.|||Take your offending table, and run this query against just that table (no, joins, no fancy stuff, just this query alone):SELECT Count(AccountNo), Count(DISTINCT AccountNo)
FROM OffendingTable-PatP|||Oh yeah, while you are at it could you explain what you consider to be a "duplicate record" ? Does that mean that all columns in more than one row are identical? Does it mean that one column (AccontNo) in more than one row are identical? Does it mean something completely different that I haven't thought of yet?

-PatP|||Thank you so much guys,

It means that there is identical [AccountNo] in the table, these are businesses, now when the business changes ownership they indicate that with a letter like A, B..etc, or if theres equipment at that business thats indicated with a letter. I was thinking of making the AccountNo Unique by getting rid of the duplicates and making it unique. And having a seperate table with the A, B and the date they changed hands linked to the AccountNo table, since this is table would reflect changes to the [AccountNo] table. The [AccountNo] table would have the information concering the business, like address, name of business..etc. Does that make sense??|||Only problem is what about the other columns...which values do you want? Does it matter to you? It usually does.

Post the DDL of the table and some sample data, and what you want in the final table...

And what are the report req's?|||Only problem is what about the other columns...which values do you want? Does it matter to you? It usually does.

Post the DDL of the table and some sample data, and what you want in the final table...

And what are the report req's?

OwnerManagerID AccountNo CheckDate CheckNumber CheckAmount OwnerAddress1 NameFor1099 OwnerCity OwnerState OwnerZip
A 00010002 2006-09-20 37065 $621.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-03-20 19006 $679.25 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-10-23 38341 $587.13 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-02-16 28548 $723.38 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-06-20 33119 $994.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-08-23 35806 $578.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-07-24 34551 $488.50 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-05-18 31820 $573.50 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-04-21 30734 $582.88 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-12-20 40892 $614.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-01-17 24366 $427.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-11-17 39614 $730.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010004 2006-06-22 23676 $1,972.79 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-02-09 23644 $1,737.02 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-03-10 23660 $1,632.50 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-05-31 32492 $1,586.44 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-09-12 36608 $1,778.60 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-07-20 34540 $1,715.68 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-04-11 30257 $1,951.40 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-08-07 35355 $1,362.58 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-01-06 25876 $1,834.49
A 00010004 2006-12-06 40426 $1,647.72 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-11-13 39605 $1,795.44 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-11-06 39164 $1,524.11 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937|||I want in the ownermanager_table Ownermanagerid, Address, city, state zip..ect, AccountNo, fedtaxid.

That should be one table and the other table should be the check amount, checkdate, name of bank..etc|||sorry, that looked like really private information so i obfuscated it|||Thank you appreciate it|||WOOHOO I GOT THE QUERY I NEEDED ITS OVER THIS HORRIBLE NIGHTMARE IS OVER....WOOOHOOOOOOOO!!!!!!!!!!!!!!!!!!!

I'M SO HAPPY I COULD SCREAM...Good grief this person just took live DATA and stuck in a table and just called it a day, no documentation no nothing. The whole thing is structured so badly its a nightmare...EEEEEEEEEEEEEEEEEE!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!

Thank you guys

duplicate record

Dear All,

I need to identify duplicate records in a table. TableA [ id, firstname, surname] Id like to see records that may be duplicates, meaning both firstname and surname are the same and would like to know how many times they appear in the table

Im not sure how to write this query, can someone help? Thanks in advance!try something like this:

select id, fname, lastname, count(*)
from tablename
having count(*) > 1|||Use a subquery with the HAVING clause to isolate duplicated firstname/lastname records, then link to the table to get id values:

select YourTable.id,
YourTable.firstname,
YourTable.surname,
YourSubquery.occurances
from YourTable
inner join --YourSubquery
(select firstname,
surname,
count(*) as Occurances
from YourTable
group by firstname,
surname
having count(*) > 1) YourSubquery
on YourTable.firstname = YourSubquery.firstname
and YourTable.surname = YourSubquery.surname|||try something like this:

select id, fname, lastname, count(*)
from tablename
having count(*) > 1

Hmm ok, doesn't look complex at all, thanks!

It gave me error messages about not having a grouped by statement in there, so I added it. It works fine, thanks a lot!sql

Duplicate last record when using SqlDataAdapter.Update for Insert command

I'm getting duplicate records for the last record in the datatable. No matter how much or how little my datatable contains row records, it always duplicate the last one for some reason. Is there something wrong with my code below? EXAMID pulling from another stored procedure, which is outputed back to a variable.

--Data Access Layer--

If dt.Rows.Count > 0Then

'INSERT EXAM ROSTER

InsertComm =New SqlCommand
sqladapter =New SqlDataAdapter
InsertComm =New SqlClient.SqlCommand("ExamOfficers_AddOfficerSpecificExamRoster", conndb)
InsertComm.CommandType = CommandType.StoredProcedure

sqladapter.InsertCommand = InsertComm

InsertComm.Parameters.Add("@.examid", SqlDbType.Int)InsertComm.Parameters("@.examid").Value = examid
InsertComm.Parameters.Add("@.officerid", SqlDbType.Int, 12,"Officer_UID")
InsertComm.Parameters.Add("@.reimburse", SqlDbType.Bit, 12,"ReimburseToDb")
InsertComm.Parameters.Add("@.posttest", SqlDbType.Int, 12,"Post_Test")
InsertComm.Parameters.Add("@.pqcdate", SqlDbType.DateTime, 12,"pqc_date")
InsertComm.Parameters.Add("@.pqcscore", SqlDbType.Int, 12,"pqc_score")

conndb.Open()

sqladapter.UpdateBatchSize = 100
InsertComm.UpdatedRowSource = UpdateRowSource.None
sqladapter.Update(dt)

InsertComm.ExecuteNonQuery()
InsertComm.Dispose()

EndIf

--Stored Procedure--

ALTERPROCEDURE [dbo].[ExamOfficers_AddOfficerSpecificExamRoster]

@.ExamIDasint,
@.OfficerIDasint,
@.reimburseasbit=NULL,
@.posttestasint=NULL,
@.pqcdateasdatetime=NULL,
@.pqcscoreasint=NULL

AS
BEGIN
SETNOCOUNTON;

InsertInto Exam_Officers(EXAM_UID,Officer_UID,reimburse,post_test,pqc_date,pqc_score)
values(@.ExamID,@.OfficerID,@.reimburse,@.posttest,@.pqcdate,@.pqcscore)

END

Hi,

Try to comment this line InsertComm.ExecuteNonQuery() because it seems like that DataAdapter itself updates the database with its Update method but after that you are also running the command manually with ExecuteNonQuery. I think this is the reason why your last record is saved twice. Try it out.

Hope it will help you

Thanks and best regards,

sql

Duplicate Key problem

Replication is failing with an error message about inserting a duplicate record into a reprint table, what can I do when a duplicate record error is present?
Can I just reinitialize that table?
"JLS" <jlshoop@.hotmail.com> wrote in message news:OpwakUclEHA.1904@.TK2MSFTNGP09.phx.gbl...
Replication is failing with an error message about inserting a duplicate record into a reprint table, what can I do when a duplicate record error is present?
|||enable logging as described in this kb article.
http://support.microsoft.com/default...&Product=sql2k
find the offending row and evaluate whether you can delete it at the
subscriber.
You may want to right click on your distribution agent and stop it and then
select the continue on data constency errors. When you clear the errors you
should run a validation to ensure your databases are in sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message
news:uKArPWclEHA.3912@.TK2MSFTNGP12.phx.gbl...
Can I just reinitialize that table?
"JLS" <jlshoop@.hotmail.com> wrote in message
news:OpwakUclEHA.1904@.TK2MSFTNGP09.phx.gbl...
Replication is failing with an error message about inserting a duplicate
record into a reprint table, what can I do when a duplicate record error is
present?

Duplicate key error

I have a table that I use an INSERT command to add a new record. The
primary key is an int field and I am calculating the highest # on table and
incrementing by 1 just before INSERT. On rare occasions, 2 people get the
same number and I get SQL error about duplicate. Is there a way to trap
this or prevent it and just assign a number 1 higher and try INSERT again
(note I cannot use Identity)? Thanks.
David"David C" <dlchase@.lifetimeinc.com> wrote in message
news:ufJVuh6DFHA.2804@.TK2MSFTNGP14.phx.gbl...
>I have a table that I use an INSERT command to add a new record. The
>primary key is an int field and I am calculating the highest # on table and
>incrementing by 1 just before INSERT. On rare occasions, 2 people get the
>same number and I get SQL error about duplicate. Is there a way to trap
>this or prevent it and just assign a number 1 higher and try INSERT again
>(note I cannot use Identity)? Thanks.
>
Which is exactly like saying "I am having trouble driving this nail with my
screwdriver (note I cannot use a hammer)."
IDENTITY is the right tool for the job. Everything else is an inferior
workaround. Now to get your inferior workaround to actually work, you must
wrap the select max(ID) and INSERT in a serializable transaction. Or
something like
BEGIN TRANSACTION
SELECT @.ID = MAX(ID) from T (tablockx,holdlock)
insert T (ID,...) valueS (@.ID,...)
COMMIT TRANSACTION
BTW, you can perhaps use an IDENTITY column on a different table to generate
your ID's, eg
http://groups-beta.google.com/group...bcc24
68
David|||David,
How are you doing this?
AMB
"David C" wrote:

> I have a table that I use an INSERT command to add a new record. The
> primary key is an int field and I am calculating the highest # on table an
d
> incrementing by 1 just before INSERT. On rare occasions, 2 people get the
> same number and I get SQL error about duplicate. Is there a way to trap
> this or prevent it and just assign a number 1 higher and try INSERT again
> (note I cannot use Identity)? Thanks.
> David
>
>|||Thank you. I agree, I always use Identity but have to wait for an old
conversion before I can change it to an identity. BTW, once I make it an
Identity field will it automatically set the SEED, or will I have to do that
with DBCC?
David
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e86uLq6DFHA.3928@.TK2MSFTNGP15.phx.gbl...
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:ufJVuh6DFHA.2804@.TK2MSFTNGP14.phx.gbl...
> Which is exactly like saying "I am having trouble driving this nail with
> my screwdriver (note I cannot use a hammer)."
> IDENTITY is the right tool for the job. Everything else is an inferior
> workaround. Now to get your inferior workaround to actually work, you
> must wrap the select max(ID) and INSERT in a serializable transaction. Or
> something like
> BEGIN TRANSACTION
> SELECT @.ID = MAX(ID) from T (tablockx,holdlock)
> insert T (ID,...) valueS (@.ID,...)
> COMMIT TRANSACTION
> BTW, you can perhaps use an IDENTITY column on a different table to
> generate your ID's, eg
> http://groups-beta.google.com/group...bcc
2468
> David
>|||David C wrote:
> I have a table that I use an INSERT command to add a new record. The
> primary key is an int field and I am calculating the highest # on
> table and incrementing by 1 just before INSERT. On rare occasions, 2
> people get the same number and I get SQL error about duplicate. Is
> there a way to trap this or prevent it and just assign a number 1
> higher and try INSERT again (note I cannot use Identity)? Thanks.
> David
You need to keep things locked in a transaction in order to prevent
users from stepping on one another. I generally prefer using another key
table that dishes out next key values if an identity can't be used.
In your case, you need to do something like this:
Begin Tran
Select @.NextID = MAX(ID) + 1 From TableA WITH (UPDLOCK, HOLDLOCK)
Insert TableA
Commit Tran
David Gugick
Imceda Software
www.imceda.com|||David,
You can also use a subquery in your INSERT statement, in this case you
don't need to use a transaction.
Instead of this:
insert MyTable values(ID, Col2, Col3, ..., ColN)
use this:
insert MyTable
select ID = IsNull((select max(ID+1) from MyTable), 1),
Col2,
Col3,
..,
ColN
Shervin
"David C" <dlchase@.lifetimeinc.com> wrote in message news:<ufJVuh6DFHA.2804@.TK2MSFTNGP14.ph
x.gbl>...
> I have a table that I use an INSERT command to add a new record. The
> primary key is an int field and I am calculating the highest # on table an
d
> incrementing by 1 just before INSERT. On rare occasions, 2 people get the
> same number and I get SQL error about duplicate. Is there a way to trap
> this or prevent it and just assign a number 1 higher and try INSERT again
> (note I cannot use Identity)? Thanks.
> David|||You'll need to recreate the table if you need to convert the existing values
to IDENTITY. You can't add the IDENTITY property to an existing column.
Hope this helps.
Dan Guzman
SQL Server MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:eHQjdv6DFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Thank you. I agree, I always use Identity but have to wait for an old
> conversion before I can change it to an identity. BTW, once I make it an
> Identity field will it automatically set the SEED, or will I have to do
> that with DBCC?
> David
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:e86uLq6DFHA.3928@.TK2MSFTNGP15.phx.gbl...
>|||> You can also use a subquery in your INSERT statement, in this case you
> don't need to use a transaction.
You can't prevent the assignment of duplicate values without a transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:4eaef17a.0502101720.4d2016f@.posting.google.com...
> David,
> You can also use a subquery in your INSERT statement, in this case you
> don't need to use a transaction.
> Instead of this:
> insert MyTable values(ID, Col2, Col3, ..., ColN)
>
> use this:
> insert MyTable
> select ID = IsNull((select max(ID+1) from MyTable), 1),
> Col2,
> Col3,
> ...,
> ColN
> Shervin
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:<ufJVuh6DFHA.2804@.TK2MSFTNGP14.phx.gbl>...|||Dan,
You are absolutely right. I just tested my code and it failed. I
thought SQL Server would automatically start a transaction to protect
the execution of subquery, but I was wrong. Thanks for correcting me.
Regards,
Shervin
Dan Guzman wrote:
you
> You can't prevent the assignment of duplicate values without a
transaction.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
> news:4eaef17a.0502101720.4d2016f@.posting.google.com...
you
The
table
get
to trap
INSERT again

Tuesday, March 27, 2012

Duplicate records

Monday, March 26, 2012

Duplicate record trigger

Duplicate record trigger

Duplicate record trigger

Duplicate record trigger

Duplicate record trigger

Duplicate record removal

Duplicate record question

Duplicate Record Problem

Duplicate Record

duplicate record

Duplicate last record when using SqlDataAdapter.Update for Insert command

Duplicate Key problem

Duplicate key error

DTS to .net

Blog Archive

About Me