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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
Append A Field
Hello, How do I append data on an update? I have a table with a field that is nVarchar(1000) and the initial insert is a few sentences. If I wanted to add to that row using an update statement and without starting at the end of the sentences, how would I write that? Update table set fieldname = 'more data' where value = @variable instead of Update table set fieldname = 'initial data more data' where value = @variable and the 'more data' appends to the initial data... hmmm help please.
View Replies !
SQL 2000 (How To Append To A Text Field?)
Okay, I know this is probably a pretty common question, but I haven't been able to find a solid answer (thus why I'm writing this post). I have a table with a text field, I do not know the lengths of any of them (normally I would record this size in a seperate field, but this is a legacy app I'm working on). I would like to append some text (also fairly large) to the end of the existing text fields. My question is how do I know where the end is? I know I can declare a pointer to the text field, but that only get's me the start. How do I go about finding the end? And once I do know the end, is UpdateText enough to get the job done? What if I need to append data to the begining of the text field? From what I've read it seems like UpdateText will overwrite existing data, not insert the new text at the given index. I really hope someone can help me out here. I'd like to do this all within SQL if possible (i.e. without setting up an ASP page or something to help process the data).
View Replies !
Append Counter Variable To Field Name
Hi all, I have a table with fields name Days1, Days2, Days3 - I am trying to use a loop in conjunction with a counter to identify each of these fields - I can't quite get the correct syntax and it is driving me crazy!!! Here's the proc: WHILE @Counter < 4 BEGIN SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey) FROM tbl_SurgerySlot INNER JOIN tbl_SurgerySlotDescription ON tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode AND tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label LEFT OUTER JOIN tbl_Appointment ON tbl_SurgerySlot.SurgerySlotKey = tbl_Appointment.SurgerySlotKey AND tbl_SurgerySlot.ExtractDate = tbl_Appointment.ExtractDate WHERE (tbl_SurgerySlot.ExtractDate = @ExtractDate) AND (tbl_Appointment.AppointmentKey IS NULL) AND (tbl_SurgerySlot.StartTime > @DateFrom) AND (tbl_SurgerySlot.StartTime < @DateTo) AND (tbl_SurgerySlotDescription.IsBookable = 1) SET @FieldName = 'Days' + CONVERT(VARCHAR(20),@Counter) INSERT INTO tmp_Availability (@FieldName) VALUES (@AppointmentsCount) SET @DateTo = DATEADD(Day,1,@DateTo) --Increment the loop counter SET @Counter = @Counter + 1 When I run the above the follwoing message is displayed: Server: Msg 208, Level 16, State 3, Line 36 Invalid object name 'tmp_Availability'. The object IS valid so I'm lost....
View Replies !
Append String To Text Field....
I am trying to append some text to a full text field in SQL Server 2000. I tried this; but, it didn't work: UPDATE DefendantEventPros SET EventComment=EventComment + ' This event was completed on "& Date() &".' WHERE EventNumber="& eventnumber &" I get an error saying: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid operator for data type. Operator equals add, type equals text. How can I append some text to the end of a field? Thanks for any help!
View Replies !
Why Adox Can Not Append AdUnsignedBigInt Field ?
my code: Code Block void AppendTableTest() { // Define ADOX object pointers, initialize pointers. These are in ADOX namespace. _CatalogPtr m_pCatalog = NULL; _TablePtr m_pTable = NULL; try { HRESULT hr = S_OK; TESTHR(hr = m_pCatalog.CreateInstance(__uuidof(Catalog))); // Open the catalog m_pCatalog->PutActiveConnection("Provider='Microsoft.JET.OLEDB.4.0';data source='c:\new.mdb';"); //m_pCatalog->Tables->Delete("MyTable"); TESTHR(hr = m_pTable.CreateInstance(__uuidof(Table))); m_pTable->PutName("MyTable"); m_pTable->Columns->Append("Column1",adInteger,0); m_pTable->Columns->Append("Column2",(DataTypeEnum)21,8); m_pTable->Columns->Append("colBinary",adBinary,8); //m_pTable->Columns->Append("Column3",adVarWChar,50); m_pCatalog->Tables->Append(_variant_t((IDispatch *)m_pTable)); printf("Table 'MyTable' is added."); // Delete the table as this is a demonstration. //m_pCatalog->Tables->Delete("MyTable"); printf("Table 'MyTable' is deleted."); } catch(_com_error &e) { // Notify the user of errors if any. _bstr_t bstrSource(e.Source()); _bstr_t bstrDescription(e.Description()); TRACE(" Source : %s description : %s ", (LPCSTR)bstrSource, (LPCSTR)bstrDescription); } but i got a invalid type error. I cannot understand. why adox can not append adUnsignedBigInt field ? Somebody can give me a answer? Thanks.
View Replies !
Insert Or Append?
Hello all,Number of rows in a table was increased significantly. Is there a way to seewhether data was inserted in to the table, or whether data was appended tothe table?Thanks in advance,Do.--Message posted via http://www.sqlmonster.com
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 !
INSERT INTO... SELECT... Cannot Insert Duplicate Key...
I want to add the content of a table into anotherI tried to copy all fields, except the primary key:INSERT INTO table2(field2, field3, field4, ...)SELECT field2, field3, field4, ...FROM anotherDB.dbo.table1gives the following error:Violation of UNIQUE KEY constraint...Cannot insert duplicate key...Why?I didn't ask him to copy the key column; Isn't the SQL Server supposedto know how to increment the key ?
View Replies !
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 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 !
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 !
Cannot Insert Duplicate Key
I am using SQL Server 2000. I have a table (tbDupes) with duplicate records. I want to populate another table (tbUnique) with unique records. My tbDupes has about 50 columns and three of them make up my primary key in tbUnique. I thought I could get unique records by using the following SQL statement. Code Snippet INSERT INTO tbUnique SELECT * FROM tbDupes I received this error: Cannot insert duplicate key Is there a database setting that will allow me to insert the first record and prevent any duplicates from being inserted? I thought this was the default behavior and think that maybe the DBA changed it. I know I can achieve this by creating a cursor with tbDupes and then something like "IF NOT EXISTS(...) THEN INSERT". However, I would prefer to avoid using a cursor.
View Replies !
Cannot Insert Duplicate Key Row In Msrepl_transactions
We have an issue that our SQL Replication between our Application Server (holding 13 months of data) and our Reporting Server (holding 5-6 years of data). Both systems are running on NT4, SQL Server 7 Ent SP3. Our issue is that we used to run SQL Replication but then stopped using it, and used another method of replicating our data from the App Svr to Rpt Svr. However, we have now been told that we have to use SQL Replication. We set it up as usual, but when the log reader starts up, it finds the 1st transaction and moves it into the distribution database, but it then fails when attempting to do the 2nd transaction with the error "Cannot insert duplicate key row in object MSrepl_transactions with unique index ucMSrepl_transactions. We have checked the tables in the publisher db and they don't hold any duplicates, so I can only assume the key it is talking about is the key field in the distribution database, but this is a Binary field which I believe is populated by replication itself!! Is this correct? If so, why is it trying to enter duplicates? And can we resync replication to fix it?
View Replies !
Don't Insert Duplicate Rows
Hi, I need to insert rows into table1 from table2 and table3 but I don't want to insert repeated combinations of col2, col3. So, table1 has the primary key col2, col3. This the table1: create table table1( col1 int not null, col2 int not null, col3 int not null, constraint PK_table1 primary key (col2, col3) ) This is my "insert" code: INSERT INTO table1 SELECT table2.col1,table2.col2, table3.col3 FROM table2, table3 WHERE table2.col1 = table3.col1 Wich conditions shoud i add to this code? Thanks. fmilano.
View Replies !
Cannot Insert Duplicate Key Row In Object
The following quote is the exact error message that I get when I attempt to create a pull subscription. The STAppointment is one of a 15 databases that need to replicate inorder for the application SalonTouch to run properly. What is causing this error? Is it user rights? Is it a problem with sql2005? Am I missing a step? Is there a problem with the database I am working with? What else? TITLE: New Subscription Wizard ------------------------------ SQL Server could not create a subscription for Subscriber 'D6LHWQ91PORTSUN9'. ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Cannot insert duplicate key row in object 'dbo.MSmerge_replinfo' with unique index 'uc1MSmerge_replinfo'. The subscription could not be created. The subscription properties table 'MSsubscription_properties' does not exist in the current database. Changed database context to 'STAppointment'. The statement has been terminated. (Microsoft SQL Server, Error: 2601) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=2601&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
View Replies !
Can I Insert/Update Large Text Field To Database Without Bulk Insert?
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View Replies !
Preventing Duplicate Rows On Insert
I have a table using an identity column as its Primary Key and twocolumns (table reduced for simplicity) EmployeeNumber and ArrivalTime.CREATE TABLE [tblRecords] ([ID] [bigint] IDENTITY (1, 1) NOT NULL ,[EmployeeNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,[ArrivalTime] [datetime] NOT NULL ,CONSTRAINT [PK_tblRecords] PRIMARY KEY CLUSTERED([ID]) ON [PRIMARY]) ON [PRIMARY]GOI have an insert procedure that checks for duplicates before insertinga new record:IF (SELECT TOP 1 [ID] FROM tblRecords WHERE EmployeeNumber =@SocialSecurity) IS NULLBEGININSERT INTO tblRecords(EmployeeNumber,ArrivalTime)VALUES (@EmployeeNumber, @ArrivalTime)SELECT SCOPE_IDENTITY()ENDELSESELECT 0 AS DuplicateRecordIn 99.9% of the cases, this works well. However, in the event that theinsert attempts are literally "ticks" apart, the "SELECT TOP 1..."command completes on both attempts before the first attempt completes.So I end up with duplicate entries if the procedure is called multipletimes vey quickly. The system needs to prevent duplicateEmployeeNumbers within the past 45 days so setting the EmployeeNumberto UNIQUE would not work. I can check for older entries (45 days ornewer) very easily, but I do not know how to handle the times when theprocedure is called multiple times within milliseconds. Would aTRANSACTION with a duplicate check after the INSERT with a ROLLBACKwork in this case? Any help is greatly appreciated!-E
View Replies !
Conditional Insert - Duplicate Key Issue
I am in the process of migrating an old access database into a new SQL Server 2000 database with a modified structure. Tables: [Chapters][Worksite][Employers] I want to insert into the chapters table a ChapterID(PK), EmployerID(FK), and ChapterName. All of the selected information is coming from the [Worksite] table. However, my conditional is based on a third table, the [Employers] table. Below is a copy of a query that returns the result set I would like to insert into my [Chapters] table. Code: Select distinct a.WorksiteCode, b.employerID, a.WorksiteName From Worksite a, employers b Where a.worksiteCode = b.employerWSCode When I run this query it appears to return the proper result set. However, when I turn that query into an insert statement: Code: SET IDENTITY_INSERT Chapters ON Insert into Chapters (chapterID, employerID, chapterName) Select distinct a.WorksiteCode, b.employerID, a.WorksiteName From Worksite a, employers b Where a.worksiteCode = b.employerWSCode SET IDENTITY_INSERT Chapters OFF I recieve the following error message: Server: Msg 2627, Level 14, State 1, Procedure InsertChapterFromFixed, Line 10 Violation of PRIMARY KEY constraint 'PK_Chapters'. Cannot insert duplicate key in object 'Chapters'. The statement has been terminated. Anyone have any idea on how I can avoid this error message... either my distinct statement is not function as I expect it to, or I am doing something else wrong. Thanks for any help.
View Replies !
Insert That Drops Duplicate Records
I ought to know how to do this, but it escapes me at the moment. I need to write an insert statement for a table that will be based on a complex select query. The select query may return rows that are already in the target table. In that case, I don't want duplicates created, but I don't want the query to error, either. I can't remember how to set that up.
View Replies !
Cannot Insert Duplicate Key In Object 'dbo.RunningJobs'
I'm using SSRS 2005 on a Server 2003 machine. Some of my reports run fine but others take a long time to run and sometimes fail with an http error 503. After a report errror happens I find entries in the event log that says: cannot open a connection to the report server When I look at the log files for Reporting services I see an error caused by a primary key violation on the Running Jobs Table: Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs' Does anyone know why do I get such errors? Bellow is the error message in the logs: w3wp!library!6!21/08/2006-09:30:22:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.RunningJobsDb.AddRunningJobs(Hashtable runningJobs)
View Replies !
Cannot Insert Duplicate Key In Object 'dbo.RunningJobs'
We continue to recieve he following error on our RS 2005 SP1 Reporting Services installation: Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs' This causes our server to become unresponsive and some reports to not render, Its becoming quite common. We reset IIS to correct it but is causes a service interuption. I know that it had been talked about before as something that may be fixed in SP2. But I do not see this referenced. Does anybody know of any fix for this? Thanks Ron
View Replies !
Error Message On Attempted Duplicate Insert
Hi All, I have a web form that inserts information into two tables in sql 2005 database on a submit button click. I have a unique key on the tables preventing duplicate information which works fine. The problem I have is that at the minute if a users tries to insert a duplicate row they just get the built in sql error message. Is there a way I can validate the information before if goes into the database and display perhaps a label telling the user of their error or is there a way I can customize the build in sql error message? I've had a search on various forums and sites and can't find any info that would help me. Thanks in advance Dave
View Replies !
Duplicate Records Are Being Inserted With One Insert Command.
This is like the bug from hell. It is kind of hard to explain, soplease bear with me.Background Info: SQL Server 7.0, on an NT box, Active Server pageswith Javascript, using ADO objects.I'm inserting simple records into a table. But one insert command isplacing 2 or 3 records into the table. The 'extra' records, have thesame data as the previous insert incident, (except for the timestamp).Here is an example. Follow the values of the 'Search String' field:I inserted one record at a time, in the following order (And only oneinsert per item):airplanejetdogcatmousetigerAfter this, I should have had 6 records in the table. But, I endedup with 11!Here is what was recorded in the database:Vid DateTime Type ProductName SearchString NumResultscgcgGeorgeWeb3 Fri Sep 26 09:48:26 PDT 2003 i null airplane 112cgcgGeorgeWeb3 Fri Sep 26 09:49:37 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null cat 75cgcgGeorgeWeb3 Fri Sep 26 09:52:53 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null tiger 14cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64Look at the timestamps, and notice which ones are the same.I did one insert for 'dog' , but notice how 2 'jet' records wereinsertedat the same time. Then, when I inserted the 'cat' record, another'dog' record was inserted. I waited awhile, and inserted mouse, andonly the mouse was inserted. But soon after, I inserted 'tiger', and 2more mouse records were inserted.If I wait awhile between inserts, then no extra records are inserted.( Notice 'airplane', and the first 'mouse' entries. ) But if I insertrecords right after one another, then the second record insertion alsoinserts a record with data from the 1st insertion.Here is the complete function, in Javascript (The main code ofinterestmay start at the Query = "INSERT ... statement):----------------------------------------------------------------------//Write SearchTrack Record ------------------------------------Search.prototype.writeSearchTrackRec = function(){Response.Write ("<br>Calling function writeSearchTrack "); // fordebugvar Query;var vid;var type = "i"; // Type is imagevar Q = "', '";var datetime = "GETDATE()";//Get the Vid// First - try to get from the outVid var of Cookieinctry{vid = outVid;}catch(e){vid = Request.Cookies("CGIVid"); // Gets cookie id valuevid = ""+vid;if (vid == 'undefined' || vid == ""){vid = "ImageSearchNoVid";}}try{Query = "INSERT SearchTrack (Vid, Type, SearchString, DateTime,NumResults) ";Query += "VALUES ('"+vid+Q+type+Q+this.searchString+"',"+datetime+","+this.numResults+ ")";this.cmd.CommandText = Query;this.cmd.Execute();}catch(e){writeGenericErrLog("Insert SearchTrack failed", "Vid: "+vid+"- SearchString:: "+this.searchString+" - NumResults: "+this.numResults, e.description);}}//end-----------------------------------------------------------------I also wrote a non-object oriented function, and created the commandobject inside the function. But I had the same results.I know that the function is not getting called multiple timesbecause I print out a message each time it is called.This really stumps me. I'll really appreciate any help you canoffer.Thanks,George
View Replies !
'Cannot Insert Duplicate Key' Error With Identity Column As PK
I guess there is first for everything...I had never seen error like this before Violation of PRIMARY KEY constraint 'PK_table1'. Cannot insert duplicate key in object 'table1'. The statement has been terminated. In this case Primary Key is Identity column. I do not include Idenity column in Insert statement. Incidently SQL server machines had cluster failure couple of days before. I am not sure whether it has anything to do with it. I see this error randomly. How should I debug it
View Replies !
SP Causes The Error Violation Of UNIQUE KEY Constraint Cannot Insert Duplicate Key
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
View Replies !
Problem Of Duplicate Records With A Simple Insert Statement !
I am using a simple stored proc shown below which inserts a record and updates the same record field called SortID with the Primary Key Column data of the same record.Everything was working fine until few days back. The site has been deployed 2 years back and was LIVE ever since. We have got thousands of records in this Credits table. The table field called SortID is used for moving the credits up and down. My problem is now after 2 years of deploying the table has got duplicate records, suprisingly a same credit is appearing under different MemberID and with the same SortID. How are these duplicate records inserted with a simple insert statement & update ?? Is this a Locking problem or Transaction problem I have no idea. Any ideas will be of great help Thanks in Advance Stored Procedure Used CREATE PROC SP_SC_INSERT (@memberID int,@title varchar(30),@dir varchar(30),@desc varchar(20))ASINSERT INTO [dbo].[Credits]([MemberID],[Title],[Director], [Description], )VALUES(@memberID,@title,@dir, @desc, )UPDATE Credits Set SortID = @@IDENTITY WHERE CreditID = @@IDENTITYGO
View Replies !
Duplicate Last Record When Using SqlDataAdapter.Update For Insert Command
I'm getting duplicate records for the last record in the datatable. No matter how much or how little my datatable contains row records, it always duplicate the last one for some reason. Is there something wrong with my code below? EXAMID pulling from another stored procedure, which is outputed back to a variable. ---Data Access Layer---- If dt.Rows.Count > 0 Then 'INSERT EXAM ROSTERInsertComm = New SqlCommandsqladapter = New SqlDataAdapterInsertComm = New SqlClient.SqlCommand("ExamOfficers_AddOfficerSpecificExamRoster", conndb)InsertComm.CommandType = CommandType.StoredProcedure sqladapter.InsertCommand = InsertCommInsertComm.Parameters.Add("@examid", SqlDbType.Int)InsertComm.Parameters("@examid").Value = examidInsertComm.Parameters.Add("@officerid", SqlDbType.Int, 12, "Officer_UID")InsertComm.Parameters.Add("@reimburse", SqlDbType.Bit, 12, "ReimburseToDb")InsertComm.Parameters.Add("@posttest", SqlDbType.Int, 12, "Post_Test")InsertComm.Parameters.Add("@pqcdate", SqlDbType.DateTime, 12, "pqc_date")InsertComm.Parameters.Add("@pqcscore", SqlDbType.Int, 12, "pqc_score") conndb.Open() sqladapter.UpdateBatchSize = 100InsertComm.UpdatedRowSource = UpdateRowSource.Nonesqladapter.Update(dt) InsertComm.ExecuteNonQuery()InsertComm.Dispose() End If ----Stored Procedure---- ALTER PROCEDURE [dbo].[ExamOfficers_AddOfficerSpecificExamRoster] @ExamID as int,@OfficerID as int,@reimburse as bit=NULL,@posttest as int=NULL,@pqcdate as datetime=NULL,@pqcscore as int=NULL ASBEGIN SET NOCOUNT ON; Insert Into Exam_Officers(EXAM_UID,Officer_UID,reimburse,post_test,pqc_date,pqc_score)values(@ExamID,@OfficerID,@reimburse,@posttest,@pqcdate,@pqcscore) END
View Replies !
Bulk Insert, Skip Rows With Duplicate Key Error?
Does sql server have a way to handle errors in a sproc which would allowone to insert rows, ignoring rows which would create a duplicate keyviolation? I know if one loops one can handle the error on a row by rowbasis. But is there a way to skip the loop and do it as a bulk insert?It's easy to do in Access, but I'm curious to know if SQL Server propercan handle like this. I am guessing that a looping operation would beslower to execute?
View Replies !
Duplicate Tables Insert/Update In Another Table? Triggers?
I want to be able to duplicate every single record that is inserted or updated in a particular table to another table, but not the delete. Is the best way to set-up a trigger? If so can anyone provide me with an example of how to do this? Also could you just duplicate certain columns in the row I would you have to do all columns? Thanks for help.
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 !
Cannot Insert Duplicate Key Row In Object 'MSmerge_genhistory' With Unique Index 'unc1MSmerge_genhistory'
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
View Replies !
Cannot Insert Duplicate Key Row In Object 'dbo.lastlogin' With Unique Index 'IX_lastlogin'.
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]
View Replies !
Cannot Insert Duplicate Key Row In Object 'dbo.blabla..' With Unique Index 'Idx_blablabl'. The Statement Has Been Terminated. At
We 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
View Replies !
|
TRACKER
