April 12, 2009 at 2:52 am
Hi
I am using SQL Server 2008.I want to write a stored proc to get values from three tables and each of these will have equal no of rows always.
so i want to merge the row on each level into a single row and insert into a new table (datatype of source and destination columns are same)
For e.g.
Row1 from table1 and row1 from table2 and row1 from table3 into a single row1 in resulttable
Help would be appreciated
Thanks and regards
Abhi
April 12, 2009 at 5:50 am
Hi,
I found a solution but i dont know if its the best performance wise.
Note :dbo.COM_FN_SplitString : it splits a string based on the delimiter passed
/*****Code*****/
Declare @table1 as table(rowID int IDENTITY(1,1),GroupId int)
Declare @table2 as table(rowID int IDENTITY(1,1),Cost Numeric(10,2))
Declare @table3 as table(rowID int IDENTITY(1,1),Currency Varchar(max))
Declare @resultTable as table(GroupId int,Cost Numeric(10,2),Currency Varchar(max))
Insert into @table1(GroupId) SELECT Convert(int,s) as GroupId From dbo.COM_FN_SplitString('1,2,3',',')
Insert into @table2(Cost) SELECT Convert(Numeric(10,2),s) From dbo.COM_FN_SplitString('30.0,35.5,40.0',',')
Insert into @table3(Currency) SELECT s From dbo.COM_FN_SplitString('Dollar,Pound,Dollar',',')
Insert into @resultTable(GroupId,Cost,Currency)
select GroupId,Cost,Currency From
@table1 as t1
inner join
@table2 as t2
on t1.rowID = t2.rowID
inner join
@table3 as t3
on t3.rowID = t1.rowID
select * From @resultTable
/*****Code*****/
April 12, 2009 at 8:52 am
Hey there Abhi,
There are lots of ways to achieve this, but could you tell us a bit more about what it is you are doing, perhaps with some example table definitions and data? Showing us what you have and how you wish it to look would be very helpful.
Cheers
Paul
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply