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
 insert - help!

Author  Topic 

obscuregirl
Starting Member

41 Posts
Posted - 2008-08-04 : 06:48:18
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

elancaster
A very urgent SQL Yakette

1208 Posts
Posted - 2008-08-04 : 07:09:54
can you explain the full outer join to tblNew_Entrants? you are only selecting fields from the tblStudent_Course table so presumably you could end up with duplicates (join to new_entrants more than once?) and nulls (from the outer join?)

simplifying your original requirement, if you just want records from 1 table that aren't in another do it like this...

select t1.col1, t1.col2 (etc....)
from t1 left join t2 on t1.idcol = t2.col2
where t2.idcol is null


Em
Go to Top of Page

obscuregirl
Starting Member

41 Posts
Posted - 2008-08-04 : 07:16:09
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.
Go to Top of Page

elancaster
A very urgent SQL Yakette

1208 Posts
Posted - 2008-08-04 : 07:25:27
that sounds more like a normal inner join between course and new_entrants then surely?

try something along these lines (obviuosly not finished )


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 
				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 r.academic_year = c.academic_year
				  AND r.faculty_code = c.faculty_code
				  AND r.faculty_description = c.faculty_description
				  etc.... (getting bored )
where r.faculty_code is null



Em
Go to Top of Page

obscuregirl
Starting Member

41 Posts
Posted - 2008-08-04 : 09:37:52
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?
Go to Top of Page

elancaster
A very urgent SQL Yakette

1208 Posts
Posted - 2008-08-04 : 09:50:32
depends what you mean when you you can't get it to work. show us the query you've actually written now, and the ddl for your tables

Em
Go to Top of Page

obscuregirl
Starting Member

41 Posts
Posted - 2008-08-04 : 09:57:35
Um, not sure what ddl means!

This is the query:
 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.Course_Code 
			,c.course_mode_of_study_hesa 
FROM         tblStudent_Course c 
				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 r.academic_year = c.academic_year
				  AND r.faculty_code = c.faculty
				  AND r.faculty_description = c.faculty_description
				  and r.department_code = c.department_code 
				and r.department_description = c.department_description 
				and r.course_level = c.course_level_code 
				and r.location_code = c.location_of_study 
				and r.location_description = c.location_of_study_description 
				and r.franchised = c.franchise_flag 
				and  r.funding_type = c.source_of_funding_hesa 
				and r.Residential_Status = c.Residential_Status_Code 
				and r.residential_status_description = c.residential_status_description  
				and r.Course_Code = c.Course_Code 
				and r.mode_of_study = c.course_mode_of_study_hesa
where r.faculty_code is null


the error message I'm getting is:
Server: Msg 213, Level 16, State 4, Line 21
Insert Error: Column name or number of supplied values does not match table definition

Thanks!
Go to Top of Page

elancaster
A very urgent SQL Yakette

1208 Posts
Posted - 2008-08-04 : 10:02:17
forget the insert for a minute and run the select bit. does it give you the records you want?

for the insert you need to list the columns you are inserting into (to match the column's you've selected)


...and DDL means the structure of your tables

Em
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-04 : 10:48:48
quote:
Originally posted by obscuregirl

Um, not sure what ddl means!

This is the query:
 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.Course_Code 
			,c.course_mode_of_study_hesa 
FROM         tblStudent_Course c 
				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 r.academic_year = c.academic_year
				  AND r.faculty_code = c.faculty
				  AND r.faculty_description = c.faculty_description
				  and r.department_code = c.department_code 
				and r.department_description = c.department_description 
				and r.course_level = c.course_level_code 
				and r.location_code = c.location_of_study 
				and r.location_description = c.location_of_study_description 
				and r.franchised = c.franchise_flag 
				and  r.funding_type = c.source_of_funding_hesa 
				and r.Residential_Status = c.Residential_Status_Code 
				and r.residential_status_description = c.residential_status_description  
				and r.Course_Code = c.Course_Code 
				and r.mode_of_study = c.course_mode_of_study_hesa
where r.faculty_code is null


the error message I'm getting is:
Server: Msg 213, Level 16, State 4, Line 21
Insert Error: Column name or number of supplied values does not match table definition

Thanks!

make sure you're number of columns of table is same as number of values passed.
Go to Top of Page

obscuregirl
Starting Member

41 Posts
Posted - 2008-08-04 : 10:53:57
I've got it working now, thanks for your help.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site