insert - help!

Question

insert - help!

Jude Lynn

SSC Veteran

Points: 279
More actions
August 4, 2008 at 4:49 am

#188878

Hi
This is probably far too easy a question to post but I've got myself completely confused and the more I look at BOL, the worse it gets!
Basically I need to do a query that compares two tables: tblRegistrations and tblStudent_Course. Any rows that appear in tblStudent_Course but not in tblRegistrations need to be inserted into tblRegistrations.
This is my code so far:
insert into tblRegistrations
sELECT c.academic_year, c.faculty, c.faculty_description,
c.department_code, c.department_description, c.course_level_code, c.location_of_study, c.location_of_study_description,
c.franchise_flag, c.source_of_funding_hesa,
c.Residential_Status_Code, c.residential_status_description,
c.Academic_Year, c.Course_Code,
c.course_mode_of_study_hesa
FROM tblStudent_Course c FULL OUTER JOIN
tblNew_Entrants n ON c.Course_Code = n.Course_Code AND
c.Max_Course_Year = n.Year_of_Study AND
c.Academic_Year = n.Academic_Year AND
c.Student_ID_Number = n.Student_ID_Number
where not exists (Select r.academic_year, r.faculty_code, r.faculty_description,
r.department_code, r.department_description, r.course_level, r.location_code, rs.location_description,
r.franchised, r.funding_type,
r.Residential_Status, rs.residential_status_description,
r.Academic_Year, r.Course_Code, r.mode_of_study
from tblRegistrations r)
If anyone has any suggestions, I'd be really grateful!
Jude

Viewing 12 posts - 1 through 11 (of 11 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Jude, what do you want to do with tblNew_Entrants? How does it fit into your description?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jude Lynn SSC Veteran Points: 279 More actions · Answer 2

I need to find all the records that exist in tblStudent_course and tblNew_entrants and then find if they exist in tblRegistrations, any that are not already in tblRegistrations need to be put in there.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

So if Student_ID_Number exists in tblStudent_course but not in tblRegistrations then you want to insert the appropriate columns from tblStudent_course into tblRegistrations;

and if Student_ID_Number exists in tblNew_entrants but not in tblRegistrations then you want to insert the appropriate columns from tblNew_entrants into tblRegistrations;

Is this correct? And you want to do this with one statement?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jude Lynn SSC Veteran Points: 279 More actions · Answer 4

not quite, I want to find all the records that exist in both tblstudent_course and tblNew_entrants. I then want to see if any of that set appear in tblRegistrations. Any that don't, need to be inserted into tblRegistrations from tblStudent_course. I don't need the info from tblNew_entrants, just to know if the record exists there. Basically a studnet course record will either be referenced in tblNew_Entrants or another table called tblReturners, so I use the tblNew_entrants join to establish the right type of row before looking for it in tblRegistrations. Does that make sense? I'm starting to confuse myself!!! 😀

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

--not quite, I want to find all the records that exist in both tblstudent_course and tblNew_entrants.

SELECT c.Course_Code, c.Max_Course_Year, c.Academic_Year, c.Student_ID_Number

FROM tblStudent_Course c

INNER JOIN tblNew_Entrants n

ON c.Course_Code = n.Course_Code

AND c.Max_Course_Year = n.Year_of_Study

AND c.Academic_Year = n.Academic_Year

AND c.Student_ID_Number = n.Student_ID_Number

--I then want to see if any of that set appear in tblRegistrations.

SELECT c.Course_Code, c.Max_Course_Year, c.Academic_Year, c.Student_ID_Number

FROM tblStudent_Course c

INNER JOIN tblNew_Entrants n

ON c.Course_Code = n.Course_Code

AND c.Max_Course_Year = n.Year_of_Study

AND c.Academic_Year = n.Academic_Year

AND c.Student_ID_Number = n.Student_ID_Number

LEFT JOIN tblRegistrations r

ON c.Course_Code = r.Course_Code

AND c.Max_Course_Year = r.Year_of_Study

AND c.Academic_Year = r.Academic_Year

AND c.Student_ID_Number = r.Student_ID_Number

WHERE r.Student_ID_Number IS NULL

--Any that don't, need to be inserted into tblRegistrations from tblStudent_course.

INSERT INTO tblRegistrations (column list)

SELECT (column list)

FROM tblStudent_Course c

INNER JOIN tblNew_Entrants n

ON c.Course_Code = n.Course_Code

AND c.Max_Course_Year = n.Year_of_Study

AND c.Academic_Year = n.Academic_Year

AND c.Student_ID_Number = n.Student_ID_Number

LEFT JOIN tblRegistrations r

ON c.Course_Code = r.Course_Code

AND c.Max_Course_Year = r.Year_of_Study

AND c.Academic_Year = r.Academic_Year

AND c.Student_ID_Number = r.Student_ID_Number

WHERE r.Student_ID_Number IS NULL

--I don't need the info from tblNew_entrants, just to

--know if the record exists there. Basically a studnet course record will either be referenced in tblNew_Entrants

--or another table called tblReturners, so I use the tblNew_entrants join to establish the right type of

--row before looking for it in tblRegistrations. Does that make sense? I'm starting to confuse myself!!!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jude Lynn SSC Veteran Points: 279 More actions · Answer 6

thanks, I can't quite get it to work, the only thing I can think of is that the columns have slightly different names in the two tables, would that be causing the problem do you think? Is there any way of getting round this without having to rename the columns?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Hi Jude

Can you please post the code you ran, with the error message?

Please also post:

The list of columns you want populated in the registration table

The list of columns from the source table which are inserted into the registration table from tblStudent_Course

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jude Lynn SSC Veteran Points: 279 More actions · Answer 8

Here's my code:

INSERT INTO tblRegistrations (r.academic_year, r.faculty_code, r.faculty_description, r.department_code, r.department_description,

r.course_level, r.location_code, r.location_description, r.franchised,

r.funding_type, r.Residential_Status, r.residential_status_description, r.Course_Code, r.mode_of_study)

SELECT (c.academic_year, c.faculty, c.faculty_description, c.department_code, c.department_description, c.course_level_code,

c.location_of_study, c.location_of_study_description, c.franchise_flag, c.source_of_funding_hesa, c.Residential_Status_Code,

c.residential_status_description, c.Course_Code, c.course_mode_of_study_hesa)

FROM tblStudent_Course c

INNER JOIN tblNew_Entrants n

ON c.Course_Code = n.Course_Code

AND c.Max_Course_Year = n.Year_of_Study

AND c.Academic_Year = n.Academic_Year

AND c.Student_ID_Number = n.Student_ID_Number

LEFT JOIN tblRegistrations r

ON c.Course_Code = r.Course_Code

AND c.Max_Course_Year = r.Year_of_Study

AND c.Academic_Year = r.Academic_Year

AND c.Student_ID_Number = r.Student_ID_Number

WHERE r.Student_ID_Number IS NULL

the column names are:

tblStudent_Course: c.academic_year, c.faculty, c.faculty_description, c.department_code, c.department_description, c.course_level_code,

c.location_of_study, c.location_of_study_description, c.franchise_flag, c.source_of_funding_hesa, c.Residential_Status_Code,

c.residential_status_description, c.Course_Code, c.course_mode_of_study_hesa

tblRegistrations: r.academic_year, r.faculty_code, r.faculty_description, r.department_code, r.department_description,

r.course_level, r.location_code, r.location_description, r.franchised,

r.funding_type, r.Residential_Status, r.residential_status_description, r.Course_Code, r.mode_of_study

The error message is:

Server: Msg 170, Level 15, State 1, Line 4

Line 4: Incorrect syntax near ','.

Thanks!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Test the SELECT on its own:

SELECT c.academic_year, c.faculty, c.faculty_description, c.department_code,

c.department_description, c.course_level_code, c.location_of_study, c.location_of_study_description,

c.franchise_flag, c.source_of_funding_hesa, c.Residential_Status_Code, c.residential_status_description,

c.Course_Code, c.course_mode_of_study_hesa

FROM tblStudent_Course c

INNER JOIN tblNew_Entrants n

ON c.Course_Code = n.Course_Code

AND c.Max_Course_Year = n.Year_of_Study

AND c.Academic_Year = n.Academic_Year

AND c.Student_ID_Number = n.Student_ID_Number

LEFT JOIN tblRegistrations r

ON c.Course_Code = r.Course_Code

AND c.Max_Course_Year = r.Year_of_Study

AND c.Academic_Year = r.Academic_Year

AND c.Student_ID_Number = r.Student_ID_Number

WHERE r.Student_ID_Number IS NULL

- always do this!

The destination column list doesn't recognise table aliasing, it doesn't need to because you can only update one table in a single INSERT statement.

INSERT INTO tblRegistrations (

academic_year, faculty_code, faculty_description, department_code,

department_description, course_level, location_code, location_description,

franchised, funding_type, Residential_Status, residential_status_description,

Course_Code, mode_of_study)

SELECT ...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jude Lynn SSC Veteran Points: 279 More actions · Answer 10

Jude Lynn

SSC Veteran

Points: 279

August 4, 2008 at 8:35 am

#852718

That's working now, thanks very much!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

You're welcome Jude, thanks for the feedback 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden