Duplicate Entry In A Primary Key Field
Hi everybody couldn't get through with saving my data on the table with two primary keys...
my table structure is this
pubidintUnchecked (primary key)
pubchar(1)Unchecked
publchar(1)Unchecked
pubcodechar(2)Unchecked (primary key)
a sample data is here
pubid pub publ pubcode
1 a b ab
1 b b bb
2 a b ab
2 b b bb
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?
thanks
View Complete Forum Thread with Replies
Related Forum Messages:
Table With Duplicate Entry With Primary Keys
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?
View Replies !
Duplicate Entry
Hi All, I have a table with name C1_Subscribers with three fields (1)-MobileNumber [varchar] (2)-ReceivedTime [datetime] (3)-Status [char]. Now here how to remove duplicate entry of same mobile number to MobileNumber field? Regards Shaji
View Replies !
How Duplicate Data Can Make An Entry????
Hi, I want to know the different sources, mediums and ways using which the duplicate recoprds or adta can make an entry in our database tables. I found 4 ways for this from many articles on net that are as follows: Duplicate data might arrive at your database via an interface to another system Data is loaded into table from other sources because during data loads, the integrity constraints are disabled Merging data from disparate systems Inheriting a poorly designed databaseBut is there any other ways also present????? Please tell me??? Thanks,
View Replies !
Preventing Duplicate Entry For A Given Foreign Key In Db Table
Hi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..
View Replies !
Removing Duplicate Rows With CTE And Partition - Need Most Recent Entry
I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry? /*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table by Chuck King 20070928 ***/ ;WITH CTE as ( SELECT ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID ,DischDate ,AdmDiagCode ,ID ,AdmDiagCodeDesc ,AdmittingDiagnosis ,AnyDx ,DischDx ,ECode ,IntOOS ,PrinDxAnesDesc ,PrinDxAnesInd ,PrinDxCode ,PrinDxCondDesc ,PrinDxCondInd ,PrinDxDesc ,PrinDxEqualsAdmDxYnu ,SecDx10AnesDesc ,SecDx10AnesInd ,SecDx10Code ,SecDx10CondDesc ,SecDx10CondInd ,SecDx10Description ,SecDx11AnesDesc ,SecDx11AnesInd ,SecDx11Code ,SecDx11CondDesc ,SecDx11CondInd ,SecDx11Description ,SecDx12AnesDesc ,SecDx12AnesInd ,SecDx12Code ,SecDx12CondDesc ,SecDx12CondInd ,SecDx12Description ,SecDx13AnesDesc ,SecDx13AnesInd ,SecDx13Code ,SecDx13CondDesc ,SecDx13CondInd ,SecDx13Description ,SecDx14AnesDesc ,SecDx14AnesInd ,SecDx14Code ,SecDx14CondDesc ,SecDx14CondInd ,SecDx14Description ,SecDx15Code ,SecDx15Description ,SecDx1AnesDesc ,SecDx1AnesInd ,SecDx1Code ,SecDx1CondDesc ,SecDx1CondInd ,SecDx1Description ,SecDx2AnesDesc ,SecDx2AnesInd ,SecDx2Code ,SecDx2CondDesc ,SecDx2CondInd ,SecDx2Description ,SecDx3AnesDesc ,SecDx3AnesInd ,SecDx3Code ,SecDx3CondDesc ,SecDx3CondInd ,SecDx3Description ,SecDx4AnesDesc ,SecDx4AnesInd ,SecDx4Code ,SecDx4CondDesc ,SecDx4CondInd ,SecDx4Description ,SecDx5AnesDesc ,SecDx5AnesInd ,SecDx5Code ,SecDx5CondDesc ,SecDx5CondInd ,SecDx5Description ,SecDx6AnesDesc ,SecDx6AnesInd ,SecDx6Code ,SecDx6CondDesc ,SecDx6CondInd ,SecDx6Description ,SecDx7AnesDesc ,SecDx7AnesInd ,SecDx7Code ,SecDx7CondDesc ,SecDx7CondInd ,SecDx7Description ,SecDx8AnesDesc ,SecDx8AnesInd ,SecDx8Code ,SecDx8CondDesc ,SecDx8CondInd ,SecDx8Description ,SecDx9AnesDesc ,SecDx9AnesInd ,SecDx9Code ,SecDx9CondDesc ,SecDx9CondInd ,SecDx9Description ,VisitTypeCode ,accountnumber ,DischVisitTypeCode FROM ampfm.rpt_AdtVisitDiag ) --Select * From CTE Delete From CTE Where ROWID > 1
View Replies !
How To Prevent A Second Entry Being Inserted With Primary Key Value? (C#)
Ok, this is a really stupid question, but I can't seem to find an answer I understand. In my SQL database I have a a table called MasterSkillList, to which the user can write by using a little web form with a text box and a drop down list. The table has 2 fields, Skill and Attribute. Skill is the primary key, as no skill can appear twice. What I want to do is prevent just that, I don't want people to enter the same skill more than once. So how do I tell the user that the entry allready exists in the database? My C# Code is as follows:1 protected void btnSubmit_Click(object sender, EventArgs e) 2 { 3 srcAddSkill.InsertParameters["Skill"].DefaultValue = txtSkillName.Text; 4 srcAddSkill.InsertParameters["Attribute"].DefaultValue = ddlAbility.SelectedValue; 5 try 6 { 7 srcAddSkill.Insert(); 8 lblErrorMessage.Text = "The skill '" + txtSkillName.Text + "' has been added. It is based on a character's " + ddlAbility.SelectedItem + " score."; 9 lblErrorMessage.Visible = true; 10 txtSkillName.Text = ""; 11 } 12 catch (Exception ex) 13 { 14 lblErrorMessage.Text = "An exception has occurred. " + ex.Message; 15 lblErrorMessage.Visible = true; 16 }
View Replies !
Insert From Formview And Checking Database To Avoid A Duplicate Entry
I have a form view that I am using to insert new data into a sql express database and would like to find a way to avoid attempting to insert a record if the key already exists. is there a way to do this with the formview insert command. Everything works great until I try to add a record with an already existing value in the unique key field, then it breaks.
View Replies !
Duplicate Data Entry When Using The ASP.NET Web Site Administration Tool For Logins
Hello All, I am using the Web Site Administration Tool with the ASP.NET login controls to create/manage user logins & roles for my site. I have exported the ASPNETDB.MDF into my SQL Database ready to use with my app. The problem that I'm having is that whenever I add a user, using either the Web Site Administration Tool or the Classes available in the code-behind. The entry is doubled up in the database. This is not a problem when logging on....however, when it comes to deleting a user it will only take one of the entries out and leave the duplicate in there. So if the administrator has deleted a user from the app and then tried to create another one with the same UserName (Which whomever is well within his/her rights to be able to do), they cannot because the entry which has "stay behind" in the database conflicts with the entry. Any help on this matter would be greatly apprieciated. If any more info on this matter is required please ask (It was quite hard to try and explain this :-)
View Replies !
CREATE TABLE DUPLICATE OBJECT/DUPLICATE FIELD NAME ERROR Msg 2714
Hello Everyone: 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. How do I fix this? TIA
View Replies !
Automatic Field Entry
Hi,I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:IDUserIdDateDescription(UserId is a foreign key to asp_net_Users as I am supporting user accounts)Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).Can anyone point me in the right direction on the steps needed to create this scenario?Thanks for any helpDaniel
View Replies !
Data Entry In A Datetime Field
Hi, I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message - "Invalid Value for cell (row 1, column 2). The changed value in this cell was not recognised as valid. .Net Framework Datatype: Datetime Error Message: Index was outside the bounds of the array. Type a value appropriate for this data type or press ESC to cancel the change." Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message. How do I input data into the field?
View Replies !
Data Entry In A Datetime Field.
My other thread containg this same topic seems to have some error. It doesn't show up in the main 'SQL Server Database Engine' group at all. So I had to start a new thread instead. Here's the link to the original - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1136124&SiteID=1 Here are the contents of the same - Post#1 Orginally posted by me - Hi, I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message - "Invalid Value for cell (row 1, column 2). The changed value in this cell was not recognised as valid. .Net Framework Datatype: Datetime Error Message: Index was outside the bounds of the array. Type a value appropriate for this data type or press ESC to cancel the change." Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message. How do I input data into the field? Post#2 Originally posted by Jens K. Suessmeyer - Hi, you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ? HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Post#3 Originally posted by me - Hi, I use only English as the default language for all programs, OS included. The date in question is not just one particular date, it is any date or time value, and I've been facing this issue ever since I started using SQL Server Express a year ago. Only I haven't used it much since, and need to seriously develop something now, that I bothered to ask the question here. Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) - 1-1-2007 01-01-2007 01/01/2007 01,01,2007 01:01:2007 Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy) I even tried entering time alongside with all those above figures in the format - 11:35 PM 11:35 AM 23:35 The only time value(s) I didn't try was/were - hh:mms tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values. I also tried entering a time value standalone, without the date. Still no go. (Also, as I have gleaned from previous posts, if all goes well and SQL Server accepts your data, then if you enter just a date value in a datetime field, then the field automatically gets populated with the value of the system time at that moment, and if you enter just a standalone time value, then the date part will be filled by the system date value on the date of the entry. Am I correct so far?) Now I'm in a real fix because of the inability to enter date/time values. Could you please help me out? Post#6 Originally posted by me - I don't understand. I can enter the datetime values in all the above mentioned combinations if I use an Insert or Update statement using T-SQL, but I can't seem to enter data in the datetime/smalldatetime fields at all if I try to use the GUI of Management Studio Express to enter data. (right click the table, select show table, and the grid view pops up). Ditto for using GUI tools of Visual Studio Express. Is this a bug in the Visual Studio (Express) software? Also, my other concerns are, once I develop a front-end for data entry using VB, will I face the same issues with data entry in the datetime fields using the GUI of the front end? I can't test that right now, because I'm still learning VB, and cannot create the front end just yet. Post#7 Originally posted by me this afternoon, but it never showed up on the page - Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2. Now I continue to face the same issue. SQL Server will not accept any value for any datetime field, unless it is entered as an SQL insert statement. It won't accept the value entered in the exact same format as the insert statement from the GUI mode of Management Studio Express, nor will it take any values from any GUI developed using VBExpress and the fill dataset method. It throws an error everytime. I don't know any other way of databinding and updating/inserting using VB at this time. I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem? Could someone please help me out? I am stuck and cannot use the product at all, without the datetime field.
View Replies !
BCP And Duplicate Primary Keys
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.
View Replies !
Lookup Allows Duplicate Primary Key
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?
View Replies !
Duplicate Primary Key Error
Hi, 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. Thanks in advance!
View Replies !
Duplicate Row Excluding Primary Key
I am trying to duplicate a row in a table and this is the solution I came up with: CREATE PROC duplicate_row(@my_primary_key) AS DECLARE @sql_stmt varchar(MAX) SET @sql_stmt = 'INSERT INTO My_Table(' SELECT@sql_stmt = @sql_stmt + COLUMN_NAME + ',' FROMINFORMATION_SCHEMA.COLUMNS WHERETABLE_NAME = 'My_Table' ANDCOLUMN_NAME NOT IN ('my_primary_key','title') SET @sql_stmt = @sql_stmt + 'title) SELECT ' SELECT@sql_stmt = @sql_stmt + COLUMN_NAME + ',' FROMINFORMATION_SCHEMA.COLUMNS WHERETABLE_NAME = 'My_Table' ANDCOLUMN_NAME NOT IN ('my_primary_key','title') SET @sql_stmt = @sql_stmt +' title+''_copy'' AS title FROM My_Table WHERE my_primary_key = '+LTRIM(STR(@my_primary_key)) EXEC(@sql_stmt) However this stored proc is horrifically slow. I was wondering if anyone had any suggestions on a better way to accomplish this. -thanks
View Replies !
Finding Primary Key Value(s) Having Duplicate Row Information
Hi, 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? Reshma.
View Replies !
Duplicate Primary Key Issue In SSIS
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. Any suggestion will be appreciated. Thanks..
View Replies !
Ignore Duplicate Primary Keys
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. Does anyone know the best way to do this? Thanks for your help, Drew
View Replies !
Duplicate Primary Keys In Input File
'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? TIA, Barkingdog
View Replies !
Duplicate Entries In A Field
I have a field called RegId. RegId is of datatype NVARCHAR (20). RegId ----- 12322 2122111 23423 etc etc I want to run a query to find out if there are duplicate entries in this field. Any ideas on how I can achieve this? Thanks in advance, Anthony
View Replies !
Trying To Append A Field If An Insert Happens To Contain A Duplicate
I deliberately intend to add some duplicates to one of my tables. For eg Job User IsAdmin JobID 235User ID 1 JobID 235User ID 5 JobID 235User ID 9 JobID 235User ID 5 JobID 235User ID 2 JobID 235User ID 9 JobID 235User ID 10 JobID 235User ID 1 I know its bad practice to do such a thing but there is a genuine reason. What I need to do is to be able to have a SQL statement that appends true to the IsAdmin field whenever it encounters the next UserID thats happens to be a duplicate. Hence the above would look like: Job User IsAdmin JobID 235User ID 1 JobID 235User ID 5 JobID 235User ID 9 JobID 235User ID 5 True JobID 235User ID 2 JobID 235User ID 9 True JobID 235User ID 10 JobID 235User ID 1 True Thanks
View Replies !
Select Records That Do Not Duplicate On A Certain Field.
I am looking for some help in pulling certain people out of a table This is the basic setup of my table PK ID NAME MEETING 1 11111 Joe CLASS98 2 22222 Jane CLASS98 3 33333 Bob CLASS98 4 11111 Joe CLASS04 5 22222 Jane CLASS04 6 44444 Sally CLASS04 What I am wanting to do is Select only the people who attended CLASS98 but didn't attend CLASS04 I could just remove the CLASS04 people by sorting if I could just return the rows that do not duplicate the ID and meeting is either CLASS04 or CLASS98 Either way would get me to my goal. Any help is GREATLY appreciated. Thanks Jimmyjoe
View Replies !
Select Single Row With Duplicate Value In Particular Field
Hi, I have several row records in which name email --------- --------- name1 abc@abc.com name2 abc@abc.com name3 abc@abc.com name4 123@123.com name6 123@123.com How should I write the query which can return records with unique email address with any name attached? The expected record set will be name email --------- --------- <any> abc@abc.com <any> 123@123.com I was thinking to select the distinct of email and just stuck at here. I encountered this problem few times and still cannot solve it. Any help will great appreciated. Thanks in advance.
View Replies !
How To Delete Duplicate Data From Field
my table structure is id field1 1 i am from india 2 i am from usa 3 i am from delhi So i want to remove common data from field1 , means after run the query table should be like id field1 1 india 2 usa 3 delhi thanks in advance saumitra tamrakar
View Replies !
Removing Duplicate Entries In SQL Field
Hi All, Below is a snippet of MS SQL inside some VB that retieves Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense. Below is the SQL and the results from the returned page. PS I tried to use distinct but with no luck I want just one of each in the example below. Thanks in Advance! Scott ============================== SQL = "" SQL = "SELECT B.CIMS_MSDS_NUM," & _ "A.COMMODITY_NUMBER, " & _ "B.CIMS_TRADE_NME," & _ "B.CIMS_MFR_NME," & _ "B.CIMS_MSDS_PREP_DTE," & _ "B.APVL_CDE," & _ "COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _ "COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _ "A.MSDS_CMDTY_VERIF, " & _ "A.CATALOG_ID " & _ "FROM ( MATEQUIP.VMSDS_CMDTY A " & _ " RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _ " ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _ " LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _ " ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _ " LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _ " ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) " SQL1 = "" SQL1 = SQL SQL = SQL & "WHERE " & Where & " " ================================== Here is a piece of the problem field, note repeating colors etc. CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11
View Replies !
Duplicate Entries In Returned SQL Field
Hi All ! Below is a snippet of MS SQL inside ASP that retieves Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get back green, red, blue, and yellow which is correct. On another similar search different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic Glass Sand. I want to remove the repeating elements returned in this field. I hope this makes sense. PS I tried to use distinct but with no luck I want just one of each in the example below. Thanks in Advance! Scott ============================== SQL = "" SQL = "SELECT B.CIMS_MSDS_NUM," & _ "A.COMMODITY_NUMBER, " & _ "B.CIMS_TRADE_NME," & _ "B.CIMS_MFR_NME," & _ "B.CIMS_MSDS_PREP_DTE," & _ "B.APVL_CDE," & _ "COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _ "COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _ "A.MSDS_CMDTY_VERIF, " & _ "A.CATALOG_ID " & _ "FROM ( MATEQUIP.VMSDS_CMDTY A " & _ " RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _ " ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _ " LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _ " ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _ " LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _ " ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) " SQL1 = "" SQL1 = SQL SQL = SQL & "WHERE " & Where & " " ================================== Here is a piece of the problem field, note repeating colors etc. CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS
View Replies !
Check For Duplicate Entries In One Field
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.
View Replies !
SELECT Highest Value Of Duplicate Field
I have a table like TradeID ActionID 58096 3663 58096 3664 78901 2235 78901 2236 I want to select the only the TradeID with the highest ActionID I tried using select distinct tradeid,actionid From cct Where ActionID = (SELECT MAX(ActionID) FROM cct1 WHERE cct1.TradeID = cct.TradeID) group by tradeid,actionid but the result is not correct please help In god we trust,everything else we test.
View Replies !
Primary Key Field
Hi, I want to find out the primary key field name of the table using system table information i.e. sysobjects, syscolumns etc. I tried to find it out but it seems to be very complicated. Could anybody help me in this regards? Thanks in advance. Prasanna.
View Replies !
Bit Field As Primary Key
Hey guys. I just wanted to ask you a question? we are having a field declared as BIT and we want that field to be unique..We are not able to declare as a primary key..Is there anyway to declare as unique from the database point of view?..please help us out in this issue and i'd appreciate that. Andrew.
View Replies !
New Primary Key Field
Does anyone has an idea on creating a new field in a table with more then say 10000 rows already in that table, and making that new field as primary key field. None of the fields in that table are unique.
View Replies !
How To Get A New Value For A Primary Key Field
Hello, I have an app that is connected to SQL 2005 express edition. In the DB there is a table that has an auto incremental primary key What I want is, I want to get a new value for that auto incremental field before sending the new record's date to the DB to be stored. So I can display the new record's ID in my app's interface So is there something in SQL server like SEQUENCE in oracle. Thanks in advance.
View Replies !
Query Returning Duplicate Field Names
I have a .NET program that can connect to either an Access 97 database or anSQL Server 7 database. In the database I have two tables which have a fieldcalled ID. When I run a query like "SELECT A.*, B.* FROM A, B", the queryreturns those fields as "A.ID" and "B.ID" when connected to Access 97, butas "ID" and "ID" in SQL Server 7. Is there anyway to get SQL Server toprepend the table name to the field name in a case like this and not returnduplicate field names like that without having to specify aliases for thefields?- Don
View Replies !
Group By Query To Find Duplicate Field Value
Hello, Not sure how to do this. I think I need to use a Group By query. I have a "Products" table with the following fields: Program# Number UPC Item Item# Size Dept Everything is specific to the Program#. In other words, for every instance of a Program# there can be more than one Product, which is specified by the "Number" field. So: SELECT * FROM Product WHERE [Program#] = '12345' should return this: 12345 | 1 | 000012345678 | Cookies | 98765 | 12ct | Retail | 12345 | 2 | 000012345678 | Cake | 98765 | 12ct | Retail | 12345 | 3 | 000012345678 | Ice Cream | 98765 | 12ct | Retail | However, some recordsets are returning like this: 12345 | 1 | 000012345678 | Cookies | 98765 | 12ct | Retail 12345 | 1 | 000012345678 | Cake | 98765 | 12ct | Retail 12345 | 2 | 000012345678 | Ice Cream | 98765 | 12ct | Retail In which case I have to fix them (the "Number" field) with an update query sp they are numbered sequentially. Luckily, this doesn't happen very often. Can someone help me with a query that will return only those records with duplicate values in the "Number" field? I am not sure how to construct this query which I will use as a stored procedure. TIA, Bruce Wexler Programmer/Analyst
View Replies !
.dbf File Import (duplicate Field Names)
I am importing a file creating by an application which exports the file into .dbf format. Very unfortunately, this .dbf file can have fields with IDENTICAL column_names. Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver. However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names. Can anyone make recommendations here that might help? Oh, this is SQL200 in case that impacts what you might advise!!!!
View Replies !
Multi-field Primary Key
I have a table (table1) that has a bunch of fields....[field1][field2][field3][field4][field5]None of these fields are unique, but if I combine them, then they areunique.I know there is a way to make multi-field primary keys, but when I tryI get an that field1 is not unique, which I already know,How can I make a multi-field primary key?thanks
View Replies !
Using An Identity Field As Primary Key
I want to use the Identity field (increment 1,1) as a primary key andhave a unique constraint on my other field which is of type char.I am worried that related data in other tables may lose referntialintegrity if records in the ID table get messed up and need to bere-entered.Can you please advice on best way to do this. I definitely need anumeric id field because it makes the joins and queries so muchfaster.
View Replies !
How To Auto Increment A Primary ID Field?
I was just wondering on a very simple database table with lets say a primary key set to columb ID and another columb lets say products, can you make the primary key automaticly increment its self whenever a new entry has been put in?For instance say I have this table set up with ID Being the primary KEY, Columb 1 = ID( INT ), Columb 2 = Products ( VarChar(50) ), and have the fields ID = 1, and products = my product.....and if a user inserts a new record say from a gridview or some sort of data entry the second ID Feild will automaticly be 2 and the products gets updated per user input.......I'm very sorry but I'm having a hard time putting this into words for some reason..umm basicly user adds something into the products feild and the ID field automaticly increments one number higher from the last one?ThanksAdam.
View Replies !
Automating Incrementation In Primary Key ID Field
I'm really puzzled with this. I am new to using MS SQL and need help with the primary key. When I was using Access, I would open the database table and just enter values for the various columns, each time a new column was begun, the computer would insert a unique number there. OK, so now I am using visual studio to work on a SQL database. I created a new table and the first field I specified was "ID", I then chose it as the primary key. For default value, I tried both auto number and auto increment. These don't seem to work, as when I type values into fields and try to move to the next row, having left the ID field blank, I get an error which reads "String or Binary data may be truncated". What is going on, what do I need to do so the field automatically populates with an ID number... Some research sort of led me to the concept of a "trigger" to do this. Is this the approach. I was able to r-click on the table and I saw an option for "create trigger" or "new trigger"... How do I learn to do this, is there a generic trigger to do an auto increment of a primary key ID field?
View Replies !
Insert Values In Primary Key Field
Hi, Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5. Thanks in advance for any reply.
View Replies !
Problem With Coping Primary Key Into Other Field In The Same Table
is there any way that i can copy primary key(PK) value to another field in the same table .. say my PK is MemberID i want to replicate that into other field say SortID at the same time when the primary key is incrementing. I'm a newbie in this field please pardon me if something is wrong in the way i'm asking ...please help me friends i'm struggling since a long time ... Thanks in advance .. savvy
View Replies !
Identity Primary Field In Merge Replication
Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity. What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e On Server 1 - Values start from 1 then 2,3,4 etc etc Server 2 - 24001, 24002, 24003 etc etc Server 3 - 46001, 46002, 46003 etc etc Server 4 - 68001, 68002, 68003 My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc? Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app. cheers
View Replies !
|
TRACKER
