February 10, 2011 at 10:42 am
This is the scenario I have...
1. Select data from Table A
2. Pass each row values (4 columns) as parameters to a stored procedure.
3. The dataset returned from stored procedure should be inserted to TableB.
Please help me with steps to follow to acheive this task.
Thanks
February 10, 2011 at 11:41 am
I would try to rewrite the stored procedure as an inline-table valued function.
This would change the RBAR approach into a set based solution.
We'd need to know more what the sproc actually does to help any further.
February 11, 2011 at 2:31 am
The stored proc basically splits the data and returns a dataset with 2 columns...
e.g! Test1~Test2 , 30~40 are the two params passed to the stored proc, while it returns like
Test1 30
Test2 40
I need to push the above two rows in to Table B Where as Test1~Test2 is the value in the column of TableA
February 11, 2011 at 11:28 am
Based on what you described so far I'd use something along the following concept:
DECLARE @tbl TABLE
(
id INT IDENTITY(1,1),
col1 VARCHAR(20),
col2 VARCHAR(20)
)
INSERT INTO @tbl
SELECT 'Test1~Test2' , '10~20' UNION ALL
SELECT 'Test3~Test4' , '30~40' UNION ALL
SELECT 'Test5~Test6' , '50~60'
SELECT
t.id,
LEFT(t.col1, CHARINDEX('~',t.col1)-1) val1,
LEFT(t.col2, CHARINDEX('~',t.col2)-1) val2
FROM @tbl t
UNION ALL
SELECT
t.id,
STUFF(t.col1,1, CHARINDEX('~',t.col1),''),
STUFF(t.col2,1, CHARINDEX('~',t.col2),'')
FROM @tbl t
ORDER BY id,val1
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply