Hi,I have a query that creates a table the selects data from another table and inserts it into the newly created table. The query works fine. The issue that i have is the data from teh selected table has lot's of trailing spaces after the values, for example course_code value when inserted ends' up being "12345678 " instead of "12345678" This happens for all the non numeric fields. I have no control over the source table just this script that gets the data, i'm sure that the source table although it has varchar in this script are actually char types which i think is part of the issue.I have built a script that i can run using the rtrim(field name) so i can get round this issue but i wondered if there was anything that i could build in to this script, maybe a default SQL comand or something that romes the spaces. Thanks for any help.IF EXISTS(SELECT NULL FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name = 'STEPHCRM')DROP TABLE STEPHCRMGOCREATE TABLE STEPHCRM ( Funding_Year smallint NULL DEFAULT(0), LSC_Funding_Stream varchar(2) NULL DEFAULT(' '), Course_Code varchar(20) NULL DEFAULT(' '), Instance_Code varchar(6) NULL DEFAULT(' '), Course_Title varchar(40) NULL DEFAULT(' '), Level varchar(1) NULL DEFAULT(' '), Learning_Aim_Title varchar(150) NULL DEFAULT(' '), Learning_Aim_Code varchar(8) NULL DEFAULT(' '), Awarding_Body varchar(8) NULL DEFAULT(' '), GLH real NULL DEFAULT(0), Annual_GLH real NULL DEFAULT(0), Venue varchar(5) NULL DEFAULT(' '), Company_Name varchar(60) NULL DEFAULT(' '), Company_Code varchar(16) NULL DEFAULT(' '), Stu_ID varchar(12) NULL DEFAULT(' '), Student_Forename varchar(20) NULL DEFAULT(' '), Surname varchar(30) NULL DEFAULT(' '), Middlename varchar(20) NULL DEFAULT(' '), Date_of_Birth datetime NULL, Sex varchar(1) NULL DEFAULT(' '), NI_Number varchar(11) NULL DEFAULT(' '), Learning_Difficulty varchar(2) NULL DEFAULT(' '), Disability varchar(2) NULL DEFAULT(' '), Learning_Diff varchar(1) NULL DEFAULT(' '), Ethnicity varchar(2) NULL DEFAULT(' '), Student_Address_1 varchar(40) NULL DEFAULT(' '), Student_Address_2 varchar(40) NULL DEFAULT(' '), Student_Address_3 varchar(40) NULL DEFAULT(' '), Student_Address_4 varchar(40) NULL DEFAULT(' '), Student_Postcode varchar(10) NULL DEFAULT(' '), Email_Address varchar(50) NULL DEFAULT(' '), Mobile_Number varchar(20) NULL DEFAULT(' '), Telephone varchar(20) NULL DEFAULT(' '), Main_Delivery_Method varchar(2) NULL DEFAULT(' '), Prior_Attainment_Level varchar(2) NULL DEFAULT(' '), Enhanced_Funded varchar(2) NULL DEFAULT(' '), Current_Status varchar(12) NULL DEFAULT(' '), Employment_Status_Begin varchar(2) NULL DEFAULT(' '), Start_Date datetime NULL, Exp_End_Date datetime NULL, Actual_End_Date datetime NULL, Completion varchar(1) NULL DEFAULT(' '), Achievement datetime NULL, EDRS_No varchar(9) NULL DEFAULT(' '), Employer_Address_1 varchar(40) NULL DEFAULT(' '), Employer_Address_2 varchar(40) NULL DEFAULT(' '), Employer_Address_3 varchar(40) NULL DEFAULT(' '), Employer_Address_4 varchar(40) NULL DEFAULT(' '), Employer_Postcode varchar(10) NULL DEFAULT(' '), Date_Created datetime NULL,STEN_Employer_Identifier varchar(16) NULL DEFAULT(' '), Assessor_Trainer varchar(16) NULL DEFAULT(' '))INSERT INTO STEPHCRM ( Funding_Year , LSC_Funding_Stream , Course_Code , Instance_Code , Course_Title , Level , Learning_Aim_Title , Learning_Aim_Code , Awarding_Body , GLH , Annual_GLH , Venue , Company_Name , Company_Code , Stu_ID , Student_Forename , Surname , Middlename , Date_of_Birth , Sex , NI_Number , Learning_Difficulty , Disability , Learning_Diff , Ethnicity , Student_Address_1 , Student_Address_2 , Student_Address_3 , Student_Address_4 , Student_Postcode , Email_Address , Mobile_Number , Telephone , Main_Delivery_Method , Prior_Attainment_Level , Enhanced_Funded , Current_Status , Employment_Status_Begin , Start_Date , Exp_End_Date , Actual_End_Date , Completion , Achievement , EDRS_No , Employer_Address_1 , Employer_Address_2 , Employer_Address_3 , Employer_Address_4 , Employer_Postcode , Date_Created ,STEN_Employer_Identifier, Assessor_Trainer )--CRM SELECT Funding_Year = STEN_Year, LSC_Funding_Stream = STEN_Funding_Stream, Course_Code = STEN_Provision_Code, Instance_Code = STEN_Provision_Instance, Course_Title = PRPI_Title, Level = NOTIONAL_NVQ_LEVEL_CODE, Learning_Aim_Title = LEARNING_AIM_TITLE, Learning_Aim_Code = LEARNING_AIM_REF, Awarding_Body = AWARDING_BODY_CODE, GLH = PRPI_GLH_A32, Annual_GLH = STEN_Annual_GLH, --- probably better to use DV field Venue = STEN_Delivery_Site, Company_Name = ISNULL(CMPN_Company_Name,''), Company_Code = ISNULL(CMPN_Company_Code,''), Stu_ID = STUD_Student_ID, Student_Forename = STUD_Forename_1, Surname = STUD_Surname, Middlename = STUD_Forename_2, Date_of_Birth = STUD_DOB, Sex = STUD_Gender, NI_Number = ISNULL(STAN_Alternative_ID,''), Learning_Difficulty = STUD_Learning_Difficulty, Disability = STUD_Disability, Learning_Diff = STUD_LDDHP, Ethnicity = STUD_Ethnicity, Student_Address_1 = ISNULL(VAddress.ADDR_Address1,''), Student_Address_2 = ISNULL(VAddress.ADDR_Address2,''), Student_Address_3 = ISNULL(VAddress.ADDR_Address3,''), Student_Address_4 = ISNULL(VAddress.ADDR_Address4,''), Student_Postcode = ISNULL(VAddress.ADDR_PostCode,''), Email_Address = STUD_EMail_Address, Mobile_Number = STUD_Mobile_Telephone, Telephone = STUD_Home_Telephone_No, Main_Delivery_Method = STEM_Del_Method, Prior_Attainment_Level = STYR_Prior_Attain_Lvl, Enhanced_Funded = STYR_Enh_Fndg_A,--STYR_Enh_Fndg_B? Current_Status = STEN_Status_Code, Employment_Status_Begin = STYR_Employ_status_First_Day, Start_Date = STEN_Start_Date, Exp_End_Date = STEN_Expctd_End_Date, Actual_End_Date = STEN_Actual_End_Date, Completion = STEN_Completion_Stat, Achievement = STEN_Achieve_date, EDRS_No = ISNULL(CMPN_EDRS_No,''), Employer_Address_1 = ISNULL(VAddress_1.ADDR_Address1,''), Employer_Address_2 = ISNULL(VAddress_1.ADDR_Address2,''), Employer_Address_3 = ISNULL(VAddress_1.ADDR_Address3,''), Employer_Address_4 = ISNULL(VAddress_1.ADDR_Address4,''), Employer_Postcode = ISNULL(VAddress_1.ADDR_PostCode,''), Date_Created = STUD_Created_On,STEN_Employer_Identifier = STEN_Employer_Identifier, Assessor_Trainer = ISNULL(WBPS_Assessor,'')FROM CMPN_Company_main RIGHT OUTER JOIN STEN INNER JOIN STEM ON STEM_Student_ID = STEN_Student_ID AND STEM_Provision_Code = STEN_Provision_Code AND STEM_Provision_Instance = STEN_Provision_Instance INNER JOIN STUDstudent ON STUD_Student_ID = STEN_Student_ID INNER JOIN STYRStudentYR ON STYR_Student_ID = STEN_Student_ID AND STYR_Year = STEN_Year INNER JOIN PRPIProvisionInstance ON PRPI_Code = STEN_Provision_Code AND PRPI_Instance = STEN_Provision_Instance INNER JOIN PRILILR ON PRIL_Code = PRPI_Code AND PRIL_Instance = PRPI_Instance AND PRIL_Year = STEN_Year INNER JOIN LEARNING_AIM ON LEARNING_AIM_REF = PRIL_Aim_A09ON STUD_Employer_Code = CMPN_Company_CodeLEFT OUTER JOIN VAddress AS VAddress_1 ON VAddress_1.ADDR_EntityRef = CMPN_Company_Code AND VAddress_1.ADDR_EntityType = 'CMPN' AND VAddress_1.ADDR_AddressType = 'INVO'LEFT OUTER JOIN VAddress ON VAddress.ADDR_EntityRef = STUD_Student_ID AND VAddress.ADDR_EntityType = 'STUD' AND VAddress.ADDR_AddressType = 'HOME'LEFT OUTER JOIN STANaltno ON STAN_Alias_Type = 'NIN' AND STAN_Student_ID = STUD_Student_IDLEFT OUTER JOIN WBPLacements ON WBPL_Student_ID = STEN_Student_ID AND WBPL_Provision_Code = STEN_Provision_Code AND WBPL_Provision_Instance = STEN_Provision_InstanceLEFT OUTER JOIN(SELECT WBPS_Placement_ISN, MAX(WBPS_Staff_Code) as WBPS_AssessorFROM WBPSPlacementStaffGROUP BY WBPS_Placement_ISN) wbp ON WBPS_Placement_ISN = WBPL_ISN WHERE (STEN_Start_Date >= '2005/01/01') AND ((STEN_Provision_Code like 'cc%') OR (NOT(STEN_Employer_Identifier IS NULL)))
Removing spaces before data entry
Posted - 2009-10-12 : 06:50:05
