We are experiencing a problem at more than one site - it has only
just started happening.
We are actually getting duplicate Identity column values in various
tables and the values seem random - its not like the counter just gets
wound back.
We have used dbcc checkident and also bcp out and in the data, which
of course corrected the tables but only temporarily. Our application
is not doing any select into`s - just plain old inserts which for some
reason are allowing dups to be inserted into the tables.
problem sites are either sp3 or sp4.
There are thousands of sites (including most of ours) where there are
no problems like this with this particular app.
hi, I've done Googling and forum hunting but haven't had success finding a simple answer... My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question and also identity doesn't seem to fit since I'm not using autofill for this particular field. Is there some other way doing this on Sql Server 2005?
I have a table with an Identity Column set up. I also have an index on the table that is set to Ignore Duplicate. Identity starts at 1 and is incremented by 1.
So first 5 rows inserted get identity
1
2
3
4
5
If I insert rows that get ignored because of the index with Ignore Duplicate, it is ignored correctly. But, the next row to get inserted will have an identity value of 7.
So, even though the insert was ignored because of the index with Ignore Duplicate, the Identity column was incremented behind the scenes.
I have a table which is a license holder table (i.e., plumbers, electricians etc...) There are some people who appear in the table more than once as they have more than 1 type of license. I am tasked with querying out 200 of these people a week for mailing a recruitment letter which I am doing using the following select statement:
SELECT TOP 200 Technicians.Name, Technicians.Address, Technicians.City, Technicians.State, Technicians.ZipCode, Technicians.LicenseType FROM Technicians
My problem is that this doesn't deal with the duplicates and distinct won't work because I need to pass the license type and that's the one field that's always distinct while the name and adress fields duplicate.
I'm ok at SQL, but this has really confused me - please help!
I've got a table which (amongst others) has two columns - myID, and barcode. Ideally myID and barcode should be a 1 to 1 relationship... but it's not. So how can I get a list of all rows where 1 barcode value has >1 myID? (and also vice versa if possible). Thanks!
I am trying to figure out a query to which the logic is simple, but I'm missing some basic SQL skill or technique somewhere.....
I have a table called NAME, from which I want to display the following columns:
namecode name Postaladdress
(there's a column called NAME as well as the table being called name incase your wondering)...
I'm concerned with the namecode and name columns for now....
I want to find duplicate name values from the table, how do I do this?
for example, in name, I have the value 'Long water Beach' appear twice. I want the query to show me how many times this appears from this table (duplicate values??)
Both namecode and name are navchar datatypes. So far I tried something like:
select name, namecode, postaladdress from name where name in (select name from name group by name, namecode, postaladdress having count(name) > 1)
I guess we can focus on the subquery here, but I just need that function or code which shows me the duplicate values!! I know its something staring me in the face, but I just can't see it!!
Hi,I have written a stored procedure to store values from a report i generated to the DB. Now there is a column PKID which is the primary key but also needs to be repeated at times. I tried to clear the memory that the same PKID has already been entered for which I wrote another SP. SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate]( @ReportDate smalldatetime, @SiteID int, @DataFeedID int, @FromCode varchar, @Sent int, @Delivered int, @TotalOpens REAL, @UniqueUserOpens REAL, @UniqueUserMessageClicks REAL, @Unsubscribes REAL, @Bounces REAL, @UniqueUserLinkClicks REAL, @TotalLinkClicks REAL, @SpamComplaints int, @Cost int)ASSET NOCOUNT ON DECLARE @PKID INTDECLARE @TagID INTSELECT @TagID=ID FROM Tag WHERE SiteID=@SiteID AND FromCode=@FromCodeSELECT @PKID=PKID FROM DimTag WHERE TagID=@TagID AND StartDate<=@ReportDate AND @ReportDate< ISNULL(EndDate,'12/31/2050')IF @PKID IS NULL BEGIN SELECT TOP 1 @PKID=PKID FROM DimTag WHERE TagID=@TagID AND SiteID=@SiteIDDECLARE @LastReportDate smalldatetime, @LastSent INT, @LastDelivered INT, @LastTotalOpens Real, @LastUniqueUserOpens Real, @LastUniqueUserMessageClicks Real, @LastUniqueUserLinkClicks Real, @LastTotalLinkClicks Real, @LastUnsubscribes Real, @LastBounces Real, @LastSpamComplaints INT, @LastCost INT SELECT @Sent=@Sent-Sent,@Delivered=@Delivered-Delivered,@TotalOpens=@TotalOpens-TotalOpens,@UniqueUserOpens=@UniqueUserOpens-UniqueUserOpens,@UniqueUserMessageClicks=@UniqueUserMessageClicks-UniqueUserMessageClicks,@UniqueUserLinkClicks=@UniqueUserLinkClicks-UniqueUserLinkClicks,@TotalLinkClicks=@TotalLinkClicks-TotalLinkClicks,@Unsubscribes=@Unsubscribes-Unsubscribes,@Bounces=@Bounces-Bounces,@SpamComplaints=@SpamComplaints-SpamComplaints,@Cost=@Cost-Cost FROM CrmPublisherSummary WHERE @LastReportDate < @ReportDate AND SiteID=@SiteID AND TagPKID=@PKIDUPDATE CrmPublisherSummary SET Sent=@Sent, Delivered=@Delivered, TotalOpens=@TotalOpens, UniqueUserOpens=@UniqueUserOpens, UniqueUserMessageClicks=@UniqueUserMessageClicks, UniqueUserLinkClicks=@UniqueUserLinkClicks, TotalLinkClicks=@TotalLinkClicks, Unsubscribes=@Unsubscribes, Bounces=@Bounces, SpamComplaints=@SpamComplaints, Cost=@Cost, TagID=@TagID WHERE ReportDate=@ReportDate AND SiteID=@SiteID AND TagPKID=@PKIDENDELSE INSERT INTO CrmPublisherSummary( ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens, UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes, Bounces, SpamComplaints, Cost, DataFeedID, TagID) VALUES( @ReportDate, @SiteID, @PKID, @Sent, @Delivered, @TotalOpens, @UniqueUserOpens, @UniqueUserMessageClicks, @UniqueUserLinkClicks, @TotalLinkClicks, @Unsubscribes, @Bounces, @SpamComplaints, @Cost, @DataFeedID, @TagID)SET NOCOUNT OFF this is the one to clear: SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[spCRMPublisherSummaryClear]( @SiteID INT, @DataFeedID INT, @ReportDate SMALLDATETIME) AS DELETE LandingSiteSummary WHERE SiteID=@SiteID AND ReportDate=@ReportDatebut it doesnt seem to be working.Please suggest.
i am trying to delete rows where a particular column (hours) has the same value for the same member (primary key) but where the effective dates are different. i want to delete the duplicate(s) rows which have the most recent effective date(s).
I know this question has been asked before but this is a little different and I can't seem to get my head around it right now. What i have is a table like so:
ID1 ID2 DESC 100 24 something1 100 24 this is a test 100 24 100 25 somethingelse 101 36 something 101 37 something else altogether
What i need is to determine which ID1 value has the same ID2 value listed more than once WITH the description filled in. If there is no description filled in even though the ID2 may be listed twice on one ID1, then it's ok and don't want it displayed in the query.
So, in this case, ID1 of 100 has ID2 24 listed twice with a description on both ID2's (of 24). this is what I need to show up in the query like so:
ID1 ID2 DESC 100 24 something1 100 24 this is a test
Could someone give me a hand with this? I've found similiar problems...but haven't been able to apply those answers to this.
I have written this script: What I want to do now is, with the new JOINED table find and display all the duplicates custID WHERE the price is either 100 OR 200. Can anyone help? I am very new to all this and can't see how to do it. Thanks!
SELECT * FROM table_customer T1 INNER JOIN table_itemsBought T2 ON T2.custID = T1.custID WHERE price = '100' OR price = '200';
HI, I keep getting this warning with a lookup (full cache mode) that retreives data form a table that contains the following information:
SRCE_SYS TABLE_NAME FIELD_NAME CODE ENGLISH_DESCRIPTION STATIC STATIC PM_PC_TX_TYPE_CODE G GROSS STATIC STATIC PM_PC_TX_TYPE_CODE E EXCESS STATIC STATIC PM_PC_TX_TYPE_CODE F FACULTATIVE STATIC STATIC PM_PC_TX_TYPE_CODE S SURPLUS STATIC STATIC PM_PC_TX_RIDER_CODE BOAT BOAT STATIC STATIC PM_PC_TX_RIDER_CODE RIDER RIDER STATIC STATIC PM_PC_TX_RIDER_CODE CONT CONTENTS
The column tab matches SRCR_SYS, TABLE_NAME and FIELD_NAME (using constants defined in a derived column transform) .The code column comes from the source. We want to retreive the english_description colum from SRCR_SYS, TABLE_NAME,FIELD_NAME and CODE in the dataflow.
I would normally ignore the warning but sometimes, it seems that the lookup does not match any values and enabling memory restriction on the advanced tab resolve the issue and suppress the warning.
As I said, I keep getting this warning and I don't know why since there are no duplicates in the table? Am I missing something?
We have a table with 1 million rows with duplicates in a column which allows nulls.Can we enforce uniqueness for only future inserts by anyway(ignoring the old ones)? Thanks!
Is it possible in SQL to Restrict value in one table checking a value on anather tables.
Scenerio-1.
I have two table let say, Teb1 and Teb2. Teb1 has a column called- Business_type and Teb2 has a coulmn called Incorporated_date. I just need to restrict If the value of Business_type column in Teb1 is "Propritory" then Incorporated_date in Teb2 should not be blank (nulll) . Otherwise it can take null value.
Scenerio -2.[/B]
I have table called [B]SIC.
This table has a two column called SIC1 anc SIC2 . Is it possible to restrict that clumn SIC1 and SIC2 should have same values( duplicate values cannot be entered in both columns.
Hi there,I would like to know how to get rows with duplicate values in certaincolumns. Let's say I have a table called "Songs" with the followingcolumns:artistalbumtitlegenretrackNow I would like to show the duplicate songs to the user. I considersongs that have the same artist and the same title to be the same song.Note: All columns do not have to be the same.How would I accomplish that with SQL in SQL Server?Thanks to everyone reading this. I hope somebody has an answer. I'vealready searched the whole newsgroups, but couldn't find the solution.
Is there a setting in SQL Server that ensures a column is not allowed to have the same value more than once? Or must this be set up in the insert statment itself? Or how about a business rule?
Hi i have a junction table(UserGroups) which is linking my "users" table with my "groups" table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change.
UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: AFC Below is my SQL statement; SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')
There's some SQL below (T-SQL) & I'm wanting to have this result set grouped by Venue_ID in order to remove rows where there are duplicate values contained in just one column.
The columns BCOM_ID contain unique values, but Venue_ID can have duplicate values. I only want to get rows for one instance of the Venue_ID (per BCOM_ID) - doesn't matter which instance but basically, no duplicates.
Oh yes, one of the columns is a Bit column.
Any ideas would be welcome & appreciated!
Many thanks, Darren darren@darrenbrook.fsnet.co.uk
SQL:-
SELECT Booking_Header.BH_ID, Booking_Header.Booking_Header_Description, Booking_Header.BStat_ID, Booking_Header.BT_ID, Booking_Header.Tagged, Booking_Header.Status_Timestamp, Booking_Header.Start_Date, Booking_Header.Days_Qty, Proposal.PPL_ID, Proposal.PPL_Status, Booking_Component.BCOM_ID, Booking_Component.Component_Description, Booking_Component.Venue_ID, Venue.Venue_Code, Venue.Description, Address.Address_ID, Address.Town, Booking_Status.BStat_Description, Booking_Type.Type_Description FROM dbo.Booking_Header INNER JOIN dbo.Proposal ON dbo.Booking_Header.BH_ID = dbo.Proposal.BH_ID INNER JOIN dbo.Booking_Component ON dbo.Proposal.PPL_ID = dbo.Booking_Component.PPL_ID INNER JOIN dbo.Venue ON dbo.Booking_Component.Venue_ID = dbo.Venue.VE_ID INNER JOIN dbo.Address ON dbo.Venue.VE_ID = dbo.Address.VE_ID INNER JOIN dbo.Booking_Status ON dbo.Booking_Header.BStat_ID = dbo.Booking_Status.BStat_ID INNER JOIN dbo.Booking_Type ON dbo.Booking_Header.BT_ID = dbo.Booking_Type.BT_ID WHERE (dbo.Proposal.PPL_Status = 1) AND (dbo.Booking_Header.BH_ID = 10)
Hi i have a junction table(UserGroups) which is linking my "users" table with my "groups" table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change.
UserName: Edwin Carols UserAge: 28 JobTitle: Manager GroupName: AFC
Below is my SQL statement;
Code:
SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')
I have a set of data in which i have a product number going through 6 stages and each stage has a date. Since the each stages are in columns, I have created a unpivot query to transpose the columns into rows.
The unpivot query is working fine however I am getting duplicate values in the result. For each productnumber there must be only 6 results however i am getting 24 rows for each product number due to duplication.
I have attached the code and the source data for reference
I have a database that has an Identity field in one table which has duplicate values. how can this be? and is there any way that this can be adjusted or corrected. (this field is used as the primary key).....UG!
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)
I have a stored procedure that returns a single row based on a parameter of employee ID. This particular procedure uses a CTE to traverse our org structure. One of the columns is returning a delimited string of Windows login values and due to the business rules, it can contain duplicate values. I need to have that column contain only unique values - no dupes.
For example, this one column could contain something like this:
With the data example below I am trying to consolidate the duplicate rows by flattening the dealer and billcode, or putting those values in each of the columns instead of creating separate rows...
74 MARTHA PATNE RIPLEY 1 23,327,76 ROTTINGDAM AAC SPRINGFIELD 3052 USA MPATRIP@AMERICANALARM.COM,MPATRIP@COMCAST.NET,PRIPLEY@ONECOMMUNICATIONS.COM
Required output: parameters are Subject column values
ClassId ClassName TotalStudents SubjectIds
1 ESL 1 20, 40, 50 1 ESL 1 30
When one student takes multiple subjects then count student only once, so in the above data 12 is one student id takes multiple subjects so counted only once. TotalStudents value is 1
I did write below query:
Declare @subjectids string set @subjectids = '20,30,40,50'
-- will split @subjectids and store in temp table
select classname, classid, Count(Distinct StudentId) from mytable where SubjectsIds in @subjectIds group by ClassId, ClassName, SubjectId,
I need write a query for removing duplicates, for Example in my table I have columns
A_ID name id 1 sam 10 2 sam 10 3 sam 10 4 sam 10 5 ccc 15 6 ccc 15 7 ccc 15 8 fff 20 9 fff 20 10 fff 20
So now I have duplicates values in id column so now I need to take only one value of each and delete the remaining. I need to take first id value 10,15,20 so only 3 rows should be there in my table.
i have a website that accepts users on it. first the user will apply to make use of my site and the data that he supplied will be put to account table. my problem is how can i get the last inserted identity value lets say id, to create the id of the person applied by simply incrementing it... i dont want to use the built in function of the sql server. can anybody help me of this process. asap...
