What is Wrong ! Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

What is Wrong ! Help

Author

Topic

isjord
Starting Member

38 Posts

Posted - 2006-01-14 : 15:27:05

INSERT INTO IS_REGISTERED VALUES
(54907,2715,'I-2001')

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__IS_REGISTERED__629A9179'. Cannot insert duplicate key in object 'IS_REGISTERED'.
The statement has been terminated.

How do I fix this? I am trying to insert this into the IS_REGISTERED Table.

Student_ID Section_ID Semester
38214 2714 I-2001
54907 2714 I-2001
54907 2715 I-2001
66324 2713 I-2001

IS_REGISTERED (Student_ID, Section_ID, Semester)

albanie

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-14 : 16:19:00

INSERT INTO IS_REGISTERED (Student_ID, Section_ID, Semester)
select 54907,2715,'I-2001'

am assuming the first two (Student_ID, Section_ID) are integer values

see books online

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-14 : 16:49:32

Follow the instruction in my hint link, BUT, very simply, you already have a row in the table for the Primary Key. What is the PK btw?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

isjord
Starting Member

38 Posts

Posted - 2006-01-14 : 17:09:26

INSERT INTO IS_REGISTERED (
(Student_ID, Section_ID, Semester),
select (54907,2715,'I-2001')

)Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ','.

albanie

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-14 : 17:25:14

1. What is the primary key as Brett asked ?
2. Why put the double delimiters ?

Would advice you paste your table structure here

INSERT INTO IS_REGISTERED
(Student_ID, Section_ID, Semester),
select (54907,2715,'I-2001')

Books online
Inserting Rows Using INSERT...SELECT
The SELECT subquery in the INSERT statement can be used to add values into a table from one or more other tables or views. Using a SELECT subquery also lets more than one row be inserted at one time.

This INSERT statement inserts into a separate table some of the data from all the rows in titles whose type is modern cooking:

USE pubs
INSERT INTO MyBooks
SELECT title_id, title, type
FROM titles
WHERE type = 'mod_cook'

The select list of the subquery must match the column list of the INSERT statement. If no column list is specified, the select list must match the columns in the table or view being inserted into.

isjord
Starting Member

38 Posts

Posted - 2006-01-14 : 18:52:15

Is this what you are asking? I do not understand.

Object dbo.PK_IS_REGISTERED_629A9179

albanie

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-14 : 20:01:14

Script your table to show the table structure or ( post the DDL of the table)

Something like this

CREATE TABLE [dbo].[Users] (
[UserID] [int] IDENTITY (1001210100, 1) NOT NULL ,
[Username] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Passwords] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[First_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Last_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address2] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[State] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Phone] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[DOB] [datetime] NULL ,

[Sex] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Nationality] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
GO

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-14 : 21:52:50

quote:
Originally posted by isjord

INSERT INTO IS_REGISTERED (
(Student_ID, Section_ID, Semester),
select (54907,2715,'I-2001')

)Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ','.

albanie

The correct syntax for the INSERT with a SELECT was already posted. Why didn't you just follow that?


INSERT INTO IS_REGISTERED
(Student_ID, Section_ID, Semester)
select 54907,2715,'I-2001'

The original problem you had was trying to insert a row that would produce a duplicate of the PRIMARY KEY in the table. For example, if the PRIMARY KEY is made of columns Student_ID, Section_ID and Semester, the insert will fail because there is already a row with Student_ID = 54907, Section_ID = 2715, and Semester = 'I-2001'.

The easiest fix is to just not try to insert the duplicate. That is probably the best solution for now, until you know more about what you are trying to do.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources