Stored Procedure does seem to work

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure does seem to work

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-06-02 : 17:27:25

Hi

I have an SSIS pacakge which gets unique jobaname to the table Batchrecords ,

The syntax of my stored procedure is

select x.* from (select *, row_number() over (partition by JobName order by batchrecordid) as Rownum from BatchRecords_TEMP)x
left join BatchRecords j ON j.JobName = x.JobName
where x.Rownum = 1 and j.JobName is null

Which should give only one record from Batchrecords temp to batchrecords

But i am getting duplicate jobanames , as it is in source table ...

Please Help is there is anything wrong with teh above syntax,
Batchrecordid is a unique id , but i have mutiple samejobanames with different batchrecord id's i need only one jobname for a batchrecordid....

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-02 : 17:32:54

quote:
i have mutiple samejobanames with different batchrecord id's i need only one jobname for a batchrecordid

This statement is confusing..atleast to me. Can you show some sample data from your BatchRecords table and your expected output.

rds207
Posting Yak Master

198 Posts

Posted - 2010-06-02 : 17:46:52

Here is some detailed explanation , hope this helps in explaining what i need ,

Batchrecords : Source Table from another database ( which has duplicate jobnames with different batchrecord id)

BatchRecords_Temp : This table which gets data from another database to batchrecords table in my database (i am using this table as staging table to filter out the duplicate jobnames)

Batchrecords : destination Table on my database(this table should have only one Jobname per batchrecordid

Sample Data:

Batchrecords table(Source)

Batchrecordid Jobname

1596326 SANGetVUs__26MAY2010_104217am
1596327 SANGetVUs__26MAY2010_104217am

Expected Output :(Batchrecords table (destination) in my database)

1596326 SANGetVUs__26MAY2010_104217am

Please Help

quote:
Originally posted by vijayisonly

quote:
i have mutiple samejobanames with different batchrecord id's i need only one jobname for a batchrecordid

This statement is confusing..atleast to me. Can you show some sample data from your BatchRecords table and your expected output.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources