Using SSE 2012 64-bit...How can I check all fields for duplicate records?I tried OVER PARTITION..But that is returning an error message
Code:
USE db
SELECT ROW_NUMBER() OVER (PARTITION BY all fields)
ORDER BY ID --Not unique) AS RowNumber
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator
I have this simple query But i want to check if the policy number appears more than once, I tried to use a count(RR.X_POLICY_NO)>1 in the where clause but i get an error, anybody have any suggestions?
QUERY:
Select
RR.X_POLICY_NO,
RR.X_POLICY_EFCTV_DT,
RR.X_ASCO_CD,
RR.PRODUCT_RENWL_ABBR,
From RR
WHERE
year(rr.X_POLICY_XPRTN_DT)>=2005
Group By
RR.X_POLICY_NO,
RR.X_POLICY_EFCTV_DT,
RR.X_ASCO_CD,
RR.PRODUCT_RENWL_ABBR
ORDER BY
rr.X_POLICY_NO
I am trying to find duplicate records based on a set of criteria, and flag one of those records as unique and the other duplicate ones as dup.
I found this suggestion:
IF( CALCULATE(Â
 COUNTROWS( Table1 );Â
 FILTER( Table1;  [Column1] = EARLIER( [Column1] ) && [Column2]
= EARLIER( [Column2]
) )Â
 ) > 1;
 "dup";
 "unique"
)
However, this method flags all duplicates as dup. how I can get to the enclosed results for one particular student?The dup check column shows the desired results. The criterion is any record with the same STUDENT_ID, PROG_DESC, FISCAL_YEAR will be considered as duplicates, but should at least have one record out of that group of duplicates flagged as 1.
STUDENT_ID| ACAD LEVEL |Â GENDER |
PROG_DESCÂ | RACEETHNICITY |
HOME_STATE | ACADEMIC_LEVELÂ | FISCAL_YEAR |
Dup Check
815581 Undergraduate
[code]....
Hi,
Does anyone have any sugestions as to the best way to achieve the following?
I want to display a list of products in a GridView. Against each product I want to place a button which will add the product to a "wish list" table.
I need to be able to check that the product has not already been inserted into the wish list.
Either suggested methods or links to tutorials will be appreciated.
Thanks in advance.
I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique.
How do I filter out only the duplicate zips.
Randy Garland
I have a large table that consists of the columns zip, state, city, county. The primary
key "zip" has duplicates but the rows are unique.
How do I filter out only the duplicate zips. So in effect I only have one row per unique key.
Randy Garland
if you just want a list of all rows with duplicate zipcodes then ...
SELECT * FROM TableName WHERE zip IN ( SELECT zip FROM TableName
GROUP BY zip HAVING COUNT(*)>1 )
Duncan
Duncan, I tried this but it does not return one row per key.
Randy Garland
I just converted an old non-relational database into something that MS SQL likes. The old primary keys were broken up into two columns, one being useful. The column I need to use has some rows with the same values in them.
I am looking for some way in a SQL script to look for the duplicate rows and add "_X" to the data where X is a value incremented by 1 for each duplicate row found.
For example, 3 duplicate rows with "5443aa" would return "5443aa", "5443aa_1","5443aa_2".
Any ideas?
--MartinZ
I'm designing an app for stock keeping. In my DB, I have a field called "ItemSerialNo" and I made it unique(but not the table's primary key). On my front end, I have a text box for Item Serial No and a combo box loaded with the item brands and also a save button. I know that if I try to save a serial no already existing in my DB, the app won't allow me because of the unique property of the field named "ItemSerialNo". But I want to be able to save a serial no already existing in my DB but with a different brand name.
For example, I want to be able to save information like:
1. ITEM SERIAL NO = 12345
BRAND NAME = AA
2. ITEM SERIAL NO = 12345
BRAND NAME = BB.
Hello, I want to write a unique constraint that applies to more than one column. What I mean is that the uniqueness should be that if column A is 5 and column B is 3 no other row where A and B has those values can exist.
Do I write this as a check constraint ? Or how do I do it ?
Also, is there anyone who knows some good reading on how to use Link Tables (many to many relations) in MS SQL Server ?
I have a table called tblImages with the following columns:
ImageID [int]
UserID [int]
MainImg [bit]
what i need to ensure is that only one MainImage can be = 1 (true) for each userId at any one time.
any idea what i need to do?
alex
I have 4 rows which are exactly the same. I want to delete one row but i do not have any unique identifing columns. How should i delete that row ?
View 1 Replies View RelatedWe are running the following query, which has a unique index on Table_2 (col1 and sys1), and Column col1 from Table_1 is unique.
select top 100 s.*, x.col1
from Table_1 s
left outer join Table_2 x
on x.col1 = s.col1 and x.sys1 = 'SYSTEM0'
Unfortunately this query returns duplicate rows. And every time the result is different
But once we dbcc dbreindex the unique index on Table_2, the result will not have any dups.
Any ideas?
Thanks
Steve
I have a look up table with old data, which i need to truncate and load with the new set of data, however when loading I'm getting the following error
[OLE DB Destination [32]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005Â
Description: "Cannot insert duplicate key row in object 'dbo.CaseType' with unique index 'idx_CaseType'. The duplicate key value is (49, AH).".
I know , what it means that since CaseType column has a unique index we cannot insert duplicate key, but in real world the scenarios are different , the record in question is as follows: so what is the workaround in this kind of scenario other than making it Non-unique Index?
CaseTypeID CountyID CaseCategory CaseTypeCode CaseTypeName
21 49 Probate AH Probate
48 49 Civil AH Adoption History
I have one table like below Test table. My requirement is to create constraints to confirm <g class="gr_ gr_331 gr-alert gr_gramm Grammar only-ins replaceWithoutSep" data-gr-id="331" id="331">uniqueness</g> of STID value 101 with LN.
likeÂ
ID - LN - STID
1 - 'ABC' - 101 Â ---- Valid Row
2 - 'ABC' - 202 --- Valid Row
3 - 'ABC' - 202 --Valid Row (as I want only unique when LN = 'ABC' with STID = 101)
4 - 'ABC' - 101 -- Invalid Row (As I want uniqueness base on LN and STID = 1011)
create table dbo.Test
(
ID int identity,
LN varchar(50),
STID bigint
)
Is this possible with constraints as I don't want to use <g class="gr_ gr_1041 gr-alert gr_gramm Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="1041" id="1041">trigger</g>.
The following SP causes the error "Violation of UNIQUE KEY constraint 'AlumniID'. Cannot insert duplicate key in object [table name].
The statement has been terminated." AlumniID is the table's PK and is set to autoincrement. I'd appreciate any help or suggestions.
1 ALTER PROCEDURE dbo.sp_CreateUser
2
3 @UserID uniqueidentifier,
4 @UserName nvarchar(128),
5 @Email nvarchar(50),
6 @FirstName nvarchar(25),
7 @LastName nvarchar(50),
8 @Teacher nvarchar(25),
9 @GradYr int
10
11 AS
12 SET NOCOUNT ON;
13 --DECLARE @UserID uniqueidentifier
14 --SELECT @UserID = NULL
15 --SELECT @UserID = UserID FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName-- AND @ApplicationId = ApplicationId
16 INSERT INTO [table]
17 (UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
18 VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr
After adding a Unique constraint to a database I cannot add more than one record with a null value for the constrained field. I've tried both adding the constraint to an empty table as well as a table with multiple null values already in the subject field; both efforts have failed.
According to BOL SQL-7 allows Unique Constraints on fields with Null values. Am I missing a step? I do need to allow nulls in the field yet ensure that when there is a non-null value it is unique.
The SQL statement I've used is: ALTER TABLE tbl_MasterUIC ADD CONSTRAINT uniquesamplenbr UNIQUE NONCLUSTERED (samplenbr)
Thanks for any and all suggestions
We write to a log file each time a job runs. We give each job a unique batchid. I want to compare the run times of each step/record between two batch ids: '20150101888' and '20150101777'. Column Mins in the number of minutes each step ran. I am having trouble comparing the rows that have generic process and stepname – Trans Switch in this example. A new process within a batchid starts with a 'XX', 'Load'.
So I want to compare CA's Trans to CA's Tran Switch and ER's Trans Switch to ER's, etc. There can be multiple Trans Switch per process. There should be the same number between each batch, but no guarantees that something might change. Also, Trans Switch is not the record right after the new process (CA, ER) in production.
I have just made a very simplified example.
/**
Want to compare 20150101888 to 20150101777 and end up with this result set. Notice that the duplicate process/step within a process has the process (CA and ER in this example) and a sequential number added to it: 'CA Trans 1'. Need this to pull out the largest time differences.
Time difference, process, step, mins1, mins2, batchid1, batchid2
-6, CA, Load, 17, 23, 20150101888, 20150101777
0, CA Trans 1, Switch, 8, 8, 20150101888, 20150101777
-6, CA Trans 2, Switch, 9, 15, 20150101888, 20150101777
-4, ER, Load, 7, 11, 20150101888, 20150101777
-4, ER Trans 1, Switch, 7, 11, 20150101888, 20150101777
**/
[Code] ....
I have a table with 0 records. When I try to insert records using a SP, it gives the following error.
Violation of UNIQUE KEY Constraint 'constraint name'. Cannot insert duplicate key in object 'Objectname'.
How do I resolve this.
Thanks.
What I am trying to do is this. I have two tables that someone else created and now I have to fix there mess. Both tables have 3 like fields. I want to check and see if the ProjectNumber field from the Artifacts table does not find a match in the Projects table. If no match is found return the record from artifacts table.
Code:
SELECT *
FROM Artifacts INNER JOIN Project ON Artifacts.ProjectNumber = Project.ProjectNumber
WHERE Artifacts.ProjectNumber NOT IN (Project.ProjectNumber);
The next thing I have to do is insert a record of the three fields from the Artifacts table to the Projects table. Any help would be great.
I have 1 client who keeps running into the following error on the subscriber and merge agents >
€œCannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.€?
Last time we got this error I ran a reindex on table MSmerge_genhistory on the publisher database, I then successfully generated a new snapshot and the subscribers started to synchronize again. This time around I keep getting the error even after I follow these steps (I also ran all the jobs to clean up replication). The last time I ran into this error I created a job to reindex msmerge_genhistory on a nightly bases in an effort to avoid this problem. Can somebody please provide me with a workaround and also the reason why this error occurs in the first place.
Thank you in advanced,
Pauly C
Hi.
I have been recently redesigning my tables - creating FK
relationships from child tables to the PK userid in the Users table.
The specifics of what I did and why can be seen here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1968856&SiteID=1
But, now I am getting the below error:
Cannot insert duplicate key row in object 'dbo.lastlogin' with unique index 'IX_lastlogin'.
The statement has been terminated.
Or, for that matter, SavedSearches or any other table where I need to
insert the same userid twice. I can see why I would want to avoid duplicates in the Users table. But, for lastlogin, savedsearches, and
a few of my other tables, the same user may account for multiple rows.
Any suggestions as to where I messed up and how to deal with this?
Thanks.
DBO.USERS
Code Snippet
CREATE TABLE [dbo].[users](
[userid] [int] IDENTITY(1,1) NOT NULL,
[lastname] [varchar](50) NULL,
[firstname] [varchar](50) NULL,
[email] [varchar](50) NOT NULL,
[alternateemail] [varchar](50) NULL,
[password] [varchar](50) NOT NULL,
[role] [varchar](10) NOT NULL,
[securityquestion] [varchar](50) NOT NULL,
[securityanswer] [varchar](50) NOT NULL,
[zipcode] [int] NOT NULL,
[birthmonth] [tinyint] NOT NULL,
[birthday] [tinyint] NOT NULL,
[birthyear] [int] NOT NULL,
[gender] [varchar](10) NULL,
[city] [varchar](50) NULL,
[state] [varchar](50) NULL,
[country] [varchar](50) NULL,
[registerdate] [datetime] NOT NULL,
[editdate] [datetime] NULL,
[confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3] DEFAULT ((0)),
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_email] UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** Object: Table [dbo].[lastlogin] Script Date: 08/22/2007 14:16:16 ******/
SET ANSI_NULLS ON
DBO.SAVEDSEARCHES
CREATE TABLE [dbo].[savedsearches](
[savedsearchesid] [int] IDENTITY(1,1) NOT NULL,
[searchname] [varchar](50) NOT NULL,
[userid] [int] NOT NULL,
[date] [datetime] NULL,
[isdefault] [bit] NULL,
[gender] [char](10) NULL,
[startyear] [varchar](50) NULL,
[endyear] [varchar](50) NULL,
[country] [varchar](50) NULL,
[miles] [int] NULL,
[pictures] [varchar](50) NULL,
[postal] [int] NULL,
[sort] [tinyint] NULL,
[photostring] [varchar](50) NULL,
[orderby] [tinyint] NULL,
CONSTRAINT [PK_SavedSearches] PRIMARY KEY CLUSTERED
(
[userid] ASC,
[searchname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[savedsearches] WITH NOCHECK ADD CONSTRAINT [FK_savedsearches_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[savedsearches] CHECK CONSTRAINT [FK_savedsearches_users]
GO
SET QUOTED_IDENTIFIER ON
GO
The following insert statement returned the error in the subject because userid = 32 already exists in the Users table.
INSERT INTO lastlogin
VALUES (32, CONVERT(VARCHAR(26), GETDATE(), 109), 1, CONVERT(VARCHAR(26), GETDATE(), 109))
DBO.LASTLOGIN
Code Snippet
CREATE TABLE [dbo].[lastlogin](
[lastloginid] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NOT NULL,
[date] [datetime] NOT NULL,
[status] [bit] NOT NULL CONSTRAINT [DF_lastlogin_status] DEFAULT ((0)),
[activity] [datetime] NOT NULL CONSTRAINT [DF_lastlogin_activity] DEFAULT (getutcdate()),
[online] AS (case when [status]=(1) AND datediff(minute,[activity],getutcdate())<(30) then (1) else (0) end),
CONSTRAINT [PK_lastlogin] PRIMARY KEY CLUSTERED
(
[date] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[lastlogin] WITH NOCHECK ADD CONSTRAINT [FK_lastlogin_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[lastlogin] CHECK CONSTRAINT [FK_lastlogin_users]
Good morning all,
I have been tasked with an interesting problem using SQL 2005. Simplified it looks like this: I have two tables both well over a million rows, totalling unrelated keys between the tables. I need to find records in table one that do not exist in table two, the twist is this, I need to find rows where the invoice number in table 1 does not exist at all in table 2 or where the invoice number exists but the timestamp associated with the invoice in table 2 is more than 10secs different than the invoice/timestamp in table 1. I'm fairly new to sql and, although I can do the really simple stuff, this one is over my head I'm afraid. Any help at all would be greatly appreciated. I would also like to hear any suggestions regarding books that might give examples of sql code at around this level of difficulty. Thanks in advance for your assistance! Paul
Obviously, I'm a complete n00b at SQL.
I have a table in Access 2003 with about 6,000 records and there are about 20 records that have duplicate data in the first field (CompID).
I'm trying to make the first field my primary key, so I need to fix these duplicate entry.
I could export to Excel and fix the problem that way, but in the interest of learning SQL I want to figure out how to do it properly.
Thanks in advance for what is hopefully a simple answer.
I have an issue where I am getting an error on an unique index.
I know why I am getting the error but not sure how to get around it.
The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.
But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.
It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.
How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.
CREATE TABLE #TestTable
(
Name varchar(50),
Email varchar (40)
)
Insert #TestTable (Name,Email) Values('Tom', 'tom@aol.com')
[Code] .....
Hi,I need to enforce that a table does not have "duplicates" for aspecific status type in the table.If the column "STATUS" = 2, then there can not be more than one rowwith a specific "ID" column.I can not use a unique key constraint because duplicate values for thiscombo of columns is valid for the status = 1.Just when the status = 2, there can not be any other rows with the sameID and status = 2.Any ideas?-Paul
View 3 Replies View RelatedWe are developing a project that is expected to hold TB of data and the back end used is SQL Server 2005.
I have the following problem
I have applied Nonclustered index over a column on a table.
Designed a SP for insertion which caters for updation incase the criteria based on the input is met.
The logic goes like this
Incase there exists a row containing the value of the column that is indexed for uniqueness, there should be updation. If not there should be a new row created.
However often there is an error message that is placed above. This happens only on some of the SPs and only on rare occasions.
Can any body tell me if there is any problem with the SQL Server 2005
Thanks in advance
R Suresh, SQLDBA
Hi there ...here comes a tricky one.
I have a database table which needs to make the Index "ParentREF, UniqueName" unique - but this fails because duplicate keys are found. Thus I now need to cleanup these duplicate rows - but I cannot just delete the duplicates, because they might have rows in detail tables.
This means that all duplicate rows needs an update on the "UniqueName" value - but not the first (valid) one!
I can find those rows by
SELECT OID, UniqueName, ParentREF, CreatedUTC, ModifiedUTC FROM dbo.CmsContent AS table0
WHERE EXISTS (
SELECT OID, UniqueName, ParentREF FROM dbo.CmsContent AS table1
WHERE table0.ParentREF = table1.ParentREF
AND table0.UniqueName = table1.UniqueName
AND table0.OID != table1.OID
)
ORDER BY ParentREF, UniqueName, ModifiedUTC desc
...but I struggle to make the required SQL (SP?) to update the "invalid" rows.
Note: the "valid" row is the one with the newest ModifiedUTC value - this row must kept unchanged!
ATM the preferred (cause easiest) way is to rename the invalid rows with
UniqueName = OID
because if I use any other name I risk to create another double entry.
Thanks in advance to whoever can help me
We have a table that has customers invoices and payment records. In some cases a customer has 10 lines with 10 different invoice numbers but may have paid 2 or more invoices with one check. I need to know how many unique payments were made per customer.
Cust# Inv# Chk#
1 109 101
1 110 101
1 111 102
3 112 10003
2 113 799
2 114 800
1 115 103
3 116 10009
2 117 799
1 118 103
So I need the statement to update the customer table with the annual payments
Customer Table
Cust# Payments
1 3
2 2
3 2
I get close but just not getting it to sort itself out.
Is there a way to get a Stored Procedure to return only unique records from a table? I am using a Stored PRocedure to query a table and it returns all the records in the table and there are many duplicates in the information. I am using SQL Server 2K.
View 12 Replies View RelatedI have the following sql:
SELECT COUNT(patient.patientID) AS total_patients
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE sub_categoryID = 4 OR patient_record.allocated = 4
from the database this gives me a COUNT of 22, it should only be 10. I am doing a join and it gives the total records in the two tables where I only want the total in the left table(patient).
How can I GROUP BY patient.patientID in a COUNT query
Thanx, Robson
Hello Everyone and thanks for your help in advance. I have a SQL Server Table wtih approximately 100,000 records. I need to determine if there are duplicate records in this table. My probelm is that there is a unique ID column that was added for each row, so I'm not exactly sure how to filter the rows. Any help on this would be greatly appreciated. Thanks.
View 4 Replies View RelatedI'm trying to read from a table and return only the unique records from a table.
The table has 3 columns,
ID - Autoinc
Amount - decimal 6,2
Name - varchar(10)
If there are records like
ID Amount Name
1 0.03 Name1
2 0.07 Name9
3 0.05 Name3
4 0.03 Name8
5 0.07 Name4
6 0.06 Name7
I am wanting to retreive only records 3 & 6 (values 0.05
0.06) as they are the only ones where amount is unique i.e. only 1 entry in the table.
I put the following sql statement together which achieves this but it it only returns the amount value
select Count(amount),amount from bids group by amount having count(amount)<2 order by amount
So I then expanded it to read
select Count(amount),amount,name from bids group by amount,name having count(amount)<2 order by amount
not sure where i'm going wrong but the results returned were
0.03
0.03
0.05
0.06
0.07
0.07
Appreciate any help here.
Hello,
I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.
How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?
Does my request make sense? Please let me know if you have questions.
Thank you for your help!
CSDunn