INsert Statement Error - 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

SQL Server 2005 Forums

Transact-SQL (2005)

INsert Statement Error

Author

Topic

gavinjb
Starting Member

34 Posts

Posted - 2007-08-21 : 09:13:41

Hi,

I have the following SQL insert statement, that works fine on my test database, but when I run it against my live database (like test a db restored from an old SQL2000 server with a few structual changes) I get the error listed below

SQL


INSERT INTO QoE (StudentRef, Initials, DoB, QualLevel, QualLevelText, Subject, 
SubjectText, Grade, Entered, UserName, AwardDate, ExamBoardText, EnteredYear, 
ShortCourse , ExamBoard, PassedTest, PassedPortfolio) 
SELECT StudentRef, Initials, DoB, QualLevel, QualLevelText, Subject, SubjectText, Grade, 
Entered, UserName, AwardDate, ExamBoardText, '2007/08', ShortCourse , ExamBoard, PassedTest, 
PassedPortfolio FROM QoE WHERE EnteredYear = '2006/07 ' AND StudentRef = '9599487'

Error


Msg 512, Level 16, State 1, Procedure RemoveNoQuals, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Both databases are on the same server and they are identical in structure, can anyone please help me.

Thanks,

Gavin,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 09:25:50

Are any of the columns in the SELECT list highlighted, as a reserved word or something?

INSERT	QoE
	(
		StudentRef,
		Initials,
		DoB,
		QualLevel,
		QualLevelText,
		Subject,
		SubjectText,
		Grade,
		Entered,
		UserName,
		AwardDate,
		ExamBoardText,
		EnteredYear,
		ShortCourse,
		ExamBoard,
		PassedTest,
		PassedPortfolio
	)
SELECT	StudentRef,
	Initials,
	DoB,
	QualLevel,
	QualLevelText,
	Subject,
	SubjectText,
	Grade, 
	Entered,
	UserName,
	AwardDate,
	ExamBoardText,
	'2007/08',
	ShortCourse,
	ExamBoard,
	PassedTest,
	PassedPortfolio
FROM	QoE
WHERE	EnteredYear = '2006/07'
	AND StudentRef = '9599487'

This query do not seem to have a subquery.

Please post all code for SP.

E 12°55'05.25"
N 56°04'39.16"

gavinjb
Starting Member

34 Posts

Posted - 2007-08-21 : 10:01:03

not that I am aware of.

I have managed to fix the issue, by doing a manual work around. Rename QoE Table to QoEBackup and then recreate QoE table from QoEBackup Structure. I then inserted all the data back into the table and my SQL worked. What I dont understand was how on 2 identical tables that one it worked on and one it didn't.

Most like another SQL 2005 bug (I wish we had stayed with SQL2000, we had far less problems).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources