and generating a report from an SQL table, and need to know how to exclude records that are "duplicates". Not duplicates in a sense that every field is identical, but duplicates in a sense where everything except the unique identifier is identical. Is there a quick and easy way to do this?
Hi All, I`m using BCP to import ASCII data text into a table that already has many records. BCP failed because of `Duplicate primary key`. Now, is there any way using BCP to know precisely which record whose primary key caused that `violation of inserting duplicate key`. I already used the option -O to output error to a `error.log`, but it doesn`t help much, because that error log contains the same error message mentioned above without telling me exactly which record so that I can pull that `duplicate record` out of my import data file. TIA and you have a great day. David Nguyen.
I have a Transform Data Task that copies a lot of data from my source system. Unfortunately, I cannot use a DISTINCT in the SQL from the source system, due to a very poor ODBC driver! So, when I am creating my primary key, I am trying to do a lookup on the PK column before I insert the record to see if it exists. If it does, then I skip the row. The lookup references the target database of the task.
The problem I have is that the lookup doesn't find any duplicates loaded from the database. It allows them through and causes the database to throw a primary key error.
Has anyone experienced this, or think they know what I'm doing wrong?
I get the following error when I try to insert a stored procedure in an SQL-server.
Violation of PRIMARY KEY constraint 'PK_Login'. Cannot insert duplicate key in object 'Login'.
My question if this is the real problem or the symtom of something else? I find it hard to believe that try to insert double key values. The table Login doesn´t contain any values.
How can I avoid duplicate primary key error when I use DetailsView Inserting that the field column is one of the primary key ? Thanks in advance ! stephen
I am putting my problem in an example as I Feel it would be clear.
Assume my table PEOPLE is having 4 columns with 6 rows, the SlNo being primary key. SlNo Name LastName birthdate 1 A B x -- 2 C B x |-- 1 pair (A, B, x) 3 D E y --|------------ 4 A E y | | 5 A B x __| |-- 2'nd pair (D, E, y) 6 D E y --------------- In this scenario, I need to find SlNo values having similar values in other columns. The o/p for above must be: 1 5 0 3 6 0 (0 needs to include in output for distinction in the sets)
(a)IS THIS POSSIBLE TO DO IN ONE SELECT STATEMET? and HOW? (b)If I create another temp table tempPEOPLE and select distinct row information of the 2'nd, 3'rd and 4'th columns from the PEOPLE table and then selecting SlNo's where the information match, I am able to get o/p 1 5 3 6 without 0...and I cannot makeout the distinct sets in this. HOW DO I FIND THE DISTINCTION IN SETS?
when i save this table modifying the pubid and pubcode as primary keys the following error displays...
Unable to create index 'PK_PUBS3'. CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'. Could not create constraint. See previous errors. The statement has been terminated.
what i understand is that on the primary key duplicates are not allowed how could i allow it?
I have one table that stores log messages generated by a web service. I have a second table where I want to store just the distinct messages from the first table. This second table has two columns one for the message and the second for the checksum of the message. The checksum column is the primary key for the table.
My query for populating the second table looks like: INSERT INTO TransactionMessages ( message, messageHash ) SELECT DISTINCT message, CHECKSUM( message ) FROM Log WHERE logDate BETWEEN '2008-03-26 00:00:00' AND '2008-03-26 23:59:59' AND NOT EXISTS ( SELECT * FROM TransactionMessages WHERE messageHash = CHECKSUM( Log.message ) )
I run this query once per day to insert the new messages from the day before. It fails when a day has two messages that have the same checksum. In this case I would like to ignore the second message and let the query proceed. I tried creating an instead of insert trigger that only inserted unique primary keys. The trigger looks like:
IF( NOT EXISTS( SELECT TM.messageHash FROM TransactionMessages TM, inserted I WHERE TM.messageHash = I.messageHash ) ) BEGIN INSERT INTO TransactionMessages ( messageHash, message ) SELECT messageHash, message FROM inserted END
That didn't work. I think the issue is that all the rows get committed to the table at the end of the whole query. That means the trigger cannot match the duplicate primary key because the initial row has not been inserted yet.
I have 3 source for IS flow. One is flat file, one is DB table and one is output bad data. It might be a situation when I could have duplicate primary key since records come from 3 sources (flat file, db table, reject (output) table). Can any one give me suggestion how to handle duplicate primary key problem in this situation.
We have a SQL Server 6.5 table, with composite Primary Key, having the Duplicate Entry for the Key. I wonder how it got entered there? Now when we are trying to import this table to SQL2K, it's failing with Duplicate row error. Any Help?
'm trying to import a text file but the primary key column contains duplicatres (tunrs out to be the nature of the legacy data). How can I kick out all duplicates except, say, for a single primary key value?
the point here that i have a small table with two fileds, ID (guid) as primerykey RAF(char) and the table is empty when i add a new row i recieve this exception, Violation of PRIMARY KEY constraint 'PK_tblType'. Cannot insert duplicate key in object 'dbo.tblType'. i found no way to solve the problem. thanks in advans
I have table variable in which I am inserting data from sql server database. I have made one of the columns called repaidID a primary key so that a clustered index will be created on the table variable. When I run the stored procedure used to insert the data. I have this error message; Violation of Primary key Constraint. Cannot insert duplicate primary key in object. The value that is causing this error is (128503).
I have queried the repaidid 128503 in the database to see if it is a duplicate but could not find any duplicate. The repaidID is a unique id normally use by my company and does not have duplicates.
I want to import a data file into a sql table. The table has a primary key but the data could have a duplicate value in the PK column (error in the source data). How can I "trap" for this type of error in SSIS?
I'm trying to figure out the best way to approach this seemingly simple task in Reporting Services 2005, but have come up with nothing but frustration. Any help would be appreciated!
Here's the basic idea:
SELECT Server.Database1.Employee, Server.Database1.HomeDept, Server.Database2.WorkedDept FROM Server.Database1, Server.Database2
I need to exclude certain combinations of HomeDept/WorkedDept (about 11)...such as 1015/2223, 1015/2226, 2002/4422, 2002/8742, 2002/2342, etc. I want any other combinations of HomeDept 1015 & 2002 to be selected (which would be hundreds).
I've tried:
WHERE (Server.Database1.HomeDept <> '1015' AND Server.Database2.WorkedDept NOT IN ('2223','2226')) AND Server.Database1.HomeDept <> '2002' AND Server.Database2.WorkedDept NOT IN ('4422','8742','2342'))
(and all combinations of NOT IN, <>, and parenthesis placement...I even tried to do each exception separately)
What happens is that all instances of HomeDept 1015 and 2002 and all instances of WorkedDept 2223, 2226, 4422, 8742, and 2342 are excluded.
The SQL Query Designer will remove parenthesis so that the HomeDept exclusion is processed separately from the WorkedDept.
I've tried to do the SQL in the Generic Query Designer, so the SQL Query Designer doesn't reformat it, and it still returns the same results.
This behavior only seems to happen when using <> for both, NOT IN for both, or a combo of NOT and <>. If I used = for the HomeDept, the exclusion of the WorkedDept worked fine.
The script basically maps ALL products to customer levels. The Delete statement at the start is needed to make sure that any deleted products or temporary mapped products are removed once a week. However I want to map some products to CustomerID's 9,10 and 14 only. But when I run this statement it maps all products to all customer levels. What I want is thisAll products mapped to customer levels 9,10 and 14Then every other product mapped to customer levels 0,5,7, and 8 but not those extra ones in 9,10 and 14.
DELETE FROM ProductCustomerLevel WHERE CustomerLevelID IN (0, 5, 7, 8, 9, 10, 14) INSERT ProductCustomerLevel ( ProductID, CustomerLevelID
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
I would like to exclude any parameter that is empty from the SELECT command? How do I do this? This is part of a stored procedure.
SELECT PersonID FROM Persons WHERE (FirstName = @firstname) AND (LastName = @lastname) AND (SSN = @ssn) AND (AddressID = @addressid) AND (DOB = @dob) AND (Middle = @middle)
Does anyone know how to write code to tell the amount of days between 2 dates excluding Saturday and Sunday. Datediff will tell me the total amount of days and I guess I need some logic to look at the startdate and manipulate the total after the fact.
I'm having a problem writing a SQL query that excludes certain data. This is for a pay stub application to display current and previous paycheck stubs. To calculate certain data such as YTD figures and time off, we SUM on other tables. However, to display correctly, I can't SUM bonus checks for the current payperiod ONLY - but for previous pay periods, I must SUM bonus checks.
Here's an example of my data:
No code has to be inserted here. No code has to be inserted here.
No code has to be inserted here. No code has to be inserted here.
Right now my SQL is this:
Code: SELECT PR04PTF.PayCheckNo, SUM(PR11ERF_History.PayCheckAmt) AS [TotalSum] FROM PR04PTF
INNER JOIN PR11ERF_History ON PR11ERF_History.EmployeeID = PR04PTF.EmployeeID AND PR11ERF_History.PayPeriodEnd <= PR04PTF.PayPeriodEnd
Suppose there is a table containing these recodes.country-------CON_CHNCON_JAPJAPCON_CHNWhen I use the following sql:select country, count(*) as num from table group by countrythe normal result will be:country num---------------CON_CHN2CON_JAP 1JAP1However, my desired result is as follows:country num-----------------CON_CHN2CON_JAP 2How can I re-write my SQL? Or any other methods to do that?
I have rows coming from the db including: Contract Number, Contract Name, owner and Actions associated with each contract.
SQL statement brings back: Contract Number Contract Name Sales Owner Action 1234 123453 $50 Neil x 1234 123453 $50 Bob y 534232 5464634211 $30 Harry z
The problem is that each contract can have multiple actions associated with it... There ideal output would be:
Contract Number Contract Name Sales Owner Action 1234 123453 $50 Neil x Bob y 534232 5464634211 $30 Harry z
Total: $80
Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..
I have to do various controls on a dataset - I created a multicast. After performing controls (one control per copy), I merge my (7) multicasted datasets using a Union All transformation. The problem I'm having are the duplicate rows created by merging the multicast copies.
How do I get rid of the duplicates? Is the Sort Transformation the solution by setting the option Remove rows with duplicate sort values to True? I have a unique key by which I'm able to discard the duplicates correctly. Are there any other ways (at a Union All level)? Is there sth like Union and Union All like in SQL?
I'm working on my 1st integration serv. project and it seems that more I work more questions I have. Shoudn't be the opposite? Thank you for the help.
i have a field with 2 characters(many of them ) and spaced inbetween them which are codes for something
ie XA OX YY BY CY DY XC XD OE In any or all combination
i want to make a count of rows which contains BOTH XA BUT DOES NOT CONTAIN any one or all of XC,XD & OE
SO I write the query select count(colname) from tablename where colname LIKE ('%XA%') AND colname NOT LIKE ('%XC%') OR colname NOT LIKE ('%XD%') OR colname NOT LIKE ('%OE%') ')
iS this correct why then it does not exclude the XC,XD,OE
I inherited a report that counts patient visits per month. Holidays are hard coded into the query. What is the best way to handle holidays without hardcoding?
One of our departments once to automate a query that they have to pull data from the previous day. We are going to set this up as a job. How can we do this without using the Saturday and Sunday dates? So what we want to do is Pull the data from Friday on Monday. Is this possible? This is what they have. I know this pull the data from the day before.
