I have an issue where I am getting an error on an unique index.
I know why I am getting the error but not sure how to get around it.
The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.
But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.
It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.
How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.
Here is the situation i am stuck with, see the example first and below explained the problem:
-- 'SESSION A
create table foo (
id integer,
pid integer,
data varchar(10)
);
begin transaction
insert into foo values ( 1, 1, 'foo' )
insert into foo values ( 2, 1, 'bar' )
insert into foo values ( 3, 1, 'bozo' )
insert into foo values ( 4, 2, 'snafu' )
insert into foo values ( 5, 2, 'rimrom' )
insert into foo values ( 6, 2, 'blark' )
insert into foo values ( 7, 3, 'smeg' )
commit transaction
create index foo_id_idx on foo ( id )
create index foo_pid_idx on foo ( pid )
begin transaction
insert into foo values ( 9, 3, 'blamo' )
-- 'SESSION B
begin transaction
select id, data from foo with ( updlock, rowlock ) where id = 5;
-- Problem:
-- Uncommitted transaction in session A, with insert into table FOO, aquires lock on index foo_pid_idx which BLOCKS select with ( updlock, rowlock ) in session B.
-- Insert should aquire only exclusive rowlock. Why does insert block select with ( updlock, rowlock )?
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.
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?
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
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'.
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
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
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.
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
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)
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.
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
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
Now i am trying to insert a duplicate copy test by passing testId..Here is my sp
if not EXISTS(SELECT testName from tblTest WHERE UPPER(@testName) = UPPER(@testName)) BEGIN INSERT INTO tblTest SELECT @userId, testName,duration,totalQuestion,termsCondition,0,GETUTCDATE(),GETUTCDATE() from tblTest WHERE id=@testId SET @insertedTestId=@@identity INSERT INTO tblTestQuestion SELECT @insertedTestId,question,0,GETUTCDATE(),GETUTCDATE() from tblTestQuestion WHERE testId=@testId END
how to insert in answer table as one question can be multiple answers.
My query wants to insert new supplier if there is any. And it should ignore, if the supplier is already present in the table. But it is trying to insert the supplier which is already available. For example, I have PART A with 2 suppliers ABC and DEF. I am getting data from third party for PART A with supplier DEF. As per the condition, it should ignore the record because DEF is already available . But my query is trying to insert supplier DEF and following that, I am getting primary constraint error.
-- Inserting new preferred supplier into R5CATALOGUE
DECLARE @DATEPROCESS DATETIME; SET @DATEPROCESS = CAST(DATEADD(D, -((DATEPART(WEEKDAY, GETDATE()) + 1 + @@DATEFIRST) % 7), GETDATE()) AS DATE) INSERT INTO R5CATALOGUE(CAT_PART, CAT_SUPPLIER,CAT_GROSS,CAT_LEADTIME,CAT_PURUOM,CAT_REF,CAT_MULTIPLY,CAT_CURR,CAT_SUPPLIER_ORG, CAT_PART_ORG,CAT_DESC,CAT_MINORDQTY)
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
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
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
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?
One of my database transaction log backup jobs failed this morning, although the log backup was still taken, the job reported failure. The job has run successfully a number of times since. The job reported a primary key violation error on the msdb.dbo.backupmediaset table. The full error message was
Violation of PRIMARY KEY constraint 'PK__backupme__DAC69E4D599F1693'. Cannot insert duplicate key in object 'dbo.backupmediaset'. The duplicate key value is (401862). Msg 3009, Level 16, State 1, Server myservername, Line 1
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
After investigating the backupset and backupmediaset tables in msdb I can see that value used for the failed insert was used previously for the last database log backup in the previous log backup run. The failure was reported for the first database log backup attempted. The SQL Log shows an entry for the log being backed up successfully.
I have checked the identify value in the backupmediaset table using DBCC CHECKIDENT and all looks OK.
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?
I have a table with 0 records. When I try to insert records using a SP, it gives the following error. Violation of UNIQUE KEY Constraint 'constraint name'. Cannot insert duplicate key in object 'Objectname'. How do I resolve this. Thanks.
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.
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.
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:
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
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.
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]
hi need help how to do this INSERT INTO SELECT FROM table + add one row for evry employee (on insert)
this is table tb_test1 empid fname unit fld1 fld2 fld3 fld4 fld5 ---------------------------------------------------------------------------------------- 111 aaa 1 a b c d d 222 bbb 3 a c e g g 333 cccc 5 s h t u j
Code Snippet
INSERT INTO [nili].[dbo].[tb_test2]
([empid]
,[fname]
,[unit]
,[fld1]
,[fld2]
,[fld4]
,[fld5])
SELECT [empid]
,[fname]
,[unit]
,[fld1]
,[fld2]
,[fld4]
,[fld5]
FROM [nili].[dbo].[tb_test1]
i need to insert into tb_test2 and ADD one row for evry employee val_orginal=1 (the orginal ROW) val_orginal=2 (the extra ROW)
this is table tb_test2 after the insert HOW TO THIS
empid fname unit fld1 fld2 fld3 fld4 fld5 val_orginal ------------------------------------------------------------------------------------------------- 111 aaa 1 a b c d d 1 111 aaa 1 - - - - - 2 222 bbb 3 a c e g g 1 222 bbb 3 - - - - - 2 333 cccc 5 s h t u j 1 333 cccc 5 - - - - - 2
hello friends my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn) 'you should use sproc instead cmd.Parameters.AddWithValue("@UserId", textbox1.text) 'your value Try conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery() conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString())) Catch sex As SqlExceptionThrow sex Finally If conn.State <> Data.ConnectionState.Closed Then conn.Close() End If End Try MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource() SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)" SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0 Try rowsaffected = SglDataSource2.Insert()Catch ex As Exception Server.Transfer("yardim.aspx") Finally SglDataSource2 = Nothing End Try If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx") ElseServer.Transfer("urunsat.aspx") End If
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.
