October 13, 2009 at 9:39 am
Hi,
I'm trying to insert data from one table into another using the following query but i'm not sure about this error. I know the insert code is fine and i know the select code is good. I just think having the two together is the issue. Thanks for looking.
INSERT INTO wce_contact (UNIQUEID,contact,firstname,middle_name,lastname,stu_id,dob,
gender,national_insurance_no,Learning_Difficulties,Learning_Diff,Disability,ethnicity,
address1,address2,address3,city,postalcode,phone,emailaddress,mobilephone,STEN_Employer_Identifier,record_type)
with cteDelegates(stu_id, Student_Forename, middlename, Surname, Date_of_Birth, Sex, NI_Number, Learning_Difficulty, Learning_Diff, Disability, Ethnicity, Student_Address_1, Student_Address_2, Student_Address_3, Student_Address_4, Student_Postcode, Telephone, Email_Address, Mobile_Number,STEN_Employer_Identifier, rown) as(Select stu_id, Student_Forename, middlename, Surname, Date_of_Birth, Sex, NI_Number, Learning_Difficulty, Learning_Diff, Disability, Ethnicity, Student_Address_1, Student_Address_2, Student_Address_3, Student_Address_4, Student_Postcode, Telephone, Email_Address, Mobile_Number,STEN_Employer_Identifier, ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY stu_id) from wce_ilr)
Select stu_id, Student_Forename + ' ' + Surname, Student_Forename, middlename,
Surname, stu_id, Date_of_Birth, Sex, NI_Number, Learning_Difficulty, Learning_Diff,
Disability, Ethnicity, Student_Address_1, Student_Address_2, Student_Address_3, Student_Address_4, Student_Postcode, Telephone, Email_Address, Mobile_Number,STEN_Employer_Identifier, 'Learner' AS Learner
from cteDelegates where rown = 1 order by Student_Forename
I did try adding the ; but that error'ed too
Error:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
October 13, 2009 at 9:49 am
You need the CTE above the entire insert statement. This should work.
;with cteDelegates(stu_id, Student_Forename, middlename, Surname, Date_of_Birth, Sex, NI_Number, Learning_Difficulty, Learning_Diff, Disability, Ethnicity, Student_Address_1, Student_Address_2, Student_Address_3, Student_Address_4, Student_Postcode, Telephone, Email_Address, Mobile_Number,STEN_Employer_Identifier, rown) as(Select stu_id, Student_Forename, middlename, Surname, Date_of_Birth, Sex, NI_Number, Learning_Difficulty, Learning_Diff, Disability, Ethnicity, Student_Address_1, Student_Address_2, Student_Address_3, Student_Address_4, Student_Postcode, Telephone, Email_Address, Mobile_Number,STEN_Employer_Identifier, ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY stu_id) from wce_ilr)
INSERT INTO wce_contact (UNIQUEID,contact,firstname,middle_name,lastname,stu_id,dob,
gender,national_insurance_no,Learning_Difficulties,Learning_Diff,Disability,ethnicity,
address1,address2,address3,city,postalcode,phone,emailaddress,mobilephone,STEN_Employer_Identifier,record_type)
Select stu_id, Student_Forename + ' ' + Surname, Student_Forename, middlename,
Surname, stu_id, Date_of_Birth, Sex, NI_Number, Learning_Difficulty, Learning_Diff,
Disability, Ethnicity, Student_Address_1, Student_Address_2, Student_Address_3, Student_Address_4, Student_Postcode, Telephone, Email_Address, Mobile_Number,STEN_Employer_Identifier, 'Learner' AS Learner
from cteDelegates where rown = 1 order by Student_Forename
October 13, 2009 at 9:56 am
Thanks Matt, that did it!! appreciate the help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply