Home Forums SQL Server 2005 Business Intelligence How to insert new record for a user having multiple records by performing lookup? RE: How to insert new record for a user having multiple records by performing lookup?

  • INSERT INTO StudentHistory(1,100,2,2013,GETDATE(),'2013-02-01 12:00:00 ')

    INSERT INTO StudentHistory(2,100,2,2013,GETDATE(),'2013-02-01 12:00:00 ')

    INSERT INTO StudentHistory(2,101,2,2013,GETDATE(),'2013-02-01 12:00:00 ')

    INSERT INTO StudentHistory(2,102,2,2013,GETDATE(),'2013-02-01 12:00:00 ')

    INSERT INTO StudentHistory(2,103,2,2013,GETDATE(),'2013-02-01 12:00:00 ')

    INSERT INTO StudentHistory(3,103,2,2013,GETDATE(),'2013-02-01 12:00:00 ')

    INSERT INTO StudentHistory(3,104,2,2013,GETDATE(),'2013-02-01 12:00:00 ')

    INSERT INTO StudentHistory(1,100,'2013-02-01 12:00:00 ','2013-02-01 12:00:00 ')

    INSERT INTO StudentHistory(2,100,'2013-02-01 12:00:00 ','2013-02-01 12:00:00 ')

    INSERT INTO StudentHistory(2,101,'2013-02-01 12:00:00 ','2013-02-01 12:00:00 ')

    INSERT INTO StudentHistory(2,102,'2013-02-01 12:00:00 ','2013-02-01 12:00:00 ')

    INSERT INTO StudentHistory(2,103,'2013-02-01 12:00:00 ','2013-02-01 12:00:00 ')

    INSERT INTO StudentHistory(3,103,'2013-02-01 12:00:00 ','2013-02-01 12:00:00 ')

    Try to insert this first later build ssis to perform left outer join with StudentHistory as Left source and Student as right Source on conditions studentid and departmentid. I have used Merge join

    Now INSERT INTO StudentHistory(2,104,2,2013,GETDATE(),GETDATE())

    run the ssis my issue is i am doing lookup for a studentid 2 to a department while doing so i should get the below data

    Source Destination

    StudentIdDepartmentId StudentIdDepartmentId

    2 100 2100

    2 101 2101

    2 102 2102

    2 103 2103

    2 104 NULLNULL

    but i am receiveing

    Source Destination

    StudentIdDepartmentId StudentIdDepartmentId

    2 100 NULLNULL

    2 101 NULLNULL

    2 102 NULLNULL

    2 103 NULLNULL

    2 104 NULLNULL

    Could u please guide me on this