INSERT INTO problem

Question

Post reply

INSERT INTO problem

cdun2

SSCertifiable

Points: 7893
More actions
November 1, 2002 at 4:31 pm

#158215

Hello,
Maybe it is just because it is Friday, but I cannot get what seems should be a very simple INSERT INTO statement to work. It looks like this:
*********************************
Insert into tblRCStudentGrades
Permnum as RCpermnum
select Permnum as SDpermnum
from Student_Data_Main
Where grade = '00'
or grade = '01'
or grade = '02'
or grade = '03'
or grade = '04'
or grade = '05'
and
Permnum Not In (Select Permnum From tblRCStudentGrades)
*********************************
When I attempt to run this code, I get a syntax error that points to the second line of the code.
The only field that the two tables share in common is 'Permnum'. What I want to do is to use this code in a stored procedure to insert a 'Permnum' into tblRCStudentGrades if it is in Student_Data_Main but not in tblRCStudentGrades.
tblRCStudentGrades is a new table that has 235 fields in it, has no records in it, and I want to populate the 'Permnum' field in this table with 'Permnum' from Student_Data_Main per the 'Where' condition.
I hope I have provided enough information, please let me know if you have any suggestions. Thanks.
CSDunn

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 1

Try

Insert into tblRCStudentGrades(RCpermnum)

select Permnum as SDpermnum

from Student_Data_Main

Where grade = '00'

or grade = '01'

or grade = '02'

or grade = '03'

or grade = '04'

or grade = '05'

and

Permnum Not In (Select Permnum From tblRCStudentGrades)

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Antares686 SSC Guru Points: 125444 More actions · Answer 2

A couple of things for the NOT IN you are using Pernum, this has been found in the past that the value will be that of the outer query when values are the same name.

Also, you do not need to alias your Column Name but it will help to alias the tables a bit.

FUrthermore, it is not nesseccary to do INSERT INTO, just INSERT works fine and instead of having so many OR use an IN list which is the same as OR options but much shorter and concise.

Finally NOT IN will do a comparison and read of all data in the tblRCStudentGrades table for each row in Student_Data_main which will severlly slow the overall process. A better set based solution is to use LEFT join and cull the records of no match where the left side table has not match (IS NULL).

This should give you, your result with best readability and optimal performance, outside of index related changed.

Insert tblRCStudentGrades(Permnum)

Select Permnum

from Student_Data_Main sdm

left join

tblRCStudentGrades rcsg

on

sdm.Pernum = rcsg.Permnum

Where grade in ('00','01','02','03','04','05')

and rcsg.Pernum is null

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 3

Need to be careful, using left join. If there can be > 1 record in tblRCStudentGrades for a particular Pernum then your query might be better off using NOT EXISTS.

When ever you have this scenario you need to try all the options, NOT IN, LEFT JOIN and NOT EXISTS.

Insert tblRCStudentGrades(Permnum)

Select Permnum

from Student_Data_Main sdm

WHERE NOT EXISTS (SELECT 1

FROM tblRCStudentGrades rcsg

WHERE sdm.Pernum = rcsg.Permnum)

AND grade in ('00','01','02','03','04','05')

Ensure Pernum is indexed on tblRCStudentGrades

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

David Burrows SSC Guru Points: 65143 More actions · Answer 4

The use of IN/NOT IN with sub queries seems to be a common theme.

I have found LEFT OUTER JOINS to be a lot faster than NOT IN (SELECT...)

I would try this although performance may be an issue depending on volume.

CREATE TABLE #tmp (Permnum int NOT NULL PRIMARY KEY CLUSTERED)

INSERT INTO #tmp

SELECT DISTINCT Permnum

FROM Student_Data_Main

WHERE grade in ('00','01','02','03','04','05')

INSERT tblRCStudentGrades (Permnum)

SELECT t.Permnum

FROM #tmp t

LEFT OUTER JOIN Student_Data_Main sdm

ON t.Permnum = sdm.Permnum

WHERE sdm.Permnum IS NULL

Far away is close at hand in the images of elsewhere.
Anon.