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


Use of cursor in ETL stored procedure for moving data from one table to many tables


Use of cursor in ETL stored procedure for moving data from one table to many tables

Author
Message
somanath_kolekar
somanath_kolekar
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 8
Hello All,

I am beginner to writing stored procedure. So checking out your views on implementing below requirement:

I have one table containing data in one system. I want to write stored procedure which will move data from this table to 7 tables based on columns mapping.
I need to some validations while moving data like searching if the record already exists in the destinations tables, rejecting records based on some conditions and moving rejected records to rejected table.

I started implementing this code with stored procedure alone. However I see that I am not able to check the conditions row-wise and insert\update based on condition when I use only stored procedure.

I am thinking of using cursor in a stored procedure to apply the logic row-wise.
This procedure will not be used frequently, however I am wondering if using cursor is the right approach to implement this requirement or are there any better approaches to write code for such requirement?

Any help is appreciated!!!

Thanks,

Somanath
rhythmk
rhythmk
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1712 Visits: 1099
somanath_kolekar (9/17/2012)
Hello All,

I am beginner to writing stored procedure. So checking out your views on implementing below requirement:

I have one table containing data in one system. I want to write stored procedure which will move data from this table to 7 tables based on columns mapping.
I need to some validations while moving data like searching if the record already exists in the destinations tables, rejecting records based on some conditions and moving rejected records to rejected table.

I started implementing this code with stored procedure alone. However I see that I am not able to check the conditions row-wise and insert\update based on condition when I use only stored procedure.

I am thinking of using cursor in a stored procedure to apply the logic row-wise.
This procedure will not be used frequently, however I am wondering if using cursor is the right approach to implement this requirement or are there any better approaches to write code for such requirement.

Any help is appreciated!!!

Thanks,

Somanath


Hi Somanath,

How are you validating the data means there must be some primary key or unique value column.You can validate data against that and apply UPINSERT logic.

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
:-)
somanath_kolekar
somanath_kolekar
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 8
Hi rhythmk,

Thanks for the response.

Yes. For validations I am using primary key. If the row with that primary key exist in table 1, I need to reject that record and add that record to 'reject' table.
If the row does not exists, then add the record in the table 1.

Again I need to refer to table 1 and create child record in other tables ( 8 tables). Basically I am splitting one master source table into many destination tables based on PK/FK relationships. An ETL process.

One more point is : I need to generate primary key in all the destination tables manually taking max of current value and incrementing it by 1.


Implementing this logic in other languages like C is easy. In SQL, adding all conditions in stored procedure without cursor seems to be difficult.

I am thinking cursor will give me that advantage of applying logic to one row at a time.

Attached in file is the requirement description. Hope it helps!

Thanks,

Somanath
Attachments
Cursor_in Stored procedure.xlsx (34 views, 9.00 KB)
rhythmk
rhythmk
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1712 Visits: 1099
somanath_kolekar (9/17/2012)
Hi rhythmk,

Thanks for the response.

Yes. For validations I am using primary key. If the row with that primary key exist in table 1, I need to reject that record and add that record to 'reject' table.
If the row does not exists, then add the record in the table 1.

Again I need to refer to table 1 and create child record in other tables ( 8 tables). Basically I am splitting one master source table into many destination tables based on PK/FK relationships. An ETL process.

One more point is : I need to generate primary key in all the destination tables manually taking max of current value and incrementing it by 1.


Implementing this logic in other languages like C is easy. In SQL, adding all conditions in stored procedure without cursor seems to be difficult.

I am thinking cursor will give me that advantage of applying logic to one row at a time.

Attached in file is the requirement description. Hope it helps!

Thanks,

Somanath


Hi Somanath,

For below requirement


Yes. For validations I am using primary key. If the row with that primary key exist in table 1, I need to reject that record and add that record to 'reject' table.
If the row does not exists, then add the record in the table 1.



For reject table you can give a try like this

INSERT INTO RejectTbl (Col1,Col2,.....)
SELECT Col1,Col2,......
FROM SourceTable INNER JOIN table 1 ON SourceTable.Pk_col = table 1.Pk_col



For Insert


INSERT INTO table1 (Col1,Col2,.....)
SELECT Col1,Col2,......
FROM SourceTable
WHERE Pk_col NOT IN (SELECT Pk_col FROM table1)



For remaining part please let me know the distribution criteria.

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
:-)
somanath_kolekar
somanath_kolekar
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 8
Hi rhythmk,

Thanks for the help!

I think I am first going to try without cursor.


Thanks,

Somanath
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