September 17, 2012 at 12:26 am
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
September 17, 2012 at 12:58 am
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
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
September 17, 2012 at 1:25 am
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
September 17, 2012 at 2:05 am
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
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
September 17, 2012 at 6:47 am
Hi rhythmk,
Thanks for the help!
I think I am first going to try without cursor.
Thanks,
Somanath
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply