October 16, 2009 at 6:12 am
Hi,
I was helped with this query few days back and need to take it to the next stage . I have tried but can't get it to do what i need and wondered if anyone can help and also describe the alertaions required.
The following query selects all items from one table (wce_ilr) only showing one row per duplicate it finds based on the stu_id. This works great i can then insert the data in to a second table (wce_course_delegate_link).
What i need to do now is select data from wce_ilr but check and a condition before getting the results and inserting the new data in to wce_course_delegate_link.
The condition is, to check and see if the stu_id exsists in the source table wce_ilr and if so ignore the records and only show records that do not exist in the destination table wce_course_delegate_link.
Any help would be appreciated. Thanks
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 16, 2009 at 6:16 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2009 at 7:26 am
Ok, here is code for the source table, destination table and the select query that I am using to remove duplications from the source.
I now need to check the source for duplicates based on the delegateid and then with the results from that check the destination table for duplicate delegateid’s then display the results that do not exist in the destination table.
Hope this is clear. I have made a sample version so the fields are different from my first post but if I get this working can work out the logic in the production environment.
Thanks and I will keep checking responses in case of any questions.
Code for select query:
with cteDelegates(delegateid, delegate_name, course_code, rown) as(Select delegateid, delegate_name, course_code, ROW_NUMBER() OVER (PARTITION BY delegateid ORDER BY delegateid) from wce_ilr)
Select delegateid, delegate_name, course_code
from cteDelegates where rown = 1 order by delegate_name
Code for Source table:
IF EXISTS(SELECT NULL FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name = 'WCE_ILR')
DROP TABLE WCE_ILR
GO
CREATE TABLE WCE_ILR (
uniqueid int IDENTITY(1,1) NOT NULL,
course_code varchar(40) NULL DEFAULT(' '),
delegateid varchar(40) NULL DEFAULT(' '),
delegate_name varchar(40) NULL DEFAULT(' ')
)
GO
INSERT INTO WCE_ILR (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Adam Bourne', '77002333111')
INSERT INTO WCE_ILR (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Alex Rowland', '71031345222')
INSERT INTO WCE_ILR (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Ammar Khan', '77002955333')
INSERT INTO WCE_ILR (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Adam Bourne', '77002333111')
INSERT INTO WCE_ILR (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Alex Rowland', '71031345222')
INSERT INTO WCE_ILR (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Ammar Khan', '77002955333')
INSERT INTO WCE_ILR (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Adam Bourne', '77002333111')
INSERT INTO WCE_ILR (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Alex Rowland', '71031345222')
INSERT INTO WCE_ILR (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Ammar Khan', '77002955333')
INSERT INTO WCE_ILR (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Andrew Connelly', '77008900')
INSERT INTO WCE_ILR (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Andrew Hunt', '77002859')
INSERT INTO WCE_ILR (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Ashley Oakey', '77003212')
Code for Destination table: this has 5 rows of data 3 rows are duplicates from the source table and the other 2 are unique
IF EXISTS(SELECT NULL FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name = 'WCE_COURSE_DELEGATE_LINK')
DROP TABLE WCE_COURSE_DELEGATE_LINK
GO
CREATE TABLE WCE_COURSE_DELEGATE_LINK (
uniqueid int IDENTITY(1,1) NOT NULL,
course_code varchar(40) NULL DEFAULT(' '),
delegateid varchar(40) NULL DEFAULT(' '),
delegate_name varchar(40) NULL DEFAULT(' ')
)
GO
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Adam Bourne', '77002333')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Alex Rowland', '71031345')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Ammar Khan', '77002955')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Andrew Connelly', '77008900')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Andrew Hunt', '77002859')
INSERT INTO WCE_COURSE_DELEGATE_LINK (course_code, delegate_name, delegateid) values
('AAEM001V3WB', 'Ashley Oakey', '77003212')
October 19, 2009 at 2:32 am
Any chance that someone can take a look at this and point me in the right direction?
Thanks
October 19, 2009 at 2:47 am
There are a few methods , not exists, count(*)=0 etc but
I prefer to test non-existance with a left outer join
select *
from WCE_ILR
left join WCE_COURSE_DELEGATE_LINK
on WCE_ILR.delegateid = WCE_COURSE_DELEGATE_LINK.delegateid
where WCE_COURSE_DELEGATE_LINK.delegateid is null
October 19, 2009 at 4:54 am
Thanks for that. I altered my code to this and it looks like i'm getting the results i want. It de-duplicates and only displays the rows that do not exsist in the wce_course_delegate_link table.
with cteDelegates(delegateid, delegate_name, course_code, rown) as(Select wce_ilr.delegateid, wce_ilr.delegate_name, wce_ilr.course_code, ROW_NUMBER() OVER (PARTITION BY wce_ilr.delegateid ORDER BY wce_ilr.delegateid) from wce_ilr left join WCE_COURSE_DELEGATE_LINK
on WCE_ILR.delegateid = WCE_COURSE_DELEGATE_LINK.delegateid
where WCE_COURSE_DELEGATE_LINK.delegateid is null)
Select delegateid, delegate_name, course_code
from cteDelegates where rown = 1 order by delegate_name
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply