SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to insert new record for a user having multiple records by performing lookup?


How to insert new record for a user having multiple records by performing lookup?

Author
Message
deepthipriya.ravi
deepthipriya.ravi
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
Hi,

I have one table called Student. The source for loading the table is StudentHistory.

My Scenario is i have a single student having multiple records like single student mapped to several departments.
Select distinct student,Department from Student gives 10 records.
if i get 11th record from StudentHistory table it should check for student mapped to that department, if no add record to Student table.

I applied Left outer join using mergejoin task in SSIS for this scenario.
StudentHistory as left source and Student as right, when i am looking for the student & department, for matched rows also my Student table is returning null values.
so that all the 11 records are getting inserted into Student table based on condition.

What is the reason for retrieving all null values even for matched records?
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14847 Visits: 14396
I could take a guess at what you want and I might nail it but I might not. So in the spirit of getting you some tested code in an efficient way I would prefer you provide examples of the tables you are using in the form of CREATE TABLE statements, a set of sample data in the form of INSERT statements, the expected results based on that sample data and most importantly, what you have tried so far.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
deepthipriya.ravi
deepthipriya.ravi
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
Hi, Thanks for the reply
here goes the example
Source Table
CREATE TABLE StudentHistory(
StudentHistoryId int IDENTITY(1,1) NOT NULL,
StudentId VARCHAR(6) NOT NULL,
DepartmentId VARCHAR(5) NOT NULL,
ProcessedMonth INT NOT NULL,
ProcessedYear INT NOT NULL,
ProcessedDate DATETIME NOT NULL,
InsertedDate DATETIME NOT NULL
CONSTRAINT PK_StudentHistory_StudentHistoryId PRIMARY KEY CLUSTERED
(
StudentHistoryId ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Considerations
1) StudentId and DepartmentId are foreign Keys
2) Every month for a year will have present active records
3) ProcessedDate is daily process time getdate
4) InsertedDate is the record first time inserted for that month in a year

DestinationTable
CREATE TABLE Student(
PrimarykeyId int IDENTITY(1,1) NOT NULL,
StudentId VARCHAR(6) NOT NULL,
DepartmentId VARCHAR(5) NOT NULL,
InsertedDate DATETIME NOT NULL,
LastUpdated DATETIME NOT NULL
CONSTRAINT PK_StudentHistory_PrimarykeyId PRIMARY KEY CLUSTERED
(
PrimarykeyId ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Consider like Student 1 is mapped to three departments in both the tables
for today history table is inserted with same student mapped to another department.
Student table lookup for student and department when left outer join is performed the matched three records are also returning null
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14847 Visits: 14396
Thank you for the table definitions. This looks like homework, which is fine, I am happy to help, but I need for you to provide the other three things I asked for.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
deepthipriya.ravi
deepthipriya.ravi
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
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
StudentId DepartmentId StudentId DepartmentId
2 100 2 100
2 101 2 101
2 102 2 102
2 103 2 103
2 104 NULL NULL


but i am receiveing


Source Destination
StudentId DepartmentId StudentId DepartmentId
2 100 NULL NULL
2 101 NULL NULL
2 102 NULL NULL
2 103 NULL NULL
2 104 NULL NULL


Could u please guide me on this
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14847 Visits: 14396
Than you for the test data. There were some errors in it but I got it to work. Here it is for other onlookers:

INSERT  INTO dbo.StudentHistory
(StudentId, DepartmentId, ProcessedMonth, ProcessedYear, ProcessedDate, InsertedDate)
VALUES (1, 100, 2, 2013, GETDATE(), '2013-02-01 12:00:00 '),
(2, 100, 2, 2013, GETDATE(), '2013-02-01 12:00:00 '),
(2, 101, 2, 2013, GETDATE(), '2013-02-01 12:00:00 '),
(2, 102, 2, 2013, GETDATE(), '2013-02-01 12:00:00 '),
(2, 103, 2, 2013, GETDATE(), '2013-02-01 12:00:00 '),
(3, 103, 2, 2013, GETDATE(), '2013-02-01 12:00:00 '),
(3, 104, 2, 2013, GETDATE(), '2013-02-01 12:00:00 ');

INSERT INTO dbo.Student
(StudentId, DepartmentId, InsertedDate, LastUpdated)
VALUES (1, 100, '2013-02-01 12:00:00 ', '2013-02-01 12:00:00 '),
(2, 100, '2013-02-01 12:00:00 ', '2013-02-01 12:00:00 '),
(2, 101, '2013-02-01 12:00:00 ', '2013-02-01 12:00:00 '),
(2, 102, '2013-02-01 12:00:00 ', '2013-02-01 12:00:00 '),
(2, 103, '2013-02-01 12:00:00 ', '2013-02-01 12:00:00 '),
(3, 103, '2013-02-01 12:00:00 ', '2013-02-01 12:00:00 ');




I am wondering if you need to bother with any of the Join Transforms in SSIS. Are StudentHistory and Student tables on the same Database Instance, i.e. can they be joined together in a single SQL query like this to add missing rows to the Student table?

INSERT  INTO dbo.Student
(
StudentId,
DepartmentId,
InsertedDate,
LastUpdated
)
SELECT DISTINCT
sh.StudentId,
sh.DepartmentId,
GETDATE(),
GETDATE()
FROM dbo.StudentHistory sh
LEFT JOIN dbo.Student s ON sh.StudentId = s.StudentId
AND sh.DepartmentId = s.DepartmentId
WHERE s.StudentId IS NULL;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
MMartin1
MMartin1
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2731 Visits: 2031
So you are adding back records to the student table that you think should be there. Could it be the students table was archived somewhere else and the history table not? By reinserting the student you may be duplicating data relative to the student and (possible) student archive tables. Just a thought that occurred to me.

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search