Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Use of cursor in ETL stored procedure for moving data from one table to many tables Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 12:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 17, 2012 6:45 AM
Points: 3, 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
Post #1359988
Posted Monday, September 17, 2012 12:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 558, Visits: 881
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/
Post #1359998
Posted Monday, September 17, 2012 1:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 17, 2012 6:45 AM
Points: 3, 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


  Post Attachments 
Cursor_in Stored procedure.xlsx (5 views, 9.14 KB)
Post #1360007
Posted Monday, September 17, 2012 2:05 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 558, Visits: 881
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/
Post #1360017
Posted Monday, September 17, 2012 6:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 17, 2012 6:45 AM
Points: 3, Visits: 8
Hi rhythmk,

Thanks for the help!

I think I am first going to try without cursor.


Thanks,

Somanath
Post #1360165
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse