August 12, 2009 at 6:41 am
Thanks Timothy that helped a lot. I will investigate so of the other posts on this matter. It's nice to learn new things that make SQL more easy to manage 🙂
August 12, 2009 at 7:57 am
Just in case anyone is reading my last post and thinks i have resolved my issue, i haven't i was thanking tim for some information in a post he wrote. Please look at my post before last re cross referencing tables. Thanks,look forward to getting some feed back.
August 12, 2009 at 10:35 am
I think I'm following you. . . Is this what you mean? (I've posted the original vs. the new)
/* I am looking to extract data from the wce_ilr table
and insert into wce_contact table but i only want to
insert data that does not already exist in wce_contact
table, this is very important.
The criteria to match on is wce_contact.postalcode,
wce_contact.company against
wce_ilr.employer_postcode = wce_contact.postalcode
AND wce_ilr.company_name where these match ignore
those records and insert ones that don't match.
*/
--ORIGINAL
SELECT wce_ilr.EDRS_no AS Uniqueid,
MAX(wce_ilr.EDRS_no) AS EDRS_no,
MAX(wce_ilr.company_Name) AS Employer_name,
max(wce_contact.company) as comp,
MAX(wce_ilr.Employer_Address_1) AS Address1,
MAX(wce_ilr.Employer_Address_2) AS Address2,
MAX(wce_ilr.Employer_Address_3) AS Address3,
MAX(wce_ilr.Employer_Address_4) AS Address4,
MAX(wce_ilr.Employer_postcode) AS Postcode,
'Company' AS Record_Type
FROM wce_ilr
LEFT OUTER JOIN wce_contact
ON wce_ilr.employer_postcode = wce_contact.postalcode
AND wce_ilr.company_name =wce_contact.company COLLATE database_default
WHERE (wce_contact.EDRS IS NULL)
AND wce_ilr.EDRS_no IS NOT NULL
GROUP BY wce_ilr.EDRS_no
ORDER BY wce_ilr.EDRS_no
--NEW
Select distinct
wce_ilr.EDRS_no AS Uniqueid,
wce_ilr.EDRS_no
wce_ilr.company_Name AS Employer_name,
wce_contact.company as comp,
wce_ilr.Employer_Address_1 AS Address1,
wce_ilr.Employer_Address_2 AS Address2,
wce_ilr.Employer_Address_3 AS Address3,
wce_ilr.Employer_Address_4 AS Address4,
wce_ilr.Employer_postcode AS Postcode,
'Company' AS Record_Type
from wce_ilr
inner join
--records that aren't currently in the table
(
Select PostalCode = employer_postcode,
Company = company_name,
EDRS
from wce_ilr
except
Select PostalCode,
Company,
EDRS = EDRS_no
from wce_contact
) vt
on wce_ilr.employer_postcode = vt.PostalCode
and wce_ilr.company_name = vt.Company
and wce_ilr.EDRS = vt.EDRS
August 12, 2009 at 2:36 pm
Hi I will look at this now but i don't think i can use distinct on all the columns i'm using which i why i ended up with the following. Thanks for the feed back.
This is what i am working with. If you see anything wrong with it let me know. thanks
Here is the actual code and structure i used based on the above example. I needed to extract unique learner records one row per learner (distinct rows) from table wce_ilr and insert them into wce_contact. The following code worked perfectly to do that.
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,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, 'Learner'
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY stu_id
ORDER BY stu_id) r, *
FROM wce_ilr) A
WHERE r = 1
The next step is to every day cross reference those tables and where a learner does not exist in the wce_ilr table but does in the wce_contact table copy that learner over to the wce_ilr table. So to test this i tried the following code but i get this error:
Error for following code:
Msg 8156, Level 16, State 1, Line 1
The column 'Disability' was specified multiple times for 'A'.
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, 'Learner'
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY wce_ilr.stu_id
ORDER BY wce_ilr.stu_id) r, *
FROM wce_ilr LEFT OUTER JOIN
wce_contact ON wce_ilr.stu_id = wce_contact.stu_id COLLATE database_default
WHERE (wce_contact.stu_id IS NULL)
AND wce_ilr.stu_id IS NOT NULL) A
WHERE r = 1
Here is the bit i added to my working exmaple that copies all the rows without checking for duplicates.
LEFT OUTER JOIN
wce_contact ON wce_ilr.stu_id = wce_contact.stu_id COLLATE database_default
WHERE (wce_contact.stu_id IS NULL)
AND wce_ilr.stu_id IS NOT NULL
Can anyone see why i am getting this error and if this is the best way to cross reference the table?
Thanks for any help.
August 12, 2009 at 2:45 pm
I assume that you're getting that error because you're selecting * from both tables in the row_number virtual table. Select only the columns that you need instead of selecting *. That would likely fix your problem. Also "except" works well for finding records that exist in one table but do not exist in the other.
August 13, 2009 at 2:30 am
I tried removing "Disability" from the top select query but still get the below error. I also remove the * and added the same columns from the top select query but removed the "Disability" from that too but i still get the same error. What i do not understand is why is it complaining about that one field, surely they are all specified multiple times for 'A'.
Any other ideas would be appreciated.
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, 'Learner'
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY wce_ilr.stu_id
ORDER BY wce_ilr.stu_id) r, *
FROM wce_ilr LEFT OUTER JOIN
wce_contact ON wce_ilr.stu_id = wce_contact.stu_id COLLATE database_default
WHERE (wce_contact.stu_id IS NULL)
AND wce_ilr.stu_id IS NOT NULL) A
WHERE r = 1
Error:
Msg 8156, Level 16, State 1, Line 1
The column 'Disability' was specified multiple times for 'A'.
August 13, 2009 at 6:03 am
Remove the astrik "*" from your sub query, and replace it with the field names (this is causing your problem).
August 13, 2009 at 6:45 am
Thanks again Tim, life saver! Thanks everyone for all the help, i think this case is closed for now.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply